How to convert rows with distinct values to columns?

Question

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.

take a look to: http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server — Rumpelstinsk, Aug 10 '16 at 12:42
thank you @Rumpelstinsk , but unfortunatelly i want to return the values in seperate columns not in one. — pavlos, Aug 10 '16 at 12:50

score 1 · Accepted Answer · answered Aug 10 '16 at 12:53

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

This is great. Thank you @NEER – pavlos Aug 10 '16 at 13:02 — pavlos, Aug 10 '16 at 13:02

score 0 · Answer 2 · answered Aug 10 '16 at 12:44

Try to use

Select a.id, (select max(b.description) from table1 b where b.id=a.id and b.attribute='color') color,
(select max(c.description) from table1 c where c.id=a.id and c.attribute='size') size,
(select max(d.description) from table1 d where d.id=a.id and d.attribute='status') status
  from table1 a group by a.id

score 0 · Answer 3 · answered Aug 10 '16 at 12:54

Didn't run it yet, but for your requirement, this should work. But if you have more attributes, then you should go for dynamic pivot.

select 
case when c.id is not null then c.id 
    when s.id is not null then s.id
    else st.id  end as id
,c.color as color
,s.size as size
,st.status as status
from
 (select id,description as color from table1 where attribute='color') c
full outer join
 (select id,description as Size from table1 where attribute='size') s
on c.id=s.id
full outer join
 (select id,description as status from table1 where attribute='status') st
on c.id=st.id

Thank you @Utsav, i tested it and it does the trick :) . I' gonna go with the pivot method though since more attributes may be added in the near future. — pavlos, Aug 10 '16 at 13:15

score 0 · Answer 4 · answered Aug 10 '16 at 12:54

0

It's simple with PIVOT:

SELECT *
FROM table1
PIVOT
(MAX(Description) FOR Attribute IN (Color, Size, Status)) Pvt

answered Aug 10 '16 at 12:54

Paweł Dyl

8,888
1
11
27

How to convert rows with distinct values to columns?

4 Answers4