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 occurred. To start you want to get the maximum value for each date column.  You write a query like this:
[sql]
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
[/sql]

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:
[sql]
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
[/sql]
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