SQL reshaping rows to columns

Question

I have a table with the following columns :

Name, Img1, Img2

A snap shot of the data looks like this :

Stack, lemon.jpg, null
Stack, null, orange.jpg
Stack,plum.jpg, null
Stack,grape.jpg, aubergine.jpg

There is no limit for the number of images associated with each name.

But, I want to return the first ten images like below :

select name, img1, img2, img3, img4, img5, img6 ... img10

Perhaps you should redesign your database schema. Why do you want the data in columns, it seems to be sparsly populated and infinite. — Jodrell, Jul 15 '13 at 15:05
See the question: http://stackoverflow.com/questions/2113251/tsql-help-with-unpivot — , Jul 15 '13 at 15:08
how do you order the data in the snap shot? what makes `lemon.jpg, null` come before `null, orange.jpg`? — Jodrell, Jul 15 '13 at 15:09
@NimChimpsky, so its any 10, not the first 10 and the result is non deterministic. — Jodrell, Jul 15 '13 at 15:24

score 2 · Accepted Answer · answered Jul 15 '13 at 15:13

Since the data in your table is denormalized, I would suggest unpivoting the data first, then applying the PIVOT function to convert the values back into the final result that you want.

The first step is to unpivot the img1 and img2 columns, into multiple rows:

select d.name,
  c.value,
  c.col 
    + cast(row_number() over(partition by d.name 
                             order by d.name) as varchar(10)) new_col
from yourtable d
cross apply
(
  select 'img', img1 union all
  select 'img', img2
) c (col, value);

See Demo. The unpivot process will give you a result:

|  NAME |         VALUE | NEW_COL |
-----------------------------------
| Stack |     lemon.jpg |    img1 |
| Stack |        (null) |    img2 |
| Stack |        (null) |    img3 |
| Stack |    orange.jpg |    img4 |
| Stack |      plum.jpg |    img5 |
| Stack |        (null) |    img6 |
| Stack |     grape.jpg |    img7 |
| Stack | aubergine.jpg |    img8 |

Then you can apply the pivot function to the values in new_col, which was calculated by using row_number() partitioned by the name:

select name, img1, img2, img3, img4, img5,
  img6, img7, img8, img9, img10
from
(
  select d.name,
    c.value,
    c.col 
      + cast(row_number() over(partition by d.name 
                               order by d.name) as varchar(10)) new_col
  from yourtable d
  cross apply
  (
    select 'img', img1 union all
    select 'img', img2
  ) c (col, value)
) src
pivot
(
  max(value)
  for new_col in (img1, img2, img3, img4, img5,
                  img6, img7, img8, img9, img10)
) piv;

See SQL Fiddle with Demo

Jodrell · Answer 2 · 2013-07-15T16:11:41.400

how about,

Now with working fiddle

    WITH snapshot(n, name, img1, img2) AS
(
    SELECT TOP 5
                ROW_NUMBER() OVER(ORDER BY Name) n,
                name,
                img1,
                img2
        FROM yourtable
        WHERE name = 'Stack'
        ORDER BY Name    
)
SELECT
             row1.Name,
             row1.Img1,
             row1.Img2,
             row2.Img1 Img3,
             row2.Img2 Img4,
             row3.Img1 Img5,
             row3.Img2 Img6,
             row4.Img1 Img7,
             row4.Img2 Img8,
             row5.Img1 Img9,
             row5.Img2 Img10
    FROM snapshot row1
        LEFT JOIN snapshot row2 ON row2.n = 2
        LEFT JOIN snapshot row3 ON row3.n = 3
        LEFT JOIN snapshot row4 ON row4.n = 4
        LEFT JOIN snapshot row5 ON row5.n = 5
    WHERE row1.n = 1

This works by defining a CTE,

    SELECT TOP 5
                ROW_NUMBER() OVER(ORDER BY Name) n,
                name,
                img1,
                img2
        FROM yourtable
        WHERE name = 'Stack'
        ORDER BY Name

The results of which could be

N  NAME   IMG1       IMG2 
1  Stack  lemon.jpg  (null) 
2  Stack  (null)     orange.jpg 
3  Stack  plum.jpg   (null) 
4  Stack  grape.jpg  aubergine.jpg

It then selects the first row and does a left self join for each subsequent pair that is required.

SQL reshaping rows to columns

2 Answers2