CODEWITHSUNDEEP
sqlsql-server
pavlos
pavlos

Reputation: 33

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.

Upvotes: 3

Views: 1929

Answers (4)

Paweł Dyl
Paweł Dyl

Reputation: 9143

It's simple with PIVOT:

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

Upvotes: 0

Utsav
Utsav

Reputation: 8093

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

Upvotes: 0

neer
neer

Reputation: 4082

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

Upvotes: 1

Christian
Christian

Reputation: 827

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

Upvotes: 0

Related Questions