具有多个列和动态列名的Unpivot表

I am trying to unpivot a table with multiple rows and columns. Each row needs to be extratced to 2 rows with specific columns and the column names need to be renamed and a new column needs to added based on the columns selected!

我正在尝试对具有多个行和列的表进行反透视。需要将每一行压缩为2行，其中包含特定的列，需要重新命名列名，并且需要根据所选列添加新的列!

I am including before and after sample data and a script to setup the data.

我包括之前和之后的样本数据和一个脚本设置数据。

 CREATE TABLE #tmpProducts (
        ProductId INT,
        ProductName nVARCHAR(100),
        B2B_GrossRevenue DECIMAL(10,2),
        B2B_DirectCost DECIMAL(10,2),
        B2B_NetRevenue DECIMAL(10,2),
        B2C_GrossRevenue DECIMAL(10,2),
        B2C_DirectCost DECIMAL(10,2),
        B2C_NetRevenue DECIMAL(10,2)
    )

    INSERT INTO #tmpProducts SELECT 1, 'Product1',1545.00,406.25,1138.75,195.00,35.10,159.90
    INSERT INTO #tmpProducts SELECT 2, 'Product2',902.00,189.00,713.00,3280.00,590.40,2689.60
    INSERT INTO #tmpProducts SELECT 3, 'Product3',15665.00,3988.39,11676.61,6247.00,1124.46,5122.54
    INSERT INTO #tmpProducts SELECT 4, 'Product4',736.00,196.16,539.84,2395.00,431.10,1963.90

    SELECT * FROM #tmpProducts
    DROP TABLE #tmpProducts

    CREATE TABLE #tmpProducts2 (
        ProductId INT,
        ProductName nVARCHAR(100),
        [Type] nVARCHAR(3),
        GrossRevenue DECIMAL(10,2),
        DirectCost DECIMAL(10,2),
        NetRevenue DECIMAL(10,2)
    )

    INSERT INTO #tmpProducts2 SELECT 1, 'Product1','B2B',1545.00,406.25,1138.75
    INSERT INTO #tmpProducts2 SELECT 1, 'Product1','B2C',195.00,35.10,159.90
    INSERT INTO #tmpProducts2 SELECT 2, 'Product2','B2B',902.00,189.00,713.00
    INSERT INTO #tmpProducts2 SELECT 2, 'Product2','B2C',3280.00,590.40,2689.60
    INSERT INTO #tmpProducts2 SELECT 3, 'Product3','B2B',15665.00,3988.39,11676.61
    INSERT INTO #tmpProducts2 SELECT 3, 'Product3','B2C',6247.00,1124.46,5122.54
    INSERT INTO #tmpProducts2 SELECT 4, 'Product4','B2B',736.00,196.16,539.84
    INSERT INTO #tmpProducts2 SELECT 4, 'Product4','B2C',2395.00,431.10,1963.90

    SELECT * FROM #tmpProducts2
    DROP TABLE #tmpProducts2

I have attempted this but i can't get past the second column and im not sure how at add a new column with specific text, (probably dynamic sql but trying to avoid this if possible)

我已经尝试过了，但是我无法通过第二列，我不知道如何添加一个具有特定文本的新列(可能是动态sql，但如果可能的话尽量避免)

Here is the start of my attempt, any help would be much appreciated.

这是我的尝试的开始，任何帮助都是值得感激的。

SELECT  ProductId, ProductName,GrossRevenue
        FROM    (
                    SELECT ProductId, ProductName, B2B_GrossRevenue,B2C_GrossRevenue FROM #tmpProducts
                ) as t
            UNPIVOT ( GrossRevenue for test IN (B2B_GrossRevenue,B2C_GrossRevenue)) AS unpvt

3 个解决方案

#1

You just keep unpivoting

你只是保持透视

    SELECT  ProductId,ProductName, Substring(col1,1,3) as type,  GrossRevenue, DirectCost, NetRevenue
    FROM    ( 
        SELECT * FROM #tmpProducts 
    ) as t 
    UNPIVOT ( GrossRevenue for col1 IN (B2B_GrossRevenue,B2C_GrossRevenue)) AS unpvt 
    unpivot ( DirectCost for col2 in (B2b_DirectCost, B2c_DirectCost)) up2
    unpivot ( NetRevenue for col3 in (B2b_NetRevenue, B2c_NetRevenue)) up3
where SUBSTRING(col1,1,3)=SUBSTRING(col2,1,3)
and  SUBSTRING(col1,1,3)=SUBSTRING(col3,1,3)

and join on the col columns to filter out mismatches

并在列上联接以过滤不匹配项

#2

Here is a way to do this using Dynamic SQL. This will allow you to get all of the columns upon execution. Then you will not have to alter the query if you get any data changes. This does both an UNPIVOT and PIVOT:

这里有一种使用动态SQL的方法。这将允许您在执行时获得所有列。然后，如果得到任何数据更改，则不必修改查询。这既做了非支点，也做了支点:

DECLARE @colsUnPivot AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @colsUnPivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('tmpProducts') and
               C.name like 'B2%'
         for xml path('')), 1, 1, '')

SET @colsPivot = stuff((select DISTINCT ','+quotename(right((C.name), len(C.name)-4))
         from sys.columns as C
         where C.object_id = object_id('tmpProducts') and
               C.name like 'B2%'
         for xml path('')), 1, 1, '')


set @query 
  = ' SELECT ProductId, ProductName, [type], ' + @colsPivot +'
      FROM
      (
        SELECT ProductId, ProductName, substring(field, 1, 3) [type]
                ,  value, right((field), len(field)-4) as col
        from tmpProducts
        unpivot 
        (
           value
           for field in (' + @colsUnPivot + ')
        ) unpvt 
      ) x
      PIVOT
      (
        sum(value)
        FOR col IN (' + @colsPivot +')
      )p'


execute(@query)

See SQL Fiddle with Demo

参见SQL小提琴演示

#3

If you have multiple groups that you're looking to pivot around you can also do something like this:

如果你有多个基团你想要旋转你也可以这样做:

Select *
  from table
  unpivot include nulls ( (test_name, test_date) for col_nm in ( (test1, date1) as 'test_val1'
                                                                ,(test2, date2) as 'test_val2'
                                                                ,(test3, date3) as 'test_val3'
                                                               )
                     )

#1