Search code examples
sqlsql-serverrowsunpivot

SQL Query columns to rows - Unpivot?

It has been awhile since I've done any T-SQL. I'm stuck trying to get a table with this format:

Table 1

Equipment Number    Description Photo01 Photo02 Photo03 Photo04 Photo05
02M1.1  GRIT COLLECTOR  BC1DE498-F222-404B-AF32-50886FD8524D.jpg    2E005CE8-2B72-4744-B707-4B5F9B3626E9.jpg    44888857-B8C1-41BC-BED1-D301FBF98C16.jpg    NULL    NULL
02M1.2  GRIT COLLECTOR  10C3E477-F7E5-49A8-8E86-2641B04A57D9.jpg    A3E66016-EEBF-4543-972F-B04ABD753D0A.jpg    NULL    NULL    NULL

to look like this:

Table 2

EQNUM    Filename                                            Description
02M1.1   x:\Photos\BC1DE498-F222-404B-AF32-50886FD8524D.jpg  GRIT COLLECTOR
02M1.1   x:\Photos\2E005CE8-2B72-4744-B707-4B5F9B3626E9.jpg  GRIT COLLECTOR
02M1.1   X:\Photos\44888857-B8C1-41BC-BED1-D301FBF98C16.jpg  GRIT COLLECTOR
02M1.2   X:\Photos\10C3E477-F7E5-49A8-8E86-2641B04A57D9.jpg  GRIT COLLECTOR
02M1.2   X:\Photos\A3E66016-EEBF-4543-972F-B04ABD753D0A.jpg  GRIT COLLECTOR

There can be anywhere from 0-5 pictures in Table1.

I'm working with SQL 2008 R2. I think I have to use UNPIVOT but haven't had any success with it so far.

Any help would be appreciated.

Solution

  • You can use the UNPIVOT function to convert the columns into rows:

    select EquipmentNumber,
  FileName,
  DESCRIPTION
from yourtable
unpivot
(
  FileName 
  for Photo In (Photo01, Photo02, Photo03, Photo04, Photo05)
) u

    See Demo.

    Since you are using SQL Server 2005+ you can also use CROSS APPLY to transpose the data. The VALUES clause became available in SQL Server 2008, prior you'd have to use a UNION ALL version:

    select EquipmentNumber, FileName, DESCRIPTION
from yourtable
cross apply
(
  values
    ('Photo01', Photo01),
    ('Photo02', Photo02),
    ('Photo03', Photo03),
    ('Photo04', Photo04),
    ('Photo05', Photo05)
) c (photo, FileName)
where FileName is not null;

    See Demo