Transform Columns to Rows dynamically using T-SQL

Question

I have the following table where I need to transform Columns to Rows

tbl_Survey:

      SurveyID   Quest_1  Quest_2  Quest_3
             7        1         1        1
             8        2         2        2
             9        3         3        3

I need to have the following result:

       SurveyID  Questions  Rating 
              7    Quest_1      1
              7    Quest_2      1
              7    Quest_3      1
              8    Quest_1      2
              8    Quest_2      2
              8    Quest_3      2
              9    Quest_1      3
              9    Quest_2      3
              9    Quest_3      3

To have this result I used the following code:

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

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

But, my Quest_1, Quest_2, Quest_3 values could potentially be changed / or even added a new once...

Are there ways to code them, so it can be used for any text values (not only for Quest_1 2 3)? Not necessary with the UNPIVOT... Could be any other way

Thank you

gotqn · Accepted Answer · 2020-07-30 08:22:40Z

2

You need to make a dynamic UNPIVOT. Check the example below:

DROP TABLE IF EXISTS [dbo].[DataSource];

CREATE TABLE [dbo].[DataSource]
(
    [SurveyID] INT
   ,[Quest_1] INT
   ,[Quest_2] INT
   ,[Quest_3] INT
);

INSERT INTO [dbo].[DataSource] ([SurveyID], [Quest_1], [Quest_2], [Quest_3])
VALUES (7, 1, 1, 1)
      ,(8, 2, 2, 2)
      ,(9, 3, 3, 3);

GO

DECLARE @DynamicTSQLStatement NVARCHAR(MAX);
DECLARE @DynamicTSQLUnpivotColumns NVARCHAR(MAX);

SET @DynamicTSQLUnpivotColumns = STUFF
(
    (
        SELECT ',' + QUOTENAME([name])
        FROM [sys].[columns]
        WHERE [object_id] = OBJECT_ID('[dbo].[DataSource]')
            AND [name] <> 'SurveyID'
        ORDER BY [name]
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)')
    ,1
    ,1
    ,''
);

SET @DynamicTSQLStatement = N'
SELECT [SurveyID], [Question], [Rating]
FROM [dbo].[DataSource]
UNPIVOT
(
    [Rating] FOR [Question] in (' + @DynamicTSQLUnpivotColumns + ')
) AS SurveyUnpivot';

EXEC sp_executesql @DynamicTSQLStatement;

answered Jul 30, 2020 at 8:22

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Attie Wagner Over a year ago

Out of curiosity, is stuff / xml better than string_agg? I personally prefer string_agg @gotqn

gotqn Over a year ago

@Birel It's better to use STRING_AGG, but since the it is supported with SQL Server 2016 and later and the question is not tag with version, I prefer to show the XML PATH technique as it will work on all editions.

Tim Biegeleisen · Accepted Answer · 2020-07-30 05:38:05Z

0

I prefer using unions here, due to the verbose nature of the syntax needed with PIVOT and UNPIVOT:

SELECT SurveyID, 'Quest_1' AS Questions, Quest_1 AS Rating FROM tbl_Survey UNION ALL
SELECT SurveyID, 'Quest_2', Quest_2 FROM tbl_Survey UNION ALL
SELECT SurveyID, 'Quest_3', Quest_3 FROM tbl_Survey
ORDER BY SurveyID, Rating;

answered Jul 30, 2020 at 5:38

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

1 Comment

Hell-1931 Over a year ago

I am going to try your query. But - still can't see the resolution of my problem ( What if I'll have "Quest_4545" or "ttt4444" instead of Quest_1 in my table, for example? In that case I have to update the code every time values change Is there any ways in t-sql - to avoid changing code every time I have updated value instead my current "Quest_1" "Quest_2" "Quest_3"?

Collectives™ on Stack Overflow

Transform Columns to Rows dynamically using T-SQL

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related