Category value into multiple columns?

Question

I have a table like this:

DateKey   | Category  |  Value 
----------+-----------+---------
20190102  | CAT1      | 12
20190502  | CAT2      | 13
20190502  | CAT2      | 16
20190102  | CAT1      |  2
20190102  | CAT3      | 22
....

I want to get a result like this.

The columns are automatically generated from the SQL statement

DateKey   | CAT1SUM   |  CAT2SUM  |  CAT3SUM
----------+-----------+-----------+-----------
20190102  | 14        |  0        | 22
20190502  |  0        | 29        |  0

I know how to do this with known Categories, but the problem is Category amount are unknown, but the amount are not too many ( less than 20). I know that I can write a store procedure / function, but Is it possible to archive this in as few sql statement as possible? What is the most efficient way to do it?

I know I can get a single column table like this

SELECT 
    DateKey, Category, SUM(Value) SummedValue
FROM 
    MyTable
GROUP BY 
    DateKey, Category

But, the result is a single 'value' column table, which is not what I want.

This requires dynamic SQL and that in turn depends on the database you are using. — Gordon Linoff, May 12 '19 at 16:43
[SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Lukasz Szozda, May 12 '19 at 16:46
That is it! I don't know the term pivot so I did not find the correct answer! Thanks @LukaszSzozda — Tony, May 12 '19 at 16:50

Category value into multiple columns?

0 Answers0