CODEWITHSUNDEEP
t-sqlunpivot
salvationishere
salvationishere

Reputation: 3511

how to convert single line SQL result into multiple rows?

I am developing a T-SQL query in SSMS 2008 R2 which returns one line only. But the problem is that in this one line there are four fields which I instead want to be unique rows. For example, my output line looks like:

Col. 1   Col. 2   Col. 3   Col. 4
xxxx     yyyy     zzzz     aaaa

Instead, I want this to look like:

Question    Answer
Col. 1      xxxx
Col. 2      yyyy
Col. 3      zzzz
Col. 4      aaaa

I have tried using the UNPIVOT operator for this, but it is not doing the above. How can I achieve this?

Upvotes: 1

Views: 8288

Answers (3)

mojave
mojave

Reputation: 31

UNION-ing together your four questions would look like:

SELECT 'column1' AS Question, MAX(column1) AS Answer UNION SELECT 'column2' , MAX(column2) UNION SELECT 'column3' , MAX(column3) UNION SELECT 'column4' , MAX(column4)

(obv just using the MAX as an example)

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

This is from my data names but it is tested 

select 'sID', sID as 'val' 
from [CSdemo01].[dbo].[docSVsys] 
where sID = 247   
union 
select 'sParID', sParID as 'val' 
from [CSdemo01].[dbo].[docSVsys] 
where sID = 247 ;

But UNPIVOT should work

Upvotes: 1

Taryn
Taryn

Reputation: 247760

You should be able to use UNPIVOT for this:

Here is a static pivot where you hard code in the values of the columns:

create table t1
(
    col1 varchar(5),
    col2 varchar(5),
    col3 varchar(5),
    col4 varchar(5)
)

insert into t1 values ('xxxx', 'yyyy', 'zzzz', 'aaaa')

select question, answer
FROM t1
unpivot
(
    answer
    for question in (col1, col2, col3, col4)
) u

drop table t1

Here is a SQL Fiddle with a demo.

but you can also use a Dynamic Unpivot:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

select @cols = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('t1') and
               C.name like 'Col%'
         for xml path('')), 1, 1, '')

set @query = 'SELECT question, answer
            from t1
            unpivot 
            (
               answer
               for question in (' + @cols + ')
            ) p '

execute(@query)

Upvotes: 8

Related Questions