Columns to Rows in T-SQL

Question

I have table tbl_Survey:

      SurveyID 1  2  3  4

            7  4  4  4  4
            8  3  3  3  3
            9  2  2  2  2

My goal is to transfer table headers - 1 2 3 4 into rows, as the following:

enter 
      SurveyID  Ouestion Rating

             7        1       4
             7        2       4
             7        3       4
             7        4       4
             8        1       3
             8        2       3
             8        3       3
             8        4       3
             9        1       2
             9        2       2
             9        3       2
             9        4       2

My code is (trying to follow help recommendations):

     SELECT [SurveyID]
           ,[Question]
           ,[Rating]
  
     FROM 
         [tbl_Survey]
         cross apply
                   (
                    values
                          ('1', 1 ),
                          ('2', 2 ),
                          ('3', 3 ),
                          ('4', 4 )
                   ) c (Question, Rating);

Results are not fully correct (Rating column is a problem):

         SurveyID  Ouestion Rating

             7        1       1
             7        2       2
             7        3       3
             7        4       4
             8        1       1
             8        2       2
             8        3       3
             8        4       4
             9        1       1
             9        2       2
             9        3       3
             9        4       4

Please, help...

My problem (because of which I couldn't proceed) was that I haven't used brackets for my code.

Here is the updated code for this:

          SELECT [SurveyID], [Question], [Rating]

          FROM [dbo].[tbl_Survey]

              UNPIVOT
              (
               [Rating]
                
                FOR [Question] in ([1], [2], [3], [4])
               
               ) AS SurveyUnpivot

Does this answer your question? [Unpivot with column name](https://stackoverflow.com/questions/19055902/unpivot-with-column-name) — Thom A, Jul 24 '20 at 10:14
@Larnu I am stuck with my code following those instructions...Trying again and will post in a few min — Hell-1931, Jul 24 '20 at 10:23

drj99 · Answer 1 · 2020-07-24T15:22:24.197

1

How about this:

DECLARE @T TABLE (SurveyID int, q1 int, q2 int,  q3 int, q4 int) 
INSERT @T (SurveyID, q1, q2, q3, q4) 
VALUES (7,4,4,4,4), (8,3,3,3,3), (9, 2, 2, 2, 2)

SELECT SurveyID, REPLACE(Question,'q','') as Question, Rating 
FROM @T UNPIVOT (Rating FOR Question in (q1, q2, q3, q4)) as UPV

edited Jul 24 '20 at 15:22

answered Jul 24 '20 at 13:58

drj99

11
3

This could work, as well as my new updated code (I am going to update the main post). But I am challenged with the new problem - I need to be able to make this work - even in case if my 1 2 3 4 headers will be changed into anything else. In case if instead 1 2 3 4 I'd have something like hi1 hi2 hi3 hi4 or anything. So I need dynamic version of this... – Hell-1931 Jul 30 '20 at 04:56

score 0 · Answer 2 · answered Aug 03 '20 at 15:57

Same approach. Just make sure you use a global temporary table as a temp table will not be visible in the scope of the EXEC statement. This should work with any column name and any number of columns.

IF OBJECT_ID('tempdb..##T') IS NOT NULL DROP TABLE ##T
CREATE TABLE ##T (SurveyID int, xxxxx int, yyyyy int, zzzzzz int, tttttt int) 
INSERT ##T VALUES (7,4,4,4,4), (8,3,3,3,3), (9, 2, 2, 2, 2)

DECLARE @Colnames nvarchar(4000)
SELECT @Colnames = STUFF((SELECT ',[' + [name] +']' FROM tempdb.sys.columns where object_id = object_id('tempdb..##T') AND name <> 'SurveyID' FOR XML PATH('') ),1,1,'')

DECLARE @SQL nvarchar(4000)  SET @SQL = 'SELECT SurveyID, Question, Rating FROM ##T UNPIVOT (Rating FOR Question in ('+@colnames+')) as UPV'
EXEC(@SQL)

Columns to Rows in T-SQL

2 Answers2