SQL Server - how to create a pivot from a select query

[Radha Shyam]

Does anybody knows a simple way to create a pivot of this result:

[0-1]  [1-2]  [2-3]  [3-4]
12      45      29    5

This result is created with query:

 SELECT [0-1]   = SUM(CASE WHEN AGE >= 0 AND AGE <= 1 THEN 1 ELSE 0 END),
       [1-2]  = SUM(CASE WHEN AGE > 1 AND AGE <= 2 THEN 1 ELSE 0 END),
       [2-3] = SUM(CASE WHEN AGE > 2 AND AGE <= 3 THEN 1 ELSE 0 END),
[3-4] = SUM(CASE WHEN AGE > 3 AND AGE <= 4 THEN 1 ELSE 0 END)
    FROM dbo.Persons

I want to show it like columns should become rows i.e it should be:

[0-1]    12
[1-2]    45
[2-3]    29

Please suggest a simple and easy to follow solution.

[tofos]

Hi,

try this:

SELECT
    CASE WHEN AGE >= 0 AND AGE <= 1 THEN '[1-2]'
            WHEN AGE > 2 AND AGE <= 3 THEN '[2-3]'
            WHEN AGE > 3 AND AGE <= 4 THEN '[3-4]'
            ELSE '[>4]' END Range
    ,count(CASE WHEN AGE >= 0 AND AGE <= 1 THEN '[1-2]'
            WHEN AGE > 2 AND AGE <= 3 THEN '[2-3]'
            WHEN AGE > 3 AND AGE <= 4 THEN '[3-4]'
            ELSE '[>4]' END) Qty
FROM dbo.Persons
GROUP BY
    CASE WHEN AGE >= 0 AND AGE <= 1 THEN '[1-2]'
            WHEN AGE > 2 AND AGE <= 3 THEN '[2-3]'
            WHEN AGE > 3 AND AGE <= 4 THEN '[3-4]'
            ELSE '[>4]' END

[Rabbit]

Please use code tags when posting code or formatted data.

By the way, there's no need for the case within the count, you can just use count(*).

[Radha Shyam]

Thank you tofos for your reply!
This shows the count as expected but the only thing is the sort order. The result is:
Range
[1-2]
[3-4]
[2-3]
[0-1]
[>4]

Can you suggest how can i show them as sorted ie. range [0-1] should come first followed by [1-2], [2-3] and so on.

Can i add one more column as sort order and set order by on that.

Please advise.

Thanks

[tofos]

Rabbit, thanks for advice.
Radha, how about this:

SELECT
    CASE WHEN AGE >= 0 AND AGE <= 1 THEN '[0-1]'
            WHEN AGE > 1 AND AGE <= 2 THEN '[1-2]'
            WHEN AGE > 2 AND AGE <= 3 THEN '[2-3]'
            WHEN AGE > 3 AND AGE <= 4 THEN '[3-4]'
            ELSE '[4...]' END Range
    ,count(*) Qty
FROM dbo.Persons
GROUP BY
    CASE WHEN AGE >= 0 AND AGE <= 1 THEN '[0-1]'
            WHEN AGE > 1 AND AGE <= 2 THEN '[1-2]'
            WHEN AGE > 2 AND AGE <= 3 THEN '[2-3]'
            WHEN AGE > 3 AND AGE <= 4 THEN '[3-4]'
            ELSE '[4...]' END
ORDER BY
    CASE WHEN AGE >= 0 AND AGE <= 1 THEN '[0-1]'
            WHEN AGE > 1 AND AGE <= 2 THEN '[1-2]'
            WHEN AGE > 2 AND AGE <= 3 THEN '[2-3]'
            WHEN AGE > 3 AND AGE <= 4 THEN '[3-4]'
            ELSE '[4...]' END