Select query results on one line

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 2000 Forums

Transact-SQL (2000)

Select query results on one line

Author

Topic

galbrecht
Starting Member

17 Posts

Posted - 2008-02-06 : 19:50:16

Hi all

I would like to select data and have the results displayed in one line not three.

The query I am using is as follows

SELECT
CORP_MASTER__ID,
(case when CORP_MAST_LOS = 'G' then CORP_MAST_PROPORTION end) 'Growth',
(case when CORP_MAST_LOS = 'L' then CORP_MAST_PROPORTION end) 'Level of Service',
(case when CORP_MAST_LOS = 'R' then CORP_MAST_PROPORTION end) 'Renewals'

FROM CORP_MASTER__CORP_MAST_LOS

WHERE (CORP_MASTER__ID LIKE 'WS%')

but the result display three (3) rows prer ID (see beloa)

ID Growth LoL Renewal
WS01 0 NULL NULL
WS01 NULL 100 NULL
WS01 NULL NULL 0
WS02 0 NULL NULL
WS02 NULL 100 NULL
WS02 NULL NULL 0
WS03 100 NULL NULL
WS03 NULL 0 NULL
WS03 NULL NULL 0

I would like it to be displayed as follows

ID Growth LoL Renewal
WS01 0 100 0
WS02 0 100 0
WS03 100 0 0

Can anyone help please. Thank you

Greg

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-06 : 20:01:36

SELECT
CORP_MASTER__ID,
max((case when CORP_MAST_LOS = 'G' then CORP_MAST_PROPORTION end)) 'Growth',
max((case when CORP_MAST_LOS = 'L' then CORP_MAST_PROPORTION end)) 'Level of Service',
max((case when CORP_MAST_LOS = 'R' then CORP_MAST_PROPORTION end)) 'Renewals'

FROM CORP_MASTER__CORP_MAST_LOS

WHERE (CORP_MASTER__ID LIKE 'WS%')

GROUP BY CORP_MASTER__ID

Be One with the Optimizer
TG