代码之家 › 专栏 › 技术社区 › Seth Spearman

如何在不聚合列的情况下透视sql数据

pivot-without-aggregate pivot tsql sql-server-2005 sql-server

3

Seth Spearman · 技术社区 · 14 年前

SKILL                                              LEVEL          SCORERANGE
-----------------------------------------------------------------------------
Stunts                                             LOW            0.0 - 4.0
Stunts                                             MED            3.0 - 7.0
Stunts                                             HI             6.0 - 10.0
Pyramids                                           LOW            0.0 - 4.0
Pyramids                                           MED            3.0 - 7.0
Pyramids                                           HI             6.0 - 10.0
Tosses                                             LOW            0.0 - 4.0
Tosses                                             MED            3.0 - 7.0
Tosses                                             HI             6.0 - 10.0
Standing Tumbling                                  LOW            0.0 - 4.0
Standing Tumbling                                  MED            3.0 - 7.0
Standing Tumbling                                  HI             6.0 - 10.0
Running Tumbling                                   LOW            0.0 - 4.0
Running Tumbling                                   MED            3.0 - 7.0
Running Tumbling                                   HI             6.0 - 10.0
Jumps                                              LOW            0.0 - 4.0
Jumps                                              MED            3.0 - 7.0

SKILL                                              LOWRANGE       MEDRANGE       HIRANGE 
Stunts                                             0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Pyramids                                           0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Tosses                                             0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Standing Tumbling                                  0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
Running Tumbling                                   0.0 - 4.0      3.0 - 7.0      6.0 - 10.0
...

我对基本的pivot语法有很好的理解,但是我正在努力解决这个问题,因为在结果集中没有什么可以聚合的。

给你点东西帮我修理。。。

SELECT SKILL, LOWRANGE, MEDRANGE, HIRANGE
FROM (SELECT SKILL, [LEVEL], SCORERANGE FROM ScoreRanges) ps
PIVOT 
    (
        MAX(SCORERANGE)  --THIS IS PROBABLY WRONG
        FOR SCORERANGE IN
        (
           --SOMETHING GOES HERE
        )
    ) as pvt

2 回复 | 直到 13 年前

1

4

Martin Smith 14 年前

如你所知,你将只有一行每 SKILL, LEVEL 您可以使用的组合 Max Min

SELECT SKILL, [LOW] LOWRANGE, [MED] MEDRANGE, [HI] HIRANGE
FROM (SELECT SKILL, [LEVEL], SCORERANGE FROM ScoreRanges) ps
PIVOT (
        MAX(SCORERANGE) 
        FOR LEVEL IN ([LOW], [MED], [HI])
    ) as pvt

2

1

SQLMenace 14 年前

SELECT SKILL, LOW as LOWRANGE, MED as MEDRANGE, HI as HIRANGE
FROM (SELECT SKILL, [LEVEL], SCORERANGE FROM ScoreRanges) ps
PIVOT 
    (
        MAX(SCORERANGE)  
        FOR [LEVEL] IN
        (
           [LO], [HIGH], [MED]
        )
    ) as pvt

下面是一个可以在Adventureworks数据库中运行的示例,因此您也可以使用它来了解 PIVOT 作品

SELECT City, F AS Females, M AS Males
 FROM(
SELECT Gender,City
FROM HumanResources.Employee AS e
    INNER join HumanResources.EmployeeAddress AS ea
    ON e.EmployeeID = ea.EmployeeID
    INNER join Person.Address AS p
    ON ea.AddressID = p.AddressID
    ) AS pivTemp
PIVOT
(   COUNT(Gender)
    FOR Gender IN (F,M)
) AS pivTable

Three different ways of populating variables with configuration values in SQL Server