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!
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
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)
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.
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.
My output into the detailsview would be"
No income
0-200
201-500
501-1000
1001-1500
1501-2000
Greater than 2000
Thanks
No income
0-200
201-500
501-1000
1001-1500
1501-2000
Greater than 2000
Thanks
Is this right for you?
select case when income = 0 then 'No Income'
when income > 0 and income <= 200 then '0 to 200'
when income > 200 and income <= 500 then '201 to 500'
when income > 500 and income <= 1000 then '501 to 1000'
when income > 1000 and income <= 1500 then '1001 to 1500'
when income > 1500 and income <= 2000 then '1501 to 2000'
else 'Greater than 2000' end as category,
count(*)
from (
SELECT CAST(income as numeric) as income
FROM client
WHERE ISNUMERIC(income) = 1
) a
group by
case when income = 0 then 'No Income'
when income > 0 and income <= 200 then '0 to 200'
when income > 200 and income <= 500 then '201 to 500'
when income > 500 and income <= 1000 then '501 to 1000'
when income > 1000 and income <= 1500 then '1001 to 1500'
when income > 1500 and income <= 2000 then '1501 to 2000'
else 'Greater than 2000' end