SQL Flatten Group By to 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

General SQL Server Forums

New to SQL Server Programming

SQL Flatten Group By to single row

Author

Topic

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2015-03-26 : 10:03:44

I'm trying to generate a single record that shows the status of an accounting period for multiple groups. For a single accounting period they could all be the same, or all be different (open, closed, hold, etc)

What I want to do is select the accounting period with a group by that says if all the groups are in the same status, then that's the status. But if there are different status' for the period, I just want to have a single line that says 'Mixed' The query below returns 3 records, but I only want 2. I tried a CASE WHEN Having COUNT > 1 but it blew up with an aggregate subquery in a group by error.

DECLARE @AccountingPeriod Table
(AccountingPeriod varchar(10), Status char(1), GroupNum int)

Insert into @AccountingPeriod
Values('2015-03','O',1),
('2015-03','O',2),
('2015-03','O',3),
('2015-02','O',1),
('2015-02','O',2),
('2015-02','C',3)

Select AccountingPeriod, Status from @AccountingPeriod
GROUP BY AccountingPeriod, Status

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 10:12:46

Using your input, please post your desired output

mhorseman
Starting Member

44 Posts

Posted - 2015-03-26 : 10:13:51

Would this do it?

Select AccountingPeriod,
case when max(status) = min(status) then max(status) else 'Mixed' end
from @AccountingPeriod
GROUP BY AccountingPeriod

Mark

ITTrucker
Yak Posting Veteran

64 Posts

Posted - 2015-03-26 : 10:20:34

The desired output is what Mark's query returns. I forget about using Min/Max when dealing with characters.

Thanks Marks, just what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources