splitting row data based on condition
I am getting result set for the below query
SELECT CASE
WHEN S.TaxableAmt <= 2000 THEN '<=2000'
WHEN (S.TaxableAmt >= 2001 AND S.TaxableAmt <= 3000) THEN '2001-3000'
WHEN (S.TaxableAmt >= 3001 AND S.TaxableAmt <= 4000) THEN '3001-4000'
ELSE '>5000'
END as Bracket,
COUNT(DISTINCT(S.INVOICENO)) AS LESSTWO , YEAR(S.invoicedate) YER,
Month(S.invoicedate) MNTH
FROM SALESDATA S
where S.BILLINGTYPE='INVOICE'
AND (S.invoicedate >='20090401' and S.invoicedate <='20100430')
GROUP BY
CASE
WHEN S.TaxableAmt <= 2000 THEN '<=2000'
WHEN (S.TaxableAmt >= 2001 AND S.TaxableAmt <= 3000) THEN '2001-3000'
WHEN (S.TaxableAmt >= 3001 AND S.TaxableAmt <= 4000) THEN '3001-4000'
ELSE '>5000'
END,
YEAR(S.invoicedate) ,Month(S.invoicedate)
ORDER BY YEAR(S.invoicedate) ,Month(S.invoicedate)
as below
BRACKET LESSTWO YER MNTH
3001-4000 331 2009 4
>5000 562 2009 4
<=2000 21271 2009 4
2001-3000 1351 2009 4
<=2000 20008 2009 5
3001-4000 325 2009 5
2001-3000 1307 2009 5
>5000 470 2009 5
<=2000 24486 2009 6
3001-4000 381 2009 6
>5000 449 2009 6
2001-3000 1465 2009 6
<=2000 49624 2009 7
>5000 548 2009 7
2001-3000 1836 2009 7
3001-4000 478 2009 7
<=2000 54828 2009 8
2001-3000 2552 2009 8
>5000 1265 2009 8
3001-4000 888 2009 8
>5000 524 2009 9
2001-3000 1597 2009 9
<=2000 30684 2009 9
3001-4000 508 2009 9
>5000 731 2009 10
2001-3000 2376 2009 10
<=2000 31032 2009 10
3001-4000 691 2009 10
>5000 1693 2009 11
2001-3000 3532 2009 11
3001-4000 1227 2009 11
<=2000 29895 2009 11
>5000 1314 2009 12
3001-4000 996 2009 12
<=2000 30018 2009 12
2001-3000 3438 2009 12
>5000 2225 2010 1
2001-3000 3243 2010 1
<=2000 56189 2010 1
3001-4000 1428 2010 1
>5000 1590 2010 2
2001-3000 2541 2010 2
3001-4000 1017 2010 2
<=2000 48069 2010 2
>5000 584 2010 3
3001-4000 551 2010 3
<=2000 30094 2010 3
2001-3000 1577 2010 3
&l开发者_开发知识库t;=2000 27552 2010 4
3001-4000 459 2010 4
>5000 505 2010 4
2001-3000 1788 2010 4
But I need result set like below.
<2000 (2001-3000) (3001 - 4000) >5000 LESSTWO YER MNTH ====> as column headings.
How can I do this?
Regards,
N.SRIRAM
Do I get you correct qassuming you like to group and have each group sum as a column instead of a row?
n that case have a look on PIVOT
精彩评论