SQL Case Statement As Columns

Question

I have the following Case statement which I would like to display as columns values in the output so the result set is formatted like this:

Resultset Header

SELECT 
    l.Title as List
,   CASE 
        WHEN _Custom57 between 13 and 17 THEN '13-17' 
        WHEN _Custom57 between 18 and 24 THEN '18-24' 
        WHEN _Custom57 between 25 and 34 THEN '25-34' 
        WHEN _Custom57 between 35 and 44 THEN '35-44' 
        WHEN _Custom57 between 45 and 54 THEN '45-54' 
        WHEN _Custom57 >= 55 THEN '55-64' 
        WHEN _Custom57 >= 65 THEN '65+' 
    END as Age
,   COUNT(r.ID) as RecipCount
FROM mytable (NOLOCK)```

Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Heinzi, Aug 20 '20 at 20:38

SteveC · Answer 1 · 2020-08-20T21:28:57.607

Conditional aggregation

select 
    l.Title as List,
    sum(iif(_Custom57 <= 17, 1, 0)) '17 and under',
    sum(iif(_Custom57 between 18 and 24 , 1, 0)) '18-24',
    sum(iif(_Custom57 between 25 and 34 , 1, 0)) '25-34',
    sum(iif(_Custom57 between 35 and 44 , 1, 0)) '35-44',
    sum(iif(_Custom57 between 45 and 54 , 1, 0)) '45-54',
    sum(iif(_Custom57 between 55 and 64 , 1, 0)) '55-64',
    sum(iif(_Custom57 > 64, 1, 0)) '65+',
    count(r.ID) as RecipCount
FROM mytable l
group by l.Title;

score 0 · Accepted Answer · answered Aug 20 '20 at 21:20

you can build your query using pivot.. sample below-

SELECT * from
(SELECT 
    l.Title as List
,   CASE 
        WHEN _Custom57 between 13 and 17 THEN '17 and under' 
        WHEN _Custom57 between 18 and 24 THEN '18-24' 
        WHEN _Custom57 between 25 and 34 THEN '25-34' 
        WHEN _Custom57 between 35 and 44 THEN '35-44' 
        WHEN _Custom57 between 45 and 54 THEN '45-54' 
        WHEN _Custom57 >= 55 THEN '55-64' 
        WHEN _Custom57 >= 65 THEN '65+' 
    END as Age
,   COUNT(r.ID) as RecipCount
FROM mytable) t
PIVOT (
sum(RecipCount) for age in ([17 and under],[18-24],[25-34],[35-44],[45-54])
) as PVT

SQL Case Statement As Columns

2 Answers2