help with 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

General SQL Server Forums

New to SQL Server Programming

help with multiple rows into one row

Author

Topic

hayleye
Starting Member

6 Posts

Posted - 2010-06-14 : 09:01:11

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-14 : 09:23:44

"Can anyone please help?"

Maybe...post the query, table DDL and some sample data in DML Context

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

hayleye
Starting Member

6 Posts

Posted - 2010-06-14 : 10:01:20

Sorry about that. Im using SQL 2005

My query is like this:
select account_group_name,
accountgroup.code,
SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11) AS account_date,
case when accountgroup.code = 'Q' and (SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11)) like '31/03/%' then 'March'
when accountgroup.code = 'Q' and (SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11)) like '30/06/%' then 'June'
when accountgroup.code = 'Q' and (SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11)) like '30/09/%' then 'September'
when accountgroup.code = 'Q' and (SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11)) like '31/12/%' then 'December'
else 'Not Received' end as status
from account

Is this possible to put it on one row? possibly a coalesce function? Im not 100% sure on how to do it though.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-14 : 10:07:13

Try this

select account_group_name, 
accountgroup.code,
SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11) AS account_date,
max(case when accountgroup.code = 'Q' and (SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11)) like '31/03/%' then 'March'
when accountgroup.code = 'Q' and (SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11)) like '30/06/%' then 'June'
when accountgroup.code = 'Q' and (SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11)) like '30/09/%' then 'September'
when accountgroup.code = 'Q' and (SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11)) like '31/12/%' then 'December'
else 'Not Received' end) as status
from account
group by account_group_name, 
accountgroup.code,
SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11)

Also why are you changing account_date to a varchar and doing the compares. Chk out DATEPART and DATENAME funcitons.

hayleye
Starting Member

6 Posts

Posted - 2010-06-14 : 10:11:43

our dates have times in them which i dont need and the report im doing is for quarter periods that the accounts werent received.
This is then going in ssrs.
Ill try that, hopefully it will work.

hayleye
Starting Member

6 Posts

Posted - 2010-06-14 : 10:19:58

Also, do I need to rather have 4 case statements so that each of the 4 months are on their own line because some acount have 3 of the 4 months received?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-14 : 10:21:01

What constitues a "Not Received" value ...I did not get that part.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-14 : 10:22:00

This should give you some idea

select account_group_name, 
accountgroup.code,
SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11) AS account_date,
max(case when accountgroup.code = 'Q' and datename(month,account_date) = 'March' then 'Received' else null end) as 'March'
max(case when accountgroup.code = 'Q' and datename(month,account_date) = 'June' then 'Received' else null end) as 'June'
max(case when accountgroup.code = 'Q' and datename(month,account_date) = 'September' then 'Received' else null end) as 'September'
max(case when accountgroup.code = 'Q' and datename(month,account_date) = 'December' then 'Received' else null end) as 'December'
from account
group by account_group_name, 
accountgroup.code,
SUBSTRING(CONVERT(varchar(20),(account_date), 103), 0, 11)

hayleye
Starting Member

6 Posts

Posted - 2010-06-14 : 10:22:27

Not received is when the account has not been received for the quarter? it just what i put in as my else in the case. This is to see which accounts are missing for which quarters.

hayleye
Starting Member

6 Posts

Posted - 2010-06-14 : 10:29:47

Thank you, its working now, I used datepart to bring in the year from my account_date fields and grouped it on that.
Now i have one line per year.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-14 : 10:32:06

Great. You are welcome.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources