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

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

zgall1
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.

sodeep
Flowing Fount of Yak Knowledge

USA
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

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

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

INSERT INTO @Table
VALUES
('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

Jim

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

zgall1
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  
 New 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.06 seconds. Powered By: Snitz Forums 2000