CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Rob
Rob

Reputation: 221

TSQL - Columns to Rows with New Columns

I'm looking for a slution to convert the following columns into two new rows with t-SQL (I'm using 2008 if that matters).

Here's an example of what I have: 

   [Question1] | [Question2] | [Question3] | [Question4] | [Question5] | [Question6]
1  'Answer1'     'Answer2'     'Answer3'     'Answer4'     'Answer5'     'Answer6'

Here's an example of what I would like:

   [Questions] | [Answers]
1  'Question1'   'Answer1'
2  'Question2'   'Answer2'
3  'Question3'   'Answer3'
4  'Question4'   'Answer4'
5  'Question5'   'Answer5'
6  'Question6'   'Answer6'

I hope my example was clear enough.

Thank you.

So between user2989408 and M.Ali I was able to piece together a query. I mostly used user2989408's query but it was soon discovered that I needed to set the datatypes for my query otherwise it wouldn't work. I just cast( column as varchar (max)) and it worked.

This is my end result.

select
    Question
,   Answer
from
    (select
    ,    cast( Question1 as varchar(max))
    ,    cast( Question2 as varchar(max))
    ,    cast( Question3 as varchar(max))
    ,    cast( Question4 as varchar(max))
    ,    cast( Question5 as varchar(max))
    ,    cast( Question6 as varchar(max))
    from table) p
unpivot
    (Answers for Questions in 
         (  Question1
         ,  Question2
         ,  Question3
         ,  Question4
         ,  Question5
         ,  Question6)
)AS unpvt;

Thank you very much.

Upvotes: 0

Views: 1313

Answers (2)

M.Ali
M.Ali

Reputation: 69494

DECLARE @TABLE TABLE([Questions] NVARCHAR(100),[Answers] NVARCHAR(100))
INSERT INTO @TABLE VALUES
('Question1','Answer1'),('Question2','Answer2'),('Question3','Answer3'),
('Question4','Answer4'),('Question5','Answer5'),('Question6','Answer6')

SELECT * FROM
(
SELECT * FROM @TABLE) T
PIVOT (MAX([Answers])
       FOR [Questions]
       IN ([Question1],[Question2],[Question3]
              ,[Question4],[Question5],[Question6])
       )p


╔═══════════╦═══════════╦═══════════╦═══════════╦═══════════╦═══════════╗
║ Question1 ║ Question2 ║ Question3 ║ Question4 ║ Question5 ║ Question6 ║
╠═══════════╬═══════════╬═══════════╬═══════════╬═══════════╬═══════════╣
║ Answer1   ║ Answer2   ║ Answer3   ║ Answer4   ║ Answer5   ║ Answer6   ║
╚═══════════╩═══════════╩═══════════╩═══════════╩═══════════╩═══════════╝

Upvotes: 1

user2989408
user2989408

Reputation: 3137

Try this UNPIVOT query. It should work.

SELECT ID, Questions, Answers
FROM 
    (SELECT ID, Question1, Question2, Question3, Question4, Question5, Question6
    FROM Table) p
UNPIVOT
    (Answers FOR Questions IN 
         (Question1, Question2, Question3, Question4, Question5, Question6)
)AS unpvt;

Upvotes: 3

Related Questions