SQL Server Pivot One Column and Keep Other Columns

user_4984061 0
Viewed 1168

I am trying to pivot a table in SQL Server (52M+ observations) however I am not getting the results I need. There are 15 descriptions each with a value that I need to pivot.

Original Dataframe:

ID         |  Date      | Description| Value 
-------------------------------------------------
P1         | 2016-12-31 |       ABC  |         900
P2         | 2016-11-30 |       XYZ  |         800
P3         | 2016-10-31 |       MNO  |         700

Desired Results

ID         |  Date      | ABC | XYZ  | MNO 
-------------------------------------------------
P1         | 2016-12-31 | 900  |     | 
P2         | 2016-11-30 |      | 800 |     
P3         | 2016-10-31 |      |     | 700

I have tried pivoting this in PySpark and SQL, but have not gotten a working result.

SQL Attempt:

SELECT [Date]
      ,[ID]
      ,[Description]
      ,[Value]
  FROM [DB].[TABLE]
  WHERE ( ([Description] IN ('ABC','XYZ', 'MNO'))
  PIVOT(
    COUNT([Value]) 
    FOR Description IN (
        [ABC], 
        [XYZ], 
        [MNO])
) AS pivot_table;

I tried this in Pyspark however it doesnt work either:

df.groupBy("ID","Date").pivot("Description").sum("Value")
pyspark pivot sql-server sql python
2 Answers

Use conditional aggregation:

select
    id,
    date,
    max(case when description = 'ABC' then value end) as abc,
    max(case when description = 'DEF' then value end) as def,
    max(case when description = 'MNO' then value end) as mno
from mytable
group by id, date
user_106767160

The pivot operator goes immediately after the table in the FROM clause. To put a WHERE clause you will need to put the pivot source table inside a subquery, or a Common Table Expression.

Also, you cannot output the Description and Value columns in the outer select, because they are now grouped by the Pivot.

Try this way:

SELECT [Date],
        [ID], 
        [ABC], 
        [XYZ], 
        [MNO]
FROM
(
    SELECT [Date]
          ,[ID]
          ,[Description]
          ,[Value]
      FROM [DB].[TABLE]
      WHERE ([Description] IN ('ABC','XYZ', 'MNO'))
) AS SourceTable 
 PIVOT(
    COUNT([Value]) 
    FOR Description IN (
        [ABC], 
        [XYZ], 
        [MNO])
) AS PivotTable;
user_24140250
Related