case multiple rows - SQL Server Forums

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)

case multiple rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-03-08 : 08:52:16

james writes "when i end the case statement on each line, it returns multiple rows and i cannot get them to mix into one line, i.e. being just grouped by the a.linvoice.

Is there any way of doing this?

select a.invoice,
case when c.petype = 'type1' THEN c.pevalue end as type1,
case when c.petype = 'type2' THEN c.pevalue end as type2
case when c.petype = 'type3' THEN c.pevalue end as type3,
case when c.petype = 'type4' THEN c.pevalue end as type4,
case when c.petype = 'type5' THEN c.pevalue end as type5,
case when c.petype = 'type6' THEN c.pevalue end as type6
from #temp a inner join prov b
on a.invoice=b.phinvoice
inner join provextra c
on b.phindex=c.phindex
where c.petype like 'type%'
--group by a.invoice,c.petype,c.pevalue"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-03-08 : 09:54:16

You need to aggregate (summarize) the columns you do not wish to group on. So, in your case, you need to SUM or MIN or MAX each of those CASE expressions, I assume (you did not make it clear what your needs are).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources