Combine multiple rows with different column values into a single one

October 18, 2022

I’m trying to create a single row starting from multiple ones and combining them based on different column values; here is the result i reached based on the following query:

select distinct ID, case info when 'name' then value end as 'NAME', case info when 'id' then value end as 'serial'
FROM TABLENAME t 
WHERE info = 'name' or info = 'id'

Howerver the expected result should be something along the lines of

I tried with group by clauses but that doesn’t seem to work.
The RDBMS is Microsoft SQL Server.
Thanks

>Solution :

SELECT X.ID,MAX(X.NAME)NAME,MAX(X.SERIAL)AS SERIAL   FROM
(
  SELECT 100 AS ID, NULL AS NAME, '24B6-97F3'AS SERIAL UNION ALL
  SELECT 100,'A',NULL UNION ALL
  SELECT 200,NULL,'8113-B600'UNION ALL
  SELECT 200,'B',NULL
)X
GROUP BY X.ID

For me GROUP BY works