Converting multiple rows into one 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 2005 Forums

Transact-SQL (2005)

Converting multiple rows into one row

Author

Topic

shaunna
Starting Member

8 Posts

Posted - 2008-07-29 : 18:48:02

I have a table that looks like the following with thousands of work items in it. Basically as a work item moves through the process a record is inserted for each time its status is changed:


ID   Status DateUpdated
1866 New    05/05/08
1866 Dev    05/09/08
1866 QA     05/11/08
1866 Closed 05/13/08
1867 New    06/03/08
1867 Dev    06/05/08
1867 QA     06/08/08
1867 Closed 06/10/08

What I really need is to take that data and put it into this format in a new table for every single work item. The new table has a column for each status a work item can pass through:


ID    New       Dev       QA        Closed
1866  05/05/08  05/09/08  05/11/08  05/13/08
1867  06/03/08  06/05/08  06/08/08  06/10/08

I know I could do this with a lot of looping and inserting, but was just wondering if there is an easier way?

I'm no T-sql guru by any means so I thought I'd check before I built some insanely cumbersome process to loop through each work item and do an insert into the new table for each status. If you have any suggestions, please let me know.

Thanks in advance!!!

Shaunna

singularity
Posting Yak Master

153 Posts

Posted - 2008-07-29 : 20:30:04

select ID, case when status = 'New' then DateUpdated end as New, case when status = 'Dev' then DateUpdated end as Dev, case when status = 'QA' then DateUpdated end as QA, case when status = 'Closed' then DateUpdated end as Closed
from yourtable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 00:21:45

[code]SELECT ID,
MAX(CASE WHEN Status='New' THEN DateUpdated ELSE NULL END) AS New,
MAX(CASE WHEN Status='Dev' THEN DateUpdated ELSE NULL END) AS Dev,
MAX(CASE WHEN Status='QA' THEN DateUpdated ELSE NULL END) AS QA,
MAX(CASE WHEN Status='Closed' THEN DateUpdated ELSE NULL END) AS Closed
FROM Table
GROUP BY ID[/code]

shaunna
Starting Member

8 Posts

Posted - 2008-07-30 : 12:47:30

Perfect! Thank you so much. I LOVE case statements (I didn't realize how wonderful they are, very cool)

I cannot tell you how awful my code would have been. Lots of nested looping most likely.

shaunna
Starting Member

8 Posts

Posted - 2008-08-07 : 13:41:35

Ruh roh. I thought this was working, but it's not. My bad.

I didn't account for the fact a status can be in the database multiple times like so for a given work item, because each time a user enters notes on a work item a new record is inserted into the table, with the existing status:

ID Status DateUpdated
1866 New 05/05/08
1866 New 05/07/08
1866 Dev 05/09/08
1866 Dev 05/10/08
1866 Dev 05/11/08
1866 QA 05/11/08
1866 QA 05/11/08
1866 Closed 05/13/08
1867 New 06/03/08
1867 Dev 06/05/08
1867 Dev 06/07/08
1867 QA 06/08/08
1867 Closed 06/10/08

I've been trying to work it out with what you guys originally suggested, but so far I'm not getting back what I'd expect. Which is of course still something like the following (with the date needing to be the minimum date found for each status):

ID New Dev QA Closed
1866 05/05/08 05/09/08 05/11/08 05/13/08
1867 06/03/08 06/05/08 06/08/08 06/10/08

The code I currently have only seems to bring back one date, and it's the data associated to the status with the last date entry of all the records:

--Get minimum date for each status
SELECT
MIN(CASE WHEN Status='New' THEN DateUpdated ELSE NULL END) AS New,
MIN(CASE WHEN Status='Dev' THEN DateUpdated ELSE NULL END) AS Dev,
MIN(CASE WHEN Status='QA' THEN DateUpdated ELSE NULL END) AS QA,
MIN(CASE WHEN Status='Closed' THEN DateUpdated ELSE NULL END) AS Closed
FROM [Work Item]
GROUP BY ID

I guess maybe a case statement isn't appropriate any more since there is more than one instance of each status for a work item?

Thanks,

Shaunna

I have just enough knowledge to be dangerous. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 13:46:03

quote:
Originally posted by shaunna

Ruh roh. I thought this was working, but it's not. My bad.

I didn't account for the fact a status can be in the database multiple times like so for a given work item, because each time a user enters notes on a work item a new record is inserted into the table, with the existing status:

ID Status DateUpdated
1866 New 05/05/08
1866 New 05/07/08
1866 Dev 05/09/08
1866 Dev 05/10/08
1866 Dev 05/11/08
1866 QA 05/11/08
1866 QA 05/11/08
1866 Closed 05/13/08
1867 New 06/03/08
1867 Dev 06/05/08
1867 Dev 06/07/08
1867 QA 06/08/08
1867 Closed 06/10/08

I've been trying to work it out with what you guys originally suggested, but so far I'm not getting back what I'd expect. Which is of course still something like the following (with the date needing to be the minimum date found for each status):

ID New Dev QA Closed
1866 05/05/08 05/09/08 05/11/08 05/13/08
1867 06/03/08 06/05/08 06/08/08 06/10/08

The code I currently have only seems to bring back one date, and it's the data associated to the status with the last date entry of all the records:

--Get minimum date for each status
SELECT
MIN(CASE WHEN Status='New' THEN DateUpdated ELSE NULL END) AS New,
MIN(CASE WHEN Status='Dev' THEN DateUpdated ELSE NULL END) AS Dev,
MIN(CASE WHEN Status='QA' THEN DateUpdated ELSE NULL END) AS QA,
MIN(CASE WHEN Status='Closed' THEN DateUpdated ELSE NULL END) AS Closed
FROM [Work Item]
GROUP BY ID

I guess maybe a case statement isn't appropriate any more since there is more than one instance of each status for a work item?

Thanks,

Shaunna

I have just enough knowledge to be dangerous. :)

so what is the output you are looking for out of aboe sample data?

shaunna
Starting Member

8 Posts

Posted - 2008-08-07 : 13:55:28

The same as I originally was:

ID New Dev QA Closed
1866 05/05/08 05/09/08 05/11/08 05/13/08
1867 06/03/08 06/05/08 06/08/08 06/10/08

But since there now can be more than one record for each status, I would need the date returned to be the minimum date found in the recordset for each status.

I have just enough knowledge to be dangerous. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-07 : 14:06:36

quote:
Originally posted by shaunna

The same as I originally was:

ID New Dev QA Closed
1866 05/05/08 05/09/08 05/11/08 05/13/08
1867 06/03/08 06/05/08 06/08/08 06/10/08

But since there now can be more than one record for each status, I would need the date returned to be the minimum date found in the recordset for each status.

I have just enough knowledge to be dangerous. :)

but thats exactly what this query does for you

SELECT ID,
MIN(CASE WHEN Status='New' THEN DateUpdated ELSE NULL END) AS New,
MIN(CASE WHEN Status='Dev' THEN DateUpdated ELSE NULL END) AS Dev,
MIN(CASE WHEN Status='QA' THEN DateUpdated ELSE NULL END) AS QA,
MIN(CASE WHEN Status='Closed' THEN DateUpdated ELSE NULL END) AS Closed
FROM [Work Item] 
GROUP BY ID

shaunna
Starting Member

8 Posts

Posted - 2008-08-07 : 15:28:54

Hmmmmm, that's definitely not what it's returning though, so something else must be up that I'm not getting.

I only ever get one column with a date in it for each and every work item. It appears to me it might just be grabbing the date and status for the last status it's been in, but I'm not entirely sure at this moment. For example, I have work item 1809's records in the base table like so:

ID Status DateUpdated
1809 New 06/25/2008
1809 Dev 06/26/2008
1809 Dev 06/27/2008
1809 Dev 06/27/2008
1809 QA 07/07/2008
1809 QA 07/08/2008
1809 QA 07/07/2008
1809 QA 08/05/2008

The row that gets created by the case statement for work item 1809 is coming back looking like the following:

ID New Dev QA Closed
1809 NULL NULL 08/05/2008 NULL

I have just enough knowledge to be dangerous. :)

shaunna
Starting Member

8 Posts

Posted - 2008-08-07 : 15:42:11

Oh, I think I found it, stupid user trick. I was running an earlier version of the query above and it had an extra field in the select that was throwing everything off. I stripped it out and it worked as expected. Doh!

I have just enough knowledge to be dangerous. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources