Home > SQL Server > SQL 2005 Unpivot Query

SQL 2005 Unpivot Query

The other day I had need to use an unpivot query in SQL 2005. Thought it was really cool so here it is…

Assume you have the following tables

  • tblFolder: represents a folder, primary key ID.
  • tblFolderItem: many to many join between folder and item
  • tblItem: a table that represents an item. It has the following columns, id, date1, date2, date3.

Therefore the folder can contain many items and each item has their own set of dates. What I want is the maximum date of all dates for all items in stored against each folder. So lets begin…

The item has three different date fields that represent different dates that certain actions occured. To start you want to get the maximum value for each date column.  You write a query like this:

select
    f.id [folderId],
    max(i.date1) [maxDate1],
    max(i.date2) [maxDate2],
    max(i.date3) [maxDate3]
from tblFolder f
    inner join tblFolderItem fi on fi.folderId = f.id
    inner join tblItem i on fi.itemId = i.id
group by f.id

This will give you results like:
1, dateA, dateB, dateC
2, dateX, dateY, dateZ.

But what you really want is the max of dateA/B & C for folder 1 and max of date X/Y/Z for folder 2.

This is where unpivot comes in, adjust your query like this:

select
    folderId,
    Max(XXXX) as maxDate
from
(
    select
        f.id [folderId],
        max(i.date1) [maxDate1],
        max(i.date2) [maxDate2],
        max(i.date3) [maxDate3]
    from tblFolder f
        inner join tblFolderItem fi on fi.folderId = f.id
        inner join tblItem i on fi.itemId = i.id
    group by f.id
) AS tblDates
UNPIVOT
    (XXXX FOR id IN
        (maxDate1, maxDate2, maxDate3)
    )AS unpvt
group by folderId

The result of the unpivot query is to return a table of results like:

id, MaxDate
1, dateA
1, dateB
1, dateC
2, dateX
2, dateY
2, dateZ

now you do the group by on the id, and the Max on your column name (XXXX) and you are done.

The XXXX can obviously be any name for a column you want, as long as it does not conflict with other columns in your base query.

Cheers

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: