Combine multiple rows into a single row in SQL

Question

I have a table like below :

    ID   TYPE    VALUE
    1    CC 1     X
    1    CC 2     Y
    1    CC 3    NULL
    1    CC 4    NULL

I am using sql PIVOT and STUFF functionality to get the desired result but still not able to do it. Please if anyone can help me.

Desired Result :

ID [CC 1]  [CC 2]  [CC 3]  [CC 4]
1    X       Y      NULL    NULL

http://stackoverflow.com/questions/24470/sql-server-pivot-examples — Andrey Davydenko, May 22 '15 at 11:29
@juergend may be .. but these are in loop for a particular Id. Let say for Id 1 these are up to 13. then for 2 and so on. — techV, May 22 '15 at 11:37
I think you are confusing STUFF with XML concatinate methods. STUFF however doesn't work as you expect. All STUFF does is replace part of a varchar with another part, and when using contatinate is it normallly used to delete the first character — t-clausen.dk, May 22 '15 at 12:35

score 3 · Answer 1 · answered May 22 '15 at 11:31

3

You can approach this in several ways. I typically use conditional aggregation:

select id,
       max(case when type = 'CC 1' then value end) as cc1,
       max(case when type = 'CC 2' then value end) as cc2,
       max(case when type = 'CC 3' then value end) as cc3,
       max(case when type = 'CC 4' then value end) as cc4
from <table t>
group by id;

answered May 22 '15 at 11:31

Gordon Linoff

1,242,037
58
646
786

Can we use stuff or pivot for this.. in other words i want the desired result using stuff or pivot. so please if u can help me.. – techV May 22 '15 at 11:40
@vivek . . . The reason I use conditional aggregation is because (1) it works in all databases and (2) it does produce unexpected results due to other columns in the table. I have no idea why this query would require `stuff()`. – Gordon Linoff May 22 '15 at 12:04

score 0 · Answer 2 · answered May 22 '15 at 11:29

FULL OUTER JOIN's:

select coalesce(t1.id, t2.id, t3.id, t4.id),
       t1.value as cc1,
       t2.value as cc2,
       t3.value as cc3,
       t4.value as cc4
from (select id, value from tablename where type = 'CC 1') as t1
  full outer join (select id, value from tablename
                   where type = 'CC 2') as t2 ON t1.id = t2.id
  full outer join (select id, value from tablename
                   where type = 'CC 3') as t3 ON t2.id = t3.id
  full outer join (select id, value from tablename
                   where type = 'CC 4') as t  ON t3.id = t4.id

score 0 · Answer 3 · edited May 22 '15 at 12:10

0

Try the following:

Select id, listagg(value, '-')  within group(order by type) over (partition by id) as txt
From table group by Id, txt

edited May 22 '15 at 12:10

jaypeagi

3,133
18
33

answered May 22 '15 at 11:58

Akhil

1

score 0 · Answer 4 · answered May 22 '15 at 12:33

0

Here is the syntax for pivot;

SELECT ID, [CC 1],[CC 2],[CC 3],[CC 4]
FROM yourtable
PIVOT
(min([value])  
FOR Type
in([CC 1],[CC 2],[CC 3],[CC 4])  
)AS p

answered May 22 '15 at 12:33

t-clausen.dk

43,517
12
59
92

Combine multiple rows into a single row in SQL

4 Answers4