We help IT Professionals succeed at work.

SQL 2008 Columns to Rows

pposton
pposton asked
on
Medium Priority
279 Views
Last Modified: 2012-08-14
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

Comment
Watch Question

Lee WadwellProject Architect
CERTIFIED EXPERT

Commented:
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

ppostonPresident/Owner

Author

Commented:
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 WadwellProject Architect
CERTIFIED EXPERT

Commented:
What are full set of ranges you want?
ppostonPresident/Owner

Author

Commented:
My output into the detailsview would be"

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

Thanks
Project Architect
CERTIFIED EXPERT
Commented:
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

Open in new window

ppostonPresident/Owner

Author

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

Thanks for all the help!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.