Combining multiple rows into a single row

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Combining multiple rows into a single row

Author

Topic

zgall1
Starting Member

9 Posts

Posted - 2012-12-04 : 14:23:25

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
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-04 : 14:31:17

[code]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[/code]

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-04 : 14:38:50

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

zgall1
Starting Member

9 Posts

Posted - 2012-12-05 : 10:07:03

Thanks for the help. Works perfectly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources