[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 272
  • Last Modified:

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

0
pposton
Asked:
pposton
  • 3
  • 3
1 Solution
 
lwadwellCommented:
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

0
 
ppostonAuthor 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.
0
 
lwadwellCommented:
What are full set of ranges you want?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
ppostonAuthor Commented:
My output into the detailsview would be"

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

Thanks
0
 
lwadwellCommented:
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

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

Thanks for all the help!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now