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

@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
– techV, Commented May 22, 2015 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
– t-clausen.dk, Commented May 22, 2015 at 12:35

Gordon Linoff · Accepted Answer · 2015-05-22 11:31:21Z

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, 2015 at 11:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

techV Over a year ago

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..

Gordon Linoff Over a year ago

@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().

jarlh · Accepted Answer · 2015-05-22 11:29:51Z

0

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

answered May 22, 2015 at 11:29

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Comments

jaypeagi · Accepted Answer · 2015-05-22 12:10:23Z

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, 2015 at 12:10

jaypeagi

3,14120 silver badges33 bronze badges

answered May 22, 2015 at 11:58

Akhil

1

Comments

t-clausen.dk · Accepted Answer · 2015-05-22 12:33:13Z

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, 2015 at 12:33

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Collectives™ on Stack Overflow

Combine multiple rows into a single row in SQL

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related