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:
BatchID | Chemical 1 Actual | Chemical 2 Actual |
---|---|---|
1 | 10 | 11 |
2 | 20 | 21 |
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.
1条答案
按热度按时间rjee0c151#
If you always have the same two chemicals "chemical 1" and "chemical 2" then a pivot would work:
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.