Multiple rows into single column

Question

I have a below data in the table.

Need output like below.

I have tried the below logic but in my case, those Item# columns are NVARCHAR type, so the MAX aggregate function is giving unknown as a result.

SELECT ID, Item1, Item2, Item3, Item4, Item5
FROM(
SELECT ID, MAX(Item1), MAX(Item2), MAX(Item3), MAX(Item4), MAX(Item5)
FROM Sample_Table WITH(NOLOCK)
) A
WHERE A.ID = '0001_11'
GROUP BY A.ID

Please help me to get this result. Thanks in advance.

Answer 1

Try using pivot logic. In this case, we want to disregard the value Unknown, so the CASE expressions below assign it to NULL, which is ignored by SUM.

SELECT
    ID,
    MAX(CASE WHEN Item1 = 'Unknown' THEN NULL ELSE Item1 END) AS Item1,
    MAX(CASE WHEN Item2 = 'Unknown' THEN NULL ELSE Item2 END) AS Item2,
    MAX(CASE WHEN Item3 = 'Unknown' THEN NULL ELSE Item3 END) AS Item3,
    MAX(CASE WHEN Item4 = 'Unknown' THEN NULL ELSE Item4 END) AS Item4,
    MAX(CASE WHEN Item5 = 'Unknown' THEN NULL ELSE Item5 END) AS Item5,
    MAX(CASE WHEN Item6 = 'Unknown' THEN NULL ELSE Item6 END) AS Item6
FROM yourTable
GROUP BY
    ID;