How to use pivot for two columns?

Question

I have this table:

   MoveDate      Typ     Amount    Capacity
  2017-01-01      A       1000        10
  2017-01-01      B        2000       20
   2017-03-05     A        3000       5
  2017-06-08      C        1500       6

and want this result:

MoveDate     A      Capacity    B      Capacity    C    Capacity
2017-01-01    1000     10       2000        20     NULL    NULL  
2017-03-05    3000     5         NULL       NULL   NULL    NULL
2017-06-08    NULL     NULL      NULL      NULL    1500      6

How can I Implement it with pivot or another approach?

Possible duplicate of [TSQL PIVOT MULTIPLE COLUMNS](https://stackoverflow.com/questions/19590799/tsql-pivot-multiple-columns) — Gurwinder Singh, Nov 01 '17 at 10:07

score 1 · Accepted Answer · answered Nov 01 '17 at 11:33

You can also use dynamic sql query to achieve this.

Query

declare @sql as varchar(max);
select @sql = stuff((
        select ',sum(case [Typ] when ' + char(39) + [Typ] + char(39) 
              + ' then [Amount] end) as [' + [Typ] + '],
              sum(case [Typ] when ' + char(39) + [Typ] + char(39) 
              + ' then [Capacity] end) as [Capacity] '
        from  [your_table_name]
        for xml path('')
    )
    , 1, 1, ''
);

select @sql = 'select [MoveDate], ' + @sql 
              + ' from [your_table_name] group by [MoveDate];';

exec(@sql);

score 0 · Answer 2 · answered Nov 01 '17 at 10:37

Try something like this.

SELECT
  MoveDate,
  SUM(CASE WHEN Typ = 'A' THEN Amount END) AS A,
  SUM(CASE WHEN Typ = 'A' THEN Capacity END) AS Capacity,
  SUM(CASE WHEN Typ = 'B' THEN Amount END) AS B,
  SUM(CASE WHEN Typ = 'B' THEN Capacity END) AS Capacity,
  SUM(CASE WHEN Typ = 'C' THEN Amount END) AS C,
  SUM(CASE WHEN Typ = 'C' THEN Capacity END) AS Capacity
FROM myTable
GROUP BY MoveDate

How to use pivot for two columns?

2 Answers2