How to convert columns to rows? - 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

General SQL Server Forums

Database Design and Application Architecture

How to convert columns to rows?

Author

Topic

Syima
Starting Member

13 Posts

Posted - 2007-11-13 : 05:30:08

Hi there

Assuming I have the following data where the header represents budget cost and the value represent no. of projects

<2K 2K-5K >5K
--------------------
10 15 5

For the above table: the following is my sql:

SELECT

SUM (CASE WHEN PRJ.BDGT_CST_TOTAL<2000000 THEN 1 ELSE 0 END) <2K,
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 THEN 1 ELSE 0 END) 2K-5K,
SUM (CASE WHEN PRJ.BDGT_CST_TOTAL>=5000000 THEN 1 ElSE 0 END) >5K

FROM

PRJ_PROJECTS AS PRJ

How do I program it in sql such that the data will be displayed as below? Thanks

Budget_Range No_of_prj
-----------------------
<2K 10
2K-5K 15
>5K 5

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-13 : 05:43:32

Try



Select 
CASE 
	WHEN PRJ.BDGT_CST_TOTAL<2000000 then '<2k'
	WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 then '2k-5k'
	WHEN PRJ.BDGT_CST_TOTAL>=2000000 then '>5k'
END as Budget_Range, count(*) as No_of_prj
from table PRJ
group by
CASE 
	WHEN PRJ.BDGT_CST_TOTAL<2000000 then '<2k'
	WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 then '2k-5k'
	WHEN PRJ.BDGT_CST_TOTAL>=2000000 then '>5k'
END

Madhivanan

Failing to plan is Planning to fail

Syima
Starting Member

13 Posts

Posted - 2007-11-13 : 19:52:47

hi there...

it works. didnt know that we r allowed to use case stmt in group by clause. anyway, tq again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-14 : 02:28:27

quote:
Originally posted by Syima

hi there...

it works. didnt know that we r allowed to use case stmt in group by clause. anyway, tq again.

Well. It seems you are lover of chat or SMS

Madhivanan

Failing to plan is Planning to fail

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-16 : 15:51:33

quote:

Originally posted by madhivanan


group by
CASE 
	WHEN PRJ.BDGT_CST_TOTAL<2000000 then '<2k'
	WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 then '2k-5k'
	WHEN PRJ.BDGT_CST_TOTAL>=2000000 then '>5k'
END

Madhi, thanks for this one. I never knew you could use CASE in a GROUP BY, and there were times I did nasty UNIONs to accomplish the same thing.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-19 : 02:05:17

quote:
Originally posted by KenW

quote:
Originally posted by madhivanan

group by
CASE
WHEN PRJ.BDGT_CST_TOTAL<2000000 then '<2k'
WHEN PRJ.BDGT_CST_TOTAL>=2000000 AND PRJ.BDGT_CST_TOTAL<5000000 then '2k-5k'
WHEN PRJ.BDGT_CST_TOTAL>=2000000 then '>5k'
END

Madhi, thanks for this one. I never knew you could use CASE in a GROUP BY, and there were times I did nasty UNIONs to accomplish the same thing.

You are welcome Ken

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources