| Author |
 Topic  |
|
|
zgall1
Starting Member
6 Posts |
 Posted - 12/04/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/04/2012 : 14:31:17
|
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 |
 |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 12/04/2012 : 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
6 Posts |
Posted - 12/05/2012 : 10:07:03
|
| Thanks for the help. Works perfectly. |
|
|
| |
Topic |
|
|
|
Combining multiple rows into a single row
