使用SQL枢轴到行的列.Server 2008 R2

Question

我必须将表中的所有行转换为Server 2008 R2中的列.我使用数据透视将一列的多行转换为一行多列。我不知道如何在这种情况下使用枢轴.

我想以“费用”一栏为基础

SQL Fiddle

所需的输出是

​

​

同时，我会尝试探讨有关的职位建议.

谢谢你的建议..。

Answer 1

根据您想要的结果，看起来您需要先进行不枢轴变换，然后再进行枢轴变换，如下所示：

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

示例SQL Fiddle

请注意，这在任何方面都不是动态的，而是在年份和费用中使用硬编码的列值。可以以动态的方式生成代码--如果您想知道如何使用SQL Server实现动态支点，那么有很多好的答案。

编辑:动态版本是为了好玩，它可能不是完美的，但它应该能工作：

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