sql-server - SQL Pivot on multiple columns

Question

I have a query where I am pulling odds and football results:

SELECT
    Div, BookieResult, COUNT(BookieResult) AS WinLossCount, OddsCategory
FROM 
    [dbo].[MatchOutcomes]
GROUP BY
    div, OddsCategory, BookieResult

This returns a result like this:

I'm looking to get results where the data is more readable and each like is on a single line like:

I've been trying to pivot the data in many different ways but cant seem to get it in SQL. does anyone know if the layout above can be accomplished?

Thanks.

标签： sql-servertsqlpivot

Answer 1

;WITH Table AS
(
  Select Div, BookieResult, Count(BookieResult) as WinLossCount, 
  OddsCategory
  FROM [dbo].[MatchOutcomes]
  group by div, OddsCategory, BookieResult
)
select Res1.Div
           ,Res1.[Loss]
           ,Res1.[Win]
           ,Res1.[oddscategory]
           ,Res2.[Loss]
           ,Res2.[Win]
           ,Res2.[oddscategory]
           ,Res3.[Loss]
           ,Res3.[Win]
           ,Res3.[oddscategory] 
           ,Res4.[Loss]
           ,Res4.[Win]
           ,Res4.[oddscategory]
    from
    (
    select Div
               ,[Loss]
               ,[Win]
               ,[oddscategory]
        from Table
        pivot
        (
             max(winlosscount)
             for BookResult IN ([Loose],[Win])
        )Rs1
        where oddscategory = 0 
     )Res1
    LEFT JOIN
    (
    select Div
               ,[Loss]
               ,[Win]
               ,[oddscategory]
        from Table
        pivot
        (
             max(winlosscount)
             for BookResult IN ([Loose],[Win])
        )Rs2
        where oddscategory = 1 
     )Res2 ON Res2.Div = Res1.Div
    LEFT JOIN
    (
    select Div
               ,[Loss]
               ,[Win]
               ,[oddscategory]
        from Table
        pivot
        (
             max(winlosscount)
             for BookResult IN ([Loose],[Win])
        )Rs3
        where oddscategory = 2 
     )Res3 Res3.Div = Res1.Div
    LEFT JOIN
    (
    select Div
               ,[Loss]
               ,[Win]
               ,[oddscategory]
        from Table
        pivot
        (
             max(winlosscount)
             for BookResult IN ([Loose],[Win])
        )Rs4
        where oddscategory = 3 
     )Res4 Res4.Div = Res1.Div