Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Combining multiple rows into a single row
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

9 Posts

Posted - 12/04/2012 :  14:23:25  Show Profile  Reply with Quote
I have a dataset that looks like the one below, where project stage is a value from 1-7.

Project # Project Stage Date Updated
123ABCDE Stage 1 1/1/2012
123ABCDE Stage 1 2/1/2012
456ABCDE Stage 1 1/1/2012
456ABCDE Stage 6 2/1/2012
789ABCDE Stage 1 1/1/2012
789ABCDE Stage 4 2/1/2012

The goal is to have one row per project with seven columns for the stage, each containing the date when the project first entered the given stage or NA in cases where the project skipped the stage.

Can someone help me out with the SQL code required? I used to be quite good with SQL but it has been a number of years since I coded regularly and I can't seem to remember how to approach a problem such as this one.

Flowing Fount of Yak Knowledge

7174 Posts

Posted - 12/04/2012 :  14:31:17  Show Profile  Reply with Quote
Select [Project#],Project,
MAX(Case When Stage = 'Stage1' then DateUpdated Else 'N/A' End)as Date1,
MAX(Case When Stage = 'Stage2' then DateUpdated Else 'N/A' End)as Date2,
MAX(Case When Stage = 'Stage7' then DateUpdated Else 'N/A' End)as Date7
from Table
Group by [Project#],Project
Order by [Project#],Project

Edited by - sodeep on 12/04/2012 14:32:11
Go to Top of Page

Flowing Fount of Yak Knowledge

2875 Posts

Posted - 12/04/2012 :  14:38:50  Show Profile  Reply with Quote
DECLARE @Table TABLE(Project char(8),Stage varchar(10),Updated date)

('123ABCDE','Stage 1','1/1/2012'),
('123ABCDE','Stage 1','2/1/2012'),
('456ABCDE','Stage 1','1/1/2012'),
('456ABCDE','Stage 6','2/1/2012'),
('789ABCDE','Stage 1','1/1/2012'),
('789ABCDE','Stage 4','2/1/2012')

select Project ,pvt.*
from @table

PIVOT( max(Updated) For Stage in ([Stage 1],[Stage 2],[Stage 3],[Stage 4],[Stage 5],[Stage 6],[Stage 7])
) pvt

Don't use NA. That changes your data type from a date to a string. Let the fron-end do that


Everyday I learn something that somebody else already knew
Go to Top of Page

Starting Member

9 Posts

Posted - 12/05/2012 :  10:07:03  Show Profile  Reply with Quote
Thanks for the help. Works perfectly.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000