How to collapse staggered records to a single row? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to collapse staggered records to a single row?

Author

Topic

mivey4
Yak Posting Veteran

66 Posts

Posted - 2011-02-10 : 21:14:29

Crazy subject line I admit. But I have a query that produces 4 records for the same date key because I have a created 3 new conditional date fields using CASE STATEMENTS in my query that results in different values 2,3 and 4 months away from the original date. I had to do this because I am joining to another table that has multiple values for the same date.

To make it simpler to understand, the results are similar to:

Date CasesToday 2MonthsAgo 3MonthsAgo 4MonthsAgo
2/10/2011 | 30.0 | NULL | NULL | 23.1
2/10/2011 | 30.0 | NULL | 76.0 | NULL
2/10/2011 | 30.0 | 45.2 | NULL | NULL
2/10/2011 | 30.0 | NULL | NULL | NULL

I want to collapse these values to 1 simple record:

Date CasesToday 2MonthsAgo 3MonthsAgo 4MonthsAgo
2/10/2011 | 30.0 | 45.2 | 76.0 | 23.1

Is there any way this could be accomplished without creating another table??

Any help is appreciated. I really need an answer ASAP! plz

Oracle OCA
Adaptec ACSP

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-02-10 : 21:18:54

use GROUP BY with MAX() or SUM() depending on your requirement

KH
[spoiler]Time is always against us[/spoiler]

mivey4
Yak Posting Veteran

66 Posts

Posted - 2011-02-10 : 21:30:56

Could you provide a little more detail to your suggestion khtan??? Given the example of my problem, where would you group???

Hmm... Let me think on that a bit but I don't readily see how I'd accomplish it.

Thanks for replying!

Oracle OCA
Adaptec ACSP

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-10 : 21:42:50

SELECT DATE,CasesToday,
SUM(ISNULL([2monthsago],0) as [2monthsago],
SUM(ISNULL([2monthsago],0) as [3monthsago],
SUM(ISNULL([2monthsago],0) as [4monthsago]
FROM [TABLE]
GROUP BY Date,CasesToday

for example.

Poor planning on your part does not constitute an emergency on my part.

mivey4
Yak Posting Veteran

66 Posts

Posted - 2011-02-10 : 22:12:18

Thank you dataguru1971!

I'm tired

and needed to get this figured out quickly so I'm certain I would have spent more time than I'd desire trying to catch khtan's suggestion.

You guys rock!

Oracle OCA
Adaptec ACSP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources