asked on

SQL 2008 Columns to Rows

I have some code that I recieved help on from EE but I have one more questions concerning this code. How would I convert the columns to rows. This seems to be an eaiser format when using with DetailsView or in Pie Charts. I am thinking that it probably involves a Pivot or Unpivot but I'm not sure exactly how to go about attaing the desired results. I've attached the query below.

Thanks!

SELECT income
FROM client
WHERE ISNUMERIC(income) = 1

-- This returns counts for multiple ranges
SELECT
      COUNT(CASE WHEN a.income BETWEEN 0 AND 150 THEN a.income END) as count_0_150,
      COUNT(CASE WHEN a.income BETWEEN 151 AND 300 THEN a.income END) as count_151_300,
      COUNT(CASE WHEN a.income BETWEEN 301 AND 450 THEN a.income END) as count_301_450,
      COUNT(CASE WHEN a.income BETWEEN 451 AND 600 THEN a.income END) as count_451_600
FROM (
      SELECT CAST(income as numeric) as income
      FROM client
      WHERE ISNUMERIC(income) = 1
	  ) a

Open in new window

Lee Wadwell

One approach would be

select case floor(income/150) when 0 then '0 to 150'
                              when 1 then '151 to 300'
                              when 2 then '301 to 450'
                              when 3 then '451 to 600'
                              else '>600' end as category, 
       count(*)
from (
      SELECT CAST(income as numeric) as income
      FROM client
      WHERE ISNUMERIC(income) = 1
	  ) a
group by floor(income/150)

Open in new window

pposton

ASKER

This is close to working but it leaves me with around 100 rows of ">2000" where each row is totaling numbers such as 2100.00 (count of 2), 3560 (count of 5), etc. Is there a way to group the "remaining numbers"?

Also, I'm not sure why , but if I set the upper limit to say "1000000" to capture what should be the remaining numbers it is including numbers such as "1300, 660, 606.60" etc. I'm not sure I need to format the data differently or if the code needs refining.

Thanks for the origional response, I hope you or someone can give some need direction.

Lee Wadwell

What are full set of ranges you want?

pposton

ASKER

My output into the detailsview would be"

No income
0-200
201-500
501-1000
1001-1500
1501-2000
Greater than 2000

Thanks

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

pposton

ASKER

GREAT...that's exactly what I was needing!!

Thanks for all the help!