Columns to Rows using SQL PIVOT... SQL Server 2008 R2

Question

I have to transpose all the Rows from a table to columns in SQL Server 2008 R2... I have used Pivot to transpose multiple rows of one column into one row with multiple columns. I am not sure how I can use pivot in this scenario...

I would like to pivot the table based on the "EXPENSE" column

SQL Fiddle

The desired output is

enter image description here

Meanwhile I will try to explore the related posts suggested....

Thank you so much for the suggestions...

possible duplicate of [TSQL PIVOT MULTIPLE COLUMNS](http://stackoverflow.com/questions/19590799/tsql-pivot-multiple-columns) — Amit, Jul 09 '15 at 21:33
Once you get the Expenses as columns, what do you want to do with the years? (sum them?) — Chuck, Jul 09 '15 at 21:46
@chuck... I need the years as rows...year1, year2, year3, year4, tear5 — Jay, Jul 09 '15 at 21:54
If my suggestion with Excel doesn't fit your needs, you should have a look on the discussion @Amit linked above... Good explanation... — Shnugo, Jul 09 '15 at 22:13
@chuck... I need the years as rows...year1, year2, year3, year4, year5 – with Column header YEARS... — Jay, Jul 09 '15 at 22:22
Are you trying to UNPIVOT the values to multiple rows for each column. Can you show a sample of the desired output? — Joseph Gagliardo, Jul 09 '15 at 23:15
@Jay Does this question have anything to do with Excel at all? — jpw, Jul 09 '15 at 23:48
I'm inclined to roll back the last edit to the question as there seems to be nothing to support the Excel tag. — jpw, Jul 10 '15 at 00:07
@Shnugo.... It's so funnny that you give an answer and try to update the question to be relevant to you answer... This question is purely SQL related and, the original tags clearly stated so.... — Jay, Jul 10 '15 at 01:06
Hi @Jay, sorry for this but my answer was the accepted one for a while. Why did you accept it, if it doesn't fit at all? If you look below my answer I was almost ordered by Amit to edit your question. Never had done it by myself... Sorry for any inconvenience! — Shnugo, Jul 10 '15 at 01:16

jpw · Accepted Answer · 2015-07-10T01:48:39.733

Based on your desired results it looks like you need to do an unpivot transform followed by a pivot, like this:

select 
    YEAR, 
    [Bps on Assets],[Setup Fee],[Account Min],[BAA Fees],[RedTail Fees (CRM)],
    [RedTail Fees (Per User)],[External IT],[External IT Setup] 
from (
    select Expense, value, year 
    from SM_TechBundleExpnsRates
    unpivot (
       value FOR year IN ([Year1], [Year2], [Year3], [Year4], [Year5]) 
    ) up 
) a
pivot (
    sum(value) for expense in 
          (
           [Bps on Assets],[Setup Fee],[Account Min],
           [BAA Fees],[RedTail Fees (CRM)],
           [RedTail Fees (Per User)],[External IT],[External IT Setup]
          )
) p

Sample SQL Fiddle

Note that this isn't dynamic in any way, but rather uses hard coded column values for the years and expenses. It's possible to generate the code in a dynamic fashion - if you want to know how there are plenty of good answers showing how to do dynamic pivot with SQL Server.

Edit: did the dynamic version for fun, it might not be perfect but it should work:

DECLARE @sql AS NVARCHAR(MAX)
DECLARE @year_cols AS NVARCHAR(MAX)
DECLARE @expe_cols AS NVARCHAR(MAX)

SELECT @expe_cols= ISNULL(@expe_cols + ',','') + QUOTENAME(Expense)
FROM (SELECT DISTINCT Expense FROM SM_TechBundleExpnsRates) AS Expenses

SELECT @year_cols= ISNULL(@year_cols + ',','') + QUOTENAME(year) 
FROM (
    SELECT c.name AS year 
    FROM sys.tables t JOIN sys.columns c ON t.object_id = c.object_id 
    WHERE t.name = 'SM_TechBundleExpnsRates' AND c.name LIKE '%Year%'
) AS Years


SET @sql = N'
SELECT 
    Year, ' + @expe_cols + '    
FROM (
    SELECT Expense, Value, Year 
    FROM SM_TechBundleExpnsRates
    UNPIVOT ( Value FOR Year IN (' + @year_cols + ') ) AS up
) a PIVOT ( SUM(Value) FOR Expense IN (' + @expe_cols + ') ) p'

EXEC sp_executesql @sql

@jpw...Thanks... This works for now... How would you do the same with dynamic column names??? Pls answer if you could.... — Jay, Jul 10 '15 at 01:31
@Jay I've updated my answer to include a dynamic version - it can probably be done smarter, but it's late... ;) — jpw, Jul 10 '15 at 01:49

Columns to Rows using SQL PIVOT... SQL Server 2008 R2

1 Answers1