SQL pivot 1:M rows into string columns

Question

I have a 1:M recordset that I need to pivot into columns where the results are concatenated.

Create sample data:

CREATE TABLE #temptable 
(
    ID int,
    Division int,
    Material int
);

 insert into #temptable
 Values
 (999, 1, 1)
 ,(999, 1, 2)
 ,(999, 1, 3)
 ,(999, 2, 1)
 ,(999, 2, 6)
 ,(999, 3, 2)

Sample data:

ID     Division     Material   
----  ----------   ----------   
999       1            1          
999       1            2
999       1            3
999       2            1
999       2            6
999       3            2

I need the results to look like this, where the materials for each division are concatenated:

 ID   Division-1   Division-2   Division-3
----  ----------   ----------   ----------
999     1,2,3          1,6          2

did you try any thing ?

Prabhat G
– Prabhat G

2017-07-05 18:20:56 +00:00
Commented Jul 5, 2017 at 18:20 — Prabhat G
– Prabhat G, Commented Jul 5, 2017 at 18:20
And what version of SQL?

Shawn
– Shawn

2017-07-05 18:44:06 +00:00
Commented Jul 5, 2017 at 18:44 — Shawn
– Shawn, Commented Jul 5, 2017 at 18:44
Kamran's answer below solved the question.

ScottM
– ScottM

2017-07-05 20:25:40 +00:00
Commented Jul 5, 2017 at 20:25 — ScottM
– ScottM, Commented Jul 5, 2017 at 20:25

Prabhat G · Accepted Answer · 2017-07-06 06:10:48Z

1

Try this :

SELECT ID,
       [1] AS 'Division-1',
       [2] AS 'Division-2',
       [3] AS 'Division-3'
FROM
(
    SELECT DISTINCT
           a.ID,
           a.Division,
           LEFT(r.ResourceName, LEN(r.ResourceName) - 1) ResourceName
    FROM #temptable a
         CROSS APPLY
    (
        SELECT CAST(Material AS VARCHAR(10))+','
        FROM #temptable t
        WHERE a.[ID] = t.[ID]
              AND a.Division = t.Division
        FOR XML PATH('')
    ) r(ResourceName)
) src PIVOT(MAX(ResourceName) FOR Division IN([1],
                                              [2],
                                              [3])) piv;

brief explanation We need to create pivot over comma separated structure.

Below query converts column value into comma separated rows.

SELECT a.ID,
       a.Division,
       LEFT(r.ResourceName, LEN(r.ResourceName) - 1) ResourceName
FROM #temptable a
     CROSS APPLY
(
    SELECT CAST(Material AS VARCHAR(10))+','
    FROM #temptable t
    WHERE a.[ID] = t.[ID]
          AND a.Division = t.Division
    FOR XML PATH('')
) r(ResourceName);

After that we create pivot over it.

edited Jul 6, 2017 at 6:10

answered Jul 5, 2017 at 18:57

Prabhat G

3,0492 gold badges27 silver badges32 bronze badges

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

2 Comments

ScottM Over a year ago

This is excellent, way beyond my kung-fu level. Thank you very much.

Prabhat G Over a year ago

@ScottM : If it helped, mark it as answer in case people stumble upon

Collectives™ on Stack Overflow

SQL pivot 1:M rows into string columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related