Unpivot table with multiple columns and dynamic column names

Question

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!

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)

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

Answer 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