CODEWITHSUNDEEP
sqlsql-serverrowsunpivot
Matt Williamson
Matt Williamson

Reputation: 7095

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.

Upvotes: 1

Views: 619

Answers (2)

Taryn
Taryn

Reputation: 247710

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269873

If your tables aren't that big, the easiest way to probably an explicit union all:

select t.*
from (select eqnum, photo1 as photo, description from table1 union all
      select eqnum, photo2, description from table1 union all
      select eqnum, photo3, description from table1 union all
      select eqnum, photo4, description from table1 union all
      select eqnum, photo5, description from table1
     ) t
where photo is not null;

You can add an into clause to create a table. This version will read the table five times. Unpivot is more efficient, but that only really makes a difference if your table is somewhat large table, say hundreds of thousands of rows or more.

Upvotes: 2

Related Questions