Sql Pivot-Tabelle mit zwei Kreuztabelle und mehr Spalten

stimmen
3

Ich versuche, einen Tisch mit zwei Kreuztabellen und mehr Spalten zu bauen. Ich habe dies so weit, und wenn ich es laufen

    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

Ich erhalte diese

s http://realestateagenthealthinsurance.com/images/111.jpg

Aber was ich brauche, ist dies mit zwei Querlaschen oben und eine Option für mehrere Spalten.

s http://realestateagenthealthinsurance.com/images/222.jpg

Ist dies möglich, mit einem Dreh oder jeder anderen Lösung? Danke im Voraus

Veröffentlicht am 19/02/2012 um 05:31
quelle vom benutzer
In anderen Sprachen...                            


2 antworten

stimmen
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
Beantwortet am 19/02/2012 um 06:22
quelle vom benutzer

stimmen
2

Sie können eine separate PIVOT Abfrage für jede der drei Altersgruppen laufen, und dann äußere die Ergebnisse einer Abfrage der Stadt und Geschlecht vorhanden beitreten:

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
Beantwortet am 19/02/2012 um 06:15
quelle vom benutzer

Cookies help us deliver our services. By using our services, you agree to our use of cookies. Learn more