pavlos pavlos - 4 months ago 7
SQL Question

How to convert rows with distinct values to columns?

I have a temporary table

table1
as seen below

table1
+------+---------------+------------+
| Id | Description | Attribute |
+------+---------------+------------+
| 1 | blue | color |
| 1 | Large | size |
| 1 | active | status |
| 2 | green | color |
| 2 | small | size |
| 2 | inactive | status |
+------+---------------+------------+


I would like to return a table as seen below:

+------+-----------+-----------+-----------+
| Id | Color | Size | Status |
+------+-----------+-----------+-----------+
| 1 | blue | large | active |
| 2 | green | small | inactive |
+------+-----------+-----------+-----------+


Is there a way to do this?
Thank you.

Answer

Use PIVOT as below:

DECLARE @Tbl TABLE (Id INT, Description NVARCHAR(max), Attribute NVARCHAR(50))
INSERT INTO @Tbl
select 1  ,   'blue',        'color' union all
select 1  ,   'Large',       'size' union all
select 1  ,   'active',     'status' union all
select 2  ,   'green',       'color' union all
select 2  ,   'small',      'size ' union all   
select 2  ,   'inactive',  'status' 


SELECT
*
FROM
(
    SELECT * 
    FROM 
        @Tbl
) A 
PIVOT
(
    MIN(Description) 
    FOR 
    Attribute IN ([color], [size], [status]  )
) B

Result:

Id  color   size    status
1   blue    Large   active
2   green   small   inactive