SQL Converting multiple rows to multiple columns

Question

I have the following:

DECLARE @tmp TABLE (cid int, pid int, name varchar(10))

Insert Into @tmp (cid, pid, name)
Select 31, 18, 'Pot_One' UNION ALL
Select 32, 18, 'Pot_One' UNION ALL
Select 31, 19, 'Pot_Two' UNION ALL
Select 32, 19, 'Pot_Two' UNION ALL
Select 33, 19, 'Pot_Two' UNION ALL
Select 40, 20, 'Pot_Three'

This describes multiple cid's becoming together to create a pot, for instance 31 and 32 are in pot one and pot id for it is 18, we can have a any number of cid's in a pot (minimum 1 and no max)

What I am trying to achieve is to retrieve pots and relevant cid's in a row, so I thought of using pivot and tried this:

SELECT * from 
(
    select cid, pid, name
    from @tmp
) x
pivot 
(
    min(cid)
    for pid in ([18],[19],[20])
) p

But this returns the following:

name         18     19      20
Pot_One      31     NULL    NULL
Pot_Three    NULL   NULL    40
Pot_Two      NULL   31      NULL

Thats is wrong because pot one has 31 and 32 in it and this only represents 31, what I need is potentially:

name         18     18      19    19    19    20
Pot_One      31     32      NULL  NULL  NULL  NULL
Pot_Three    NULL   NULL    NULL  NULL  NULL  40
Pot_Two      NULL   NULL    31    32    33    NULL

I have changed the pivot to get the cid's as column headers and got this, which is more closer to what I am trying to achieve:

SELECT * from 
(
    select cid, pid, name
    from @tmp
) x
pivot 
(
    min(pid)
    for cid in ([31],[32],[33],[40])
) p 

name        31    32        33    40
Pot_One     18    18        NULL  NULL
Pot_Three   NULL  NULL      NULL  20
Pot_Two     19    19        19    NULL

I tried a few different ways without success, are there any other way of achieving this?

Thanks

This looks to be a bit of a challenge but also very interesting. Can you please provide your desired output? — Sean Lange
– Sean Lange, Commented Apr 14, 2016 at 19:45
Thanks for looking. What I need is to be able to display somehow cid 31 and 32 is in pot 18 and cid 31,32 and 33 is in pot 19 and cid 40 is in pot 20. — 03Usr
– 03Usr, Commented Apr 14, 2016 at 20:06

DhruvJoshi · Accepted Answer · 2016-04-14 21:14:34Z

Here's an alternative solution.

Basically pot id and pot name are two names for same thing, they do not vary, So basically 18 (Pot_One) should be good enough indicator for same thing and then you can create a two D map between this indicator and the cid values to show if they are in it or not like below

SELECT * from 
(
    select cid, cast(pid as varchar) +' ('+ name +')' as name, 1 r
    from @tmp
) x
pivot 
(
    min(r)
    for cid in ([31],[32],[33],[40])
) p

Output:

name           31          32          33          40
-------------------------- ----------- ----------- -----------
18 (Pot_One)   1           1           NULL        NULL
19 (Pot_Two)   1           1           1           NULL
20 (Pot_Three) NULL        NULL        NULL        1

More over you can replace NULL with say 'X' by modifying like below

SELECT name, ISNULL([31], 'x') [31],ISNULL([32],'x') [32],ISNULL([33],'x') [33],ISNULL([40],'x') [40] 
from 
(
    select cid, cast(pid as varchar) +' ('+ name +')' as name, '1' r
    from @tmp
) x
pivot 
(
    min(r)
    for cid in ([31],[32],[33],[40])
) p

output

name            31   32   33   40
--------------- ---- ---- ---- ----
18 (Pot_One)    1    1    x    x
19 (Pot_Two)    1    1    1    x
20 (Pot_Three)  x    x    x    1

03Usr · Accepted Answer · 2016-04-14 21:08:49Z

1

Problem is the min(cid) in the pivot, it won't give you all entries. Switched to pivoting over pid, giving multiple cid results. Subqueried that and replaced values with cid value where result is not NULL.

DECLARE @tmp TABLE (cid int, pid int, name varchar(10))

Insert Into @tmp (cid, pid, name)
Select 31, 18, 'Pot_One' UNION ALL
Select 32, 18, 'Pot_One' UNION ALL
Select 31, 19, 'Pot_Two' UNION ALL
Select 32, 19, 'Pot_Two' UNION ALL
Select 33, 19, 'Pot_Two' UNION ALL
Select 40, 20, 'Pot_Three' 

SELECT name, CASE WHEN [18] IS NOT NULL THEN CID END AS [18], CASE WHEN [19] IS NOT NULL THEN CID END AS [19], CASE WHEN [20] IS NOT NULL THEN CID END AS [20]
FROM
(
SELECT * from 
(
    select cid, pid, name
    from @tmp
) x
pivot 
(
    min(pid)
    for pid in ([18],[19],[20])
) p 
) x

edited Apr 14, 2016 at 21:08

03Usr

3,4456 gold badges43 silver badges64 bronze badges

answered Apr 14, 2016 at 20:52

dbbri

2241 silver badge7 bronze badges

Collectives™ on Stack Overflow

SQL Converting multiple rows to multiple columns

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related