Sql Pivot table with two cross tab and multiple columns

Question

I am trying to build a table with two cross tabs and multiple columns. I have this so far, and when I run it

    create table test2(city nvarchar(10), race nvarchar(30), sex nvarchar(10), age int)
    insert into test2 values ('Austin',  'African-American', 'male', 21)
    insert into test2 values ('Austin',  'Asian', 'female', 22)
    insert into test2 values ('Austin',  'Caucasian', 'male', 23)
    insert into test2 values ('Austin',  'Hispanic', 'female', 24)
    insert into test2 values ('Austin',  'African-American', 'Unknown', 25)
    insert into test2 values ('Austin',  'Asian', 'male', 26)
    insert into test2 values ('Austin',  'Caucasian', 'female', 27)
    insert into test2 values ('Austin',  'Hispanic', 'Unknown', 28)
    insert into test2 values ('Austin',  'Asian', 'male', 29)
    insert into test2 values ('Austin',  'Caucasian', 'female', 31)
    insert into test2 values ('Dallas',  'Hispanic', 'Unknown', 32)
    insert into test2 values ('Dallas',  'African-American', 'male', 33)
    insert into test2 values ('Dallas',  'Asian', 'female', 34)
    insert into test2 values ('Dallas',  'Caucasian', 'Unknown', 35)
    insert into test2 values ('Dallas',  'Hispanic', 'male', 500)
    insert into test2 values ('Dallas',  'African-American', 'female', 36)
    insert into test2 values ('Dallas',  'Asian', 'Unknown', 37)
    insert into test2 values ('Dallas',  'Caucasian', 'male', 38)
    insert into test2 values ('Dallas',  'Hispanic', 'female', 39)
    insert into test2 values ('Dallas',  'African-American', 'Unknown', 41)
    insert into test2 values ('Houston',  'Asian', 'male', 42)
    insert into test2 values ('Houston',  'Caucasian', 'female', 43)
    insert into test2 values ('Houston',  'Hispanic', 'Unknown', 44)
    insert into test2 values ('Houston',  'African-American', 'male', 45)
    insert into test2 values ('Houston',  'Asian', 'female', 46)
    insert into test2 values ('Houston',  'Caucasian', 'Unknown', 47)
    insert into test2 values ('Houston',  'Hispanic', 'male', 48)
    insert into test2 values ('Houston',  'African-American', 'female', 49)
    insert into test2 values ('Houston',  'Asian', 'Unknown', 51)
    insert into test2 values ('Houston',  'Caucasian', 'male', 52)

    SELECT  *
    FROM
    (
    SELECT  a.city, a.sex
    FROM [AdventureWorksDW].[dbo].[test2] a

    ) p
    PIVOT
    (
    COUNT (sex)
    FOR sex
    IN ([male], [female], [Unknown])
    ) AS pvt

I get this

_{(source: realestateagenthealthinsurance.com)}

But what I need is this, with two cross tabs on top and a option for multiple columns.

_{(source: realestateagenthealthinsurance.com)}

Is this possible with a pivot or any other solution? Thanks in advance

Do you really want an overlap of ages on the boundaries? or the groups 20 to 29, 30 to 39, and 40 to 49? — tawman, Feb 19 '12 at 05:08
@user973671 with those age 30 and 40 in two groups? Your headings overlap boundaries. Age 20 to 29 is a group of ten :) — tawman, Feb 19 '12 at 05:13

tawman · Answer 1 · 2012-02-19T05:27:09.187

You can run a separate PIVOT query for each of the 3 age groups, and then outer join the results to a query of the city and sex present:

WITH DataKeys AS (
    select distinct city, sex
    from test2),
Data20to29 AS (
    SELECT *
    FROM(SELECT * FROM test2 WHERE age between 20 and 29
    ) AS Age20to29
    PIVOT (COUNT(age)
           FOR race IN ([African-American], [Asian], [Caucasian], [Hispanic])
    ) as Data20to29),
Data30to39 AS (
    SELECT *
    FROM(SELECT * FROM test2 WHERE age between 30 and 39
    ) AS Age30to39
    PIVOT (COUNT(age)
           FOR race IN ([African-American], [Asian], [Caucasian], [Hispanic])
    ) as Data30to39),
Data40to49 AS (
    SELECT *
    FROM(SELECT * FROM test2 WHERE age between 40 and 49
    ) AS Age40to49
    PIVOT (COUNT(age)
           FOR race IN ([African-American], [Asian], [Caucasian], [Hispanic])
    ) as Data40to49)
SELECT SELECT k.city, k.sex,
   d20.[African-American], d20.Asian, d20.Caucasian, d20.Hispanic,
   d30.[African-American], d30.Asian, d30.Caucasian, d30.Hispanic,
   d40.[African-American], d40.Asian, d40.Caucasian, d40.Hispanic
FROM DataKeys k LEFT JOIN Data20to29 d20 ON d20.city = k.city AND d20.sex = k.sex
LEFT JOIN Data30to39 d30 ON d30.city = k.city AND d30.sex = k.sex
LEFT JOIN Data40to49 d40 ON d40.city = k.city AND d40.sex = k.sex
ORDER BY k.city, k.sex

score 2 · Accepted Answer · answered Feb 19 '12 at 05:22

2

WITH T AS (
    SELECT A.city,
           A.sex, 
           CASE
                WHEN A.age BETWEEN 20 AND 30 THEN '20-30_' + race
                WHEN A.age BETWEEN 31 AND 40 THEN '31-40_' + race
                WHEN A.age BETWEEN 41 AND 50 THEN '41-50_' + race
           END AS age_range_race
    FROM @test2 AS A
)
SELECT  *
FROM T
PIVOT( COUNT(age_range_race) FOR age_range_race
       IN(
          [20-30_African-American], 
          [20-30_Asian], 
          [20-30_Caucasian], 
          [20-30_Hispanic],
          [31-40_African-American], 
          [31-40_Asian], 
          [31-40_Caucasian], 
          [31-40_Hispanic],
          [41-50_African-American], 
          [41-50_Asian], 
          [41-50_Caucasian], 
          [41-50_Hispanic]
          )
) AS P
ORDER BY city, sex

answered Feb 19 '12 at 05:22

J Cooper

4,828
3
36
39

1

I like this one much better than mine +1 .. replace @test2 w/ test2 to get it to run against the sample – tawman Feb 19 '12 at 05:25
This is good but is it possible to actually create this exact result, with age on the first row and race on the second? [link](http://www.realestateagenthealthinsurance.com/images/333.jpg), Thanks – user973671 Feb 19 '12 at 05:38
@user973671 - to answer your question, this is a presentational concern that should be handled by an application or reporting framework, not with SQL. SQL is for retrieving data, not layout and report formatting :) – J Cooper Feb 19 '12 at 05:43
this reminds me of a question i responded to the other day regarding 'multi-level' column headers: http://stackoverflow.com/questions/9280724/sql-multi-layer-headers/9280813#9280813 – J Cooper Feb 19 '12 at 05:46
That's what I was thinking, just wondering if it was possible. But if someone knows how to make it format like this with SQL, please post. J Cooper and tawman you all have more than answered my question. Thanks – user973671 Feb 19 '12 at 05:52

Sql Pivot table with two cross tab and multiple columns

2 Answers2