CODEWITHSUNDEEP
sqlpivotsnowflake-cloud-data-platform
Koba
Koba

Reputation: 1542

Pivot two columns keeping corresponding ids programmatically

I have a table with the following structure

ID    Category    Value
1        Age        23
1      Income     10000
2        Age        30
2      Income     50000

I would like to pivot it so that there is one row per id

ID      Age   Income
1       23     10000
2       30     50000

I am not sure if its possible in Snowflake DB.

EDIT: the actual table has 206 levels in the category factor, so I am looking for a programmatic way without needing to reference category levels in the query.

Upvotes: 1

Views: 1782

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use conditional aggregation:

SELECT ID,
       MAX(CASE WHEN Category = 'Age' THEN Value END) as Age,
       MAX(CASE WHEN Category = 'Income' THEN Value END) as Income
FROM TABLENAME
GROUP BY ID;

Upvotes: 1

TheSnake
TheSnake

Reputation: 36

Something like this should give you what you need by using the CASE statement:

SELECT ID,
CASE WHEN Category = 'Age' THEN Value END as 'Age',
CASE WHEN Category = 'Income' THEN Value END as 'Income'
FROM TABLENAME

Upvotes: 1

Related Questions