How do you transpose rows into columns in a SQL query

Flemming Hald Published at Dev

Flemming Hald

I have this output:

Contact_Type    Category_Type        Category_Count
---------------------------------------------------
Window          Admissions              1775
Window          Financial Aid          17377
Window          Miscellaneous           2720
Window          Student Financials     14039
Phone           Admissions              5758
Phone           Financial Aid          10048
Phone           Miscellaneous           4497
Phone           Registration              11
Phone           Student Financials      4857

and this is my query:

SELECT 
    Contact_Type, Category_Type1, Category_Type2, Category_Type3,
    Category_Type4, Category_Type5 
FROM
    (SELECT 
         CASE
            WHEN event.contact_type = 0 THEN 'Window' 
            WHEN event.contact_type = 1 THEN 'Phone' 
         END AS Contact_Type,
         cat.category_type AS Category_Type,
         COUNT(ec.category_id) AS Category_Count,
         'Category_Type' + CAST(ROW_NUMBER() OVER (PARTITION BY Contact_Type
ORDER BY Contact_Type) AS varchar(20)) AS ColumnSequence
     FROM 
         yLines.ylines_event AS Event
     JOIN 
         ylines.ylines_event_category AS ec ON ec.event_id = event.event_id
     JOIN 
         ylines.ylines_category AS cat ON ec.category_id = cat.category_id
     WHERE /*event.contact_type = '0' AND*/    
         CAST(FORMAT(event.event_date_time, 'yyyy') AS int) BETWEEN 2014 AND dateadd(year, 1, event.event_date_time)
     GROUP BY 
         Category_Type, Contact_Type) a 
PIVOT
    (MAX(Contact_Type)
     FOR ColumnSequence IN (Category_Type1, Category_Type2, Category_Type3,
Category_Type4, Category_Type5)) as piv;

If I run this it gives me an error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'Contact_Type'

and I can't seem to fix this. I am trying to transpose it so I see two rows only with 'Windows' and 'Phone' and the five Category Types transposed as five columns with the count in each. I am writing T-SQL statements. Please help!

n00bs

I would try do it in dynamic

       ; WITH [CONTACT]
     AS (
         SELECT *
           FROM (
                 VALUES 
                        ('Window', 'Admissions        ', ' 1775')
                      , ('Window', 'Financial Aid     ', '17377')
                      , ('Window', 'Miscellaneous     ', ' 2720')
                      , ('Window', 'Student Financials', '14039')
                      , ('Phone ', 'Admissions        ', ' 5758')
                      , ('Phone ', 'Financial Aid     ', '10048')
                      , ('Phone ', 'Miscellaneous     ', ' 4497')
                      , ('Phone ', 'Registration      ', '   11')
                      , ('Phone ', 'Student Financials', ' 4857')
                ) X ([Contact_Type], [Category_Type], [Category_Count])
     )   


     SELECT * 
       INTO #TEMP_PIVOT
       FROM [CONTACT]

    DECLARE @TYPE    VARCHAR(MAX)   
        SET @TYPE = STUFF( 
                          (SELECT DISTINCT ', ' + QUOTENAME(RTRIM(LTRIM([CATEGORY_TYPE])))
                                    FROM #TEMP_PIVOT
                                     FOR XML PATH('')
                          )
                          , 1, 1, '')

   DECLARE @SQL    VARCHAR(MAX)
       SET @SQL = '  SELECT [CONTACT_TYPE] '
                + '       , ' + @TYPE
                + '    FROM #TEMP_PIVOT '
                + '   PIVOT ( '
                + '           MAX([CATEGORY_COUNT]) '
                + '           FOR [CATEGORY_TYPE] IN (' + @TYPE + ')'
                + '         ) P '

     EXECUTE (@SQL)

Collected from the Internet

Please contact [email protected] to delete if infringement.