Transform Columns to Rows dynamically using T-SQL

  • Hell-1934

    SSC Veteran

    Points: 273

    Hi All,

    I have the following table :

    tbl_Survey

    tbl_Survey

     

    My goal is to transform it into the following format (to transpose Columns into Rows):

    Capture

    To achieve this I am using the following code (at the end of this post).

    It works perfectly, but only for these values [Quest_1]  [Quest_2]  [Quest_3]

    My reality - that a new values like "Quest_4" or "Question_5" or - anything text could be potentially added to an existing values in my table.

    Is there any way to code - dynamically - so that no matter how many more values added - I'd still have the new transposed (unpivoted) table with these new values?

    Not necessary could be UNPIVOT function.

    Might be anything else... Any method (as long as it's in T-SQL)

    Thank you

    SELECT [SurveyID], [Question], [Rating]
    FROM [dbo].[tbl_Survey]

    UNPIVOT
    (
    [Rating]
    FOR [Question] in ([Quest_1], [Quest_2], [Quest_3])
    ) AS SurveyUnpivot

    • This topic was modified 1 month, 2 weeks ago by  Hell-1934.
    • This topic was modified 1 month, 2 weeks ago by  Hell-1934.
  • DesNorton

    SSC-Insane

    Points: 23277

    I have adapted the code from the following 2 links in order to create a sample of how to dynamically unpivot data

    if object_id('tempdb..#temp') is not null
    drop table #temp;
    GO
    create table #temp
    (
    date datetime,
    ABC money,
    DEF money,
    GHI VARCHAR(50)
    );
    insert into #temp(date, ABC, DEF, GHI) values ('1/1/2012', 1000.00, NULL, NULL);
    insert into #temp(date, ABC, DEF, GHI) values ('2/1/2012', NULL, 500.00, 'Hello');
    insert into #temp(date, ABC, DEF, GHI) values ('2/10/2012', NULL, 700.00, NULL);
    insert into #temp(date, ABC, DEF, GHI) values ('3/1/2012', 1100.00, NULL, 'World');
    --=======================================================================

    DECLARE
    @stcCols NVARCHAR(MAX)
    , @pvtCols NVARCHAR(MAX)
    , @cstCols NVARCHAR(MAX)
    , @Query NVARCHAR(MAX)
    , @ForceDataType NVARCHAR(50) = 'nvarchar(max)' --NULL --
    , @Debug BIT = 1; -- Set to 1 to output the final query being used to unpivot the data.

    DECLARE @tbCols TABLE (
    colID INT NOT NULL
    , colName SYSNAME NOT NULL
    , isStatic BIT NOT NULL DEFAULT(0)
    , hasError BIT NOT NULL DEFAULT(0)
    );
    INSERT INTO @tbCols (colID, colName)
    SELECT column_id, name
    FROM tempdb.sys.columns
    WHERE object_id = OBJECT_ID('tempdb..#temp')
    ORDER BY column_id;

    -- Idenify the columns we want to keep static
    UPDATE @tbCols
    SET isStatic = 1
    WHERE colName IN ('date', '');

    -- Build list of columns we want to remain static
    SET @stcCols = STUFF( (SELECT DISTINCT ',' + QUOTENAME(c.colName)
    FROM @tbCols c
    WHERE isStatic = 1
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    , 1, 1, '');

    -- Build list of cols we want to unpivot with the required datatype
    SET @cstCols = STUFF( (SELECT ', (''' + c.colName + ''', ' + CASE WHEN @ForceDataType IS NULL THEN QUOTENAME(c.colName)
    ELSE 'CAST(' + QUOTENAME(c.colName) + ' AS ' + @ForceDataType + '))'
    END + CHAR(13)+CHAR(10)
    FROM @tbCols c
    WHERE isStatic = 0
    GROUP BY c.colName
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    , 1, 1, ' ');

    SET @Query = N'
    SELECT ' + @stcCols + ', colName, Val
    FROM #temp
    CROSS APPLY (VALUES ' + CHAR(13)+CHAR(10) + @cstCols + ') x (colName, Val)
    WHERE Val IS NOT NULL;
    '

    IF (@Debug = 1) PRINT (@Query);
    EXEC sp_executesql @Query;

    drop table #temp;

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply