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 IN

Solution 1:

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