Can I use Pivot or Aggregation and write query to convert multiple rows into multiple columns

Question

I have a table like this...

ID, Obj_Name, Value
1     Col1     XA
1     Col2     XB
2     Col1     DX
2     Col2     XY

Which I would like to transpose like the below table

ID, Col1, Col2
1    XA    XB
2    DX    XY

My following query is not working properly, I know why, But I do not know how to fix. I have searched internet for similar problem, but didn't find. Can someone give me a clue on what's right way to do this?

SELECT ID, 
       CASE WHEN OBJ_NAME = 'Col1' THEN Value END as Col1,
       CASE WHEN OBJ_NAME = 'Col2' THEN Value END as Col2
  FROM Table1

Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Mani Deep, Jul 26 '18 at 02:54

score 3 · Answer 1 · answered Jul 26 '18 at 03:02

3

You are missing a group by in your conditional aggregation query

SELECT 
ID, 
MAX(CASE WHEN OBJ_NAME = 'Col1' THEN Value END) as Col1, 
MAX(CASE WHEN OBJ_NAME = 'Col2' THEN Value END) as Col2 
FROM Table1
Group by ID

answered Jul 26 '18 at 03:02

DhruvJoshi

17,041
6
41
60

Can I use Pivot or Aggregation and write query to convert multiple rows into multiple columns

1 Answers1