Get the SQL results pivoted

Question

I have this query and would like to pivot the results in such a way that I get the AirlineCode in y-axis and the Month in the x-axis for a certain year. The query is doing just the right thing for me now but it is kind of exhausting to copy everything to Excel and sort it by myself.

;WITH MonthsStartDates AS
(
    SELECT
        MonthStartDate = CONVERT(DATE, '2018-01-01')

    UNION ALL

    SELECT
        MonthStartDate = DATEADD(MONTH, 1, M.MonthStartDate)
    FROM
        MonthsStartDates AS M
    WHERE
        M.MonthStartDate <= '2020-01-01'
)
SELECT 
    AirlineCode,
    Year = DATEPART(YEAR, M.MonthStartDate),
    Month = DATEPART(MONTH, M.MonthStartDate),
    AppUser = COUNT(DISTINCT AppUser.Id)
FROM 
    [followme.aero.live].[dbo].[AppUser]
INNER JOIN 
    [followme.aero.live].[dbo].[UserInAppPurchase] ON AppUser.Id = UserInAppPurchase.UserId
INNER JOIN 
    MonthsStartDates AS M ON PurchaseDate < M.MonthStartDate 
                          AND ValidTo > M.MonthStartDate
GROUP BY 
    AirlineCode,
    DATEPART(YEAR, M.MonthStartDate),
    DATEPART(MONTH, M.MonthStartDate)

Sounds like you're asking for a dynamic Pivot? Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query), [SQL Query with Dynamic Columns Using Pivot](https://stackoverflow.com/questions/46307571/sql-query-with-dynamic-columns-using-pivot) and [T-SQL dynamic pivot](https://stackoverflow.com/questions/12210692/t-sql-dynamic-pivot)? — Thom A, Jan 13 '19 at 15:33

score 0 · Answer 1 · answered Jan 13 '19 at 15:48

Are you looking for something like this?

WITH MonthsStartDates AS
(
    SELECT
        MonthStartDate = CONVERT(DATE, '2018-01-01')

    UNION ALL

    SELECT
        MonthStartDate = DATEADD(MONTH, 1, M.MonthStartDate)
    FROM
        MonthsStartDates AS M
    WHERE
        M.MonthStartDate <= '2020-01-01'
), Wth1 as 
(

    SELECT 
        AirlineCode,
        Year = DATEPART(YEAR, M.MonthStartDate),
        Month = DATEPART(MONTH, M.MonthStartDate),
        AppUser = COUNT(DISTINCT AppUser.Id)
    FROM 
        [followme.aero.live].[dbo].[AppUser]
        INNER JOIN [followme.aero.live].[dbo].[UserInAppPurchase] ON AppUser.Id = UserInAppPurchase.UserId
        INNER JOIN MonthsStartDates AS M ON
            PurchaseDate < M.MonthStartDate AND
            ValidTo > M.MonthStartDate
    GROUP BY 
        AirlineCode,
        DATEPART(YEAR, M.MonthStartDate),
        DATEPART(MONTH, M.MonthStartDate)
)
SELECT 
   AirlineCode,
   Year, 
   AppUser
   [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM (
  SELECT * FROM WTh1
  PIVOT 
  ( 
    SUM(AppUser)
    For Month In ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
  ) as PivotTable;

thanks a lot. sadly I get a syntax error for the second last select statement in line 34-37, for all lines I get an invalid column name. Any idea why? — Andreas, Jan 15 '19 at 08:51

Get the SQL results pivoted

1 Answers1