Combine multiple rows with different column values into a single one-CodePudding

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

CodePudding user response：

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

CodePudding user response：

I'm a fan of a self join for things like this

SELECT tName.ID, tName.Value AS Name, tSerial.Value AS Serial
FROM TableName AS tName
INNER JOIN TableName AS tSerial ON tSerial.ID = tName.ID AND tSerial.Info = 'Serial'
WHERE tName.Info = 'Name'

This initially selects only the Name rows, then self joins on the same IDs and now filter to the Serial rows. You may want to change the INNER JOIN to a LEFT JOIN if not everything has a Name and Serial and you want to know which Names don't have a Serial