sql - How to use pivot in SQL?

Question

I have a table that looks like below:

Need to convert the same into the below format:

The data is at A,B, C level. Tried using case when as shown below but it is resulting in multiple rows. So thinking of using a pivot.

Select a,b,c,
       case when D = N and E = 1 then N1 = value,
       case when D = N and E = 2 then N2 = value,
       case when D = O and E = 1 then O1 = value,
       case when D = O and E = 2 then O2 = value,

Any help would be appreciated. Thank you!

标签： sqlsql-servertsqlssmsazure-sql-data-warehouse

Answer 1

Use aggregation:

Select a, b, c,
       max(case when D = 'N' and E = 1 then value end) as N1,
       max(case when D = 'N' and E = 2 then value end) as N2,
       max(case when D = 'O' and E = 1 then value end) as O1,
       max(case when D = 'O' and E = 2 then value end) as O2
from t
group by a, b, c;

Note that this also fixes the logic in the case expressions.