使用pivot将多个值显示到sql server中的列中 [英] Displaying multiple values into columns in sql server using pivot

问题描述

declare @t table 
(
    driver varchar(20),
    roadband varchar(20),
    category varchar(20),
    points int

)   

INSERT INTO @T VALUES( 'Dan'    ,'20 Mph','CAT1',58)
INSERT INTO @T VALUES( 'Dan'    ,'20 Mph',  'CAT2', 0)
INSERT INTO @T VALUES( 'Dan'    ,'20 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'Dan'    ,'30 Mph',  'CAT1', 102)
INSERT INTO @T VALUES( 'Dan'    ,'30 Mph',  'CAT2', 30)
INSERT INTO @T VALUES( 'Dan'    ,'30 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'Dan'    ,'40 Mph',  'CAT1', 6)
INSERT INTO @T VALUES( 'Dan'    ,'40 Mph',  'CAT2', 3)
INSERT INTO @T VALUES( 'Dan'    ,'40 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'Dan'    ,'50 Mph',  'CAT1', 5)
INSERT INTO @T VALUES( 'Dan'    ,'50 Mph',  'CAT2', 0)
INSERT INTO @T VALUES( 'Dan'    ,'50 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'Dan'    ,'60 Mph',  'CAT1', 0)
INSERT INTO @T VALUES( 'Dan'    ,'60 Mph',  'CAT2', 0)
INSERT INTO @T VALUES( 'Dan'    ,'60 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'Dan'    ,'70 Mph',  'CAT1', 0)
INSERT INTO @T VALUES( 'Dan'    ,'70 Mph',  'CAT2', 0)
INSERT INTO @T VALUES( 'Dan'    ,'70 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'John'   ,'20 Mph',  'CAT1', 6)
INSERT INTO @T VALUES( 'John'   ,'20 Mph',  'CAT2', 0)
INSERT INTO @T VALUES( 'John'   ,'20 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'John'   ,'30 Mph',  'CAT1', 1228)
INSERT INTO @T VALUES( 'John'   ,'30 Mph',  'CAT2', 654)
INSERT INTO @T VALUES( 'John'   ,'30 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'John'   ,'40 Mph',  'CAT1', 196)
INSERT INTO @T VALUES( 'John'   ,'40 Mph',  'CAT2', 117)
INSERT INTO @T VALUES( 'John'   ,'40 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'John'   ,'50 Mph',  'CAT1', 6)
INSERT INTO @T VALUES( 'John'   ,'50 Mph',  'CAT2', 14)
INSERT INTO @T VALUES( 'John'   ,'50 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'John'   ,'60 Mph',  'CAT1', 0)
INSERT INTO @T VALUES( 'John'   ,'60 Mph',  'CAT2', 0)
INSERT INTO @T VALUES( 'John'   ,'60 Mph',  'CAT3', 0)
INSERT INTO @T VALUES( 'John'   ,'70 Mph',  'CAT1', 0)
INSERT INTO @T VALUES( 'John'   ,'70 Mph',  'CAT2', 0)
INSERT INTO @T VALUES( 'John'   ,'70 Mph',  'CAT3', 0)

我需要以 Roadband 和 Category 为中心..

I need to pivot on Roadband and Category..

我已经写了查询..需要一些帮助..

I have written the query .. need some help..

select * from 
(
   select 
     Driver,  ROADBAND,  category,SUM(points) as Points
   from @t
   where DRIVER is not null
   group by DRIVER ,ROADBAND,CATEGORY
) main
pivot
(
    sum(Points)
    for ROADBAND in ([20 Mph],[30 Mph],[40 Mph],[50 Mph],[60 Mph],[70 Mph])
) pq

现在如何通过路带为每个类别制作支点.

Now how can make the pivot for each category by roadband.

请帮忙.

推荐答案

根据您的问题，我不确定您想要列中的值以及行中的值，因此我编写了两个查询，希望其中一个可以回答您的问题问题:

From your question I wasn't sure what values you wanted in columns and what should be in rows so I wrote both queries, I hope one of them answers your question:

SELECT  *
FROM    (
            SELECT  category
                    , roadband
                    , SUM(points) points_per_category_roadband
            FROM    @t
            GROUP BY
                    category, roadband
) tbl
PIVOT
(
    SUM(points_per_category_roadband) FOR roadband IN ([20 Mph],[30 Mph],[40 Mph],[50 Mph],[60 Mph],[70 Mph])
) pvt


SELECT  *
FROM    (
            SELECT  roadband
                    , category
                    , SUM(points) points_per_category_roadband
            FROM    @t
            GROUP BY
                    roadband, category
) tbl
PIVOT
(
    SUM(points_per_category_roadband) FOR category IN ([CAT1],[CAT2],[CAT3])
) pvt

这篇关于使用pivot将多个值显示到sql server中的列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！