February 17, 2005 at 4:24 pm
Is it possible to return the columns as rows instead? I have the query below;
SELECT COUNT(CASE WHEN DurationofClaim = 0 THEN 1 END) AS 'LessThanOneMonth',
COUNT(CASE WHEN DurationofClaim BETWEEN 1 AND 2 THEN 1 END) AS '1-2Months',
COUNT(CASE WHEN DurationofClaim BETWEEN 2 AND 3 THEN 1 END) AS '2-3Months' FROM #AllData
This returns;
LessThanOneMonth 1-2Months 2-3Months
270 1063 791
I wish to retrun it as
LessThanOneMonth 270
1-2Months 1063
2-3Months 791
I know I can insert each record into a temp table again to perform the COUNT but is there a simpler way?
February 17, 2005 at 4:31 pm
Columns to Rows is usually accomplished with a UNION:
SELECT 'LessThanOneMonth' As Category, COUNT(*) As CountValue
FROM #AllData
WHERE DurationofClaim = 0
UNION
SELECT '1-2Months', COUNT(*)
FROM #AllData
WHERE DurationofClaim = BETWEEN 1 AND 2
UNION
SELECT '2-3Months', COUNT(*)
FROM #AllData
WHERE DurationofClaim = BETWEEN 2 AND 3
February 17, 2005 at 5:14 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply