Pivot table into multiple columns and multiple rows

Question

Background I am have a table which contains a varchar with a comma separated string (which has multiple rows similar to this question. Unfortunately I haven't been able to find a way to change a csv varchar into a temp table simply so I am looking into splitting it up myself. (I would also appreciate any answers tackling this, although maybe it should be a separate question?).

Question

I have successfully split the string up and am now producing an output similar to:

   -------------------------------------
   | Row Index  | Column Index | Value |
   -------------------------------------
   |     0      |      0       | (0,0) |
   |     0      |      1       | (1,0) |
   |     0      |      2       | (2,0) |
   |     1      |      0       | (0,1) |
   |     1      |      1       | (1,1) |
   |     1      |      2       | (2,1) |
   |     2      |      0       | (0,2) |
   |     2      |      1       | (1,2) |
   |     2      |      2       | (2,2) |
   -------------------------------------

I would like to pivot this so that I can insert it into a temp table and the final result would look like:

   -------------------------------------
   | Column 1  | Column 2  | Column 3  |
   -------------------------------------
   |   (0,0)   |   (1,0)   |   (2,0)   |
   |   (0,1)   |   (1,1)   |   (2,1)   |
   |   (0,2)   |   (1,2)   |   (2,2)   |
   -------------------------------------

Asides

The number of columns is known ahead of time but answers which don't depend on this are also welcome.
I know that I could repeatedly left outer join the first table to get this result but this requires outer joining once for every column (as I have ~9 columns this will be a lot of repetition) and I assume that there is another way.
Performance is not key but there will be ~2000 rows with 8 columns in the final table.

You may want to read this article that explains the canonical SQL pivot technique: modern-sql.com/use-case/pivot — Markus Winand
– Markus Winand, Commented Nov 8, 2017 at 16:13
Cheers Markus, I've had to tackle this issue before and wish I had come across that then (I previously had to go with the JOIN multiple times method). — Malrig
– Malrig, Commented Nov 8, 2017 at 16:40

SqlZim · Accepted Answer · 2017-11-08 16:00:38Z

1

using conditional aggregation:

select 
    RowIndex
  , Column1 = max(case when ColumnIndex=0 then Value end)
  , Column2 = max(case when ColumnIndex=1 then Value end)
  , Column3 = max(case when ColumnIndex=2 then Value end)
from t
group by RowIndex

rextester demo: http://rextester.com/QLPHR39222

returns:

+----------+---------+---------+---------+
| RowIndex | Column1 | Column2 | Column3 |
+----------+---------+---------+---------+
|        0 | (0,0)   | (1,0)   | (2,0)   |
|        1 | (0,1)   | (1,1)   | (2,1)   |
|        2 | (0,2)   | (1,2)   | (2,2)   |
+----------+---------+---------+---------+

or with pivot():

select 
    RowIndex
  , Column1 = [0]
  , Column2 = [1]
  , Column3 = [2]
from t
pivot (max(Value) for ColumnIndex in ([0],[1],[2])) p

answered Nov 8, 2017 at 16:00

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

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

1 Comment

Malrig Over a year ago

Thanks SqlZim, I saw similar answers all over the place but didn't think to add the group by to get multiple rows. I'm going to leave it a bit longer so i can have a play around and see if anyone has any other answers

Sentinel · Accepted Answer · 2017-11-08 16:30:18Z

1

This is a straightforward application of a PIVOT query (See The Docs):

select x.[0] Column1
     , x.[1] Column2
     , x.[2] Column3
  from YourData
 pivot (max(Value)
   for [Column Index]
    in ([0], [1], [2]) ) x
 order by x.[Row Index]

which Returns:

| Column1 | Column2 | Column3 |
|---------|---------|---------|
|   (0,0) |   (1,0) |   (2,0) |
|   (0,1) |   (1,1) |   (2,1) |
|   (0,2) |   (1,2) |   (2,2) |

SQL Fiddle

To add more columns simply add more column indexes to the FOR column IN (list) section, and add the same values to the projection (select list)

answered Nov 8, 2017 at 16:30

Sentinel

6,4871 gold badge20 silver badges24 bronze badges

1 Comment

Malrig Over a year ago

Thanks, I couldn't quite get my head around the docs. I'll take a look at your example and try to make sure it sticks.

Collectives™ on Stack Overflow

Pivot table into multiple columns and multiple rows

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related