Query to get row-related data in columns in SQL Server

jc3wubiy  于 2023-04-28  发布在  SQL Server
关注(0)|答案(1)|浏览(132)

I have an existing table in SQL Server that I need to organize into a more readable format.

Here is my example:
| BatchAddID | BatchID | Description | Actual |
| ------------ | ------------ | ------------ | ------------ |
| 1 | 1 | Chemical 1 | 10 |
| 2 | 1 | Chemical 2 | 11 |
| 3 | 2 | Chemical 1 | 20 |
| 4 | 2 | Chemical 2 | 21 |

Here is what I would like my result to be:

BatchIDChemical 1 ActualChemical 2 Actual
11011
22021

What is the most efficient way to perform this query?

I have tried nesting select statements to achieve the result, but I'm having difficulties.

rjee0c15

rjee0c151#

If you always have the same two chemicals "chemical 1" and "chemical 2" then a pivot would work:

select 
    BatchID, 
    [Chemical 1] as [Chemical 1 Actual],
    [Chemical 2] as [Chemical 2 Actual]
from
(
    select 
        BatchID,
        [Description],
        Actual
    from 
        dbo.BatchResults
) t
pivot(
    sum(Actual)
    for [Description] in ([Chemical 1],[Chemical 2])
) as pivot_table;

Some further detail and information on pivot (including some extra details on making pivots "dynamic" with a dynamic sql approach):

https://www.sqlservertutorial.net/sql-server-basics/sql-server-pivot/

Note that personally I would consider your original output as perfectly acceptable "readable format". You may be able leave the details of presentation of data to client code rather than being done in "raw sql" - for example, feeding a pivot table in Excel, a chart or table in a web app, or simply being transformed by some code in a client app.

相关问题