CODEWITHSUNDEEP
sqlsql-serverpivot-table
AJ Mann
AJ Mann

Reputation: 11

Data Transformation for Survey Data - SQL Server

I am new here and still learning so please give me grace as I attempt to explain what I am trying to do.

I have data that looks like this

what I currently have

BUT, I want it to look like this

What I want

I have the data loaded in our SQL Server and need to know how to transform it using SQL code.

Can someone help me?

Upvotes: 1

Views: 118

Answers (3)

Eray Balkanli
Eray Balkanli

Reputation: 7990

You need to use "unpivot" keyword like:

select u.SrNo, u.question, u.Answer
from yourtable
unpivot
(
  Answer
  for question in ([I like to go to school], [Learning is fun])
) u;

Upvotes: 1

Jason A. Long
Jason A. Long

Reputation: 4442

Here is a dynamic version of what Gordon proposed above...

IF OBJECT_ID('CodeTest.dbo.TestData', 'U') IS NOT NULL 
BEGIN DROP TABLE dbo.TestData; END;

CREATE TABLE dbo.TestData (
    SrNum INT NOT NULL,
    [I like to go to school] CHAR(3) NOT NULL,
    [Learning is fun] CHAR(3) NOT NULL 
    );
INSERT dbo.TestData (SrNum, [I like to go to school], [Learning is fun]) VALUES
    (1, 'Yes', 'Yes'), (2, 'Yes', 'Yes');


--=================================================

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT 
    @sql = CONCAT(@sql, N',
        (', c.column_id, N', ''', c.name, N''', td.[', c.name, N'])')
FROM
    sys.columns c
WHERE 
    c.object_id = OBJECT_ID('CodeTest.dbo.TestData', 'U')
    AND c.column_id > 1

SET @sql = CONCAT(N'
SELECT 
    td.SrNum,
    qa.Question,
    qa.Answer
FROM 
    dbo.TestData td
    CROSS APPLY ( VALUES', 
    STUFF(@sql, 1, 1, N''), N'
        ) qa (Qnum, Question, Answer)
ORDER BY 
    td.SrNum,
    qa.Qnum;');

EXEC sys.sp_executesql @sql;

Results:

SrNum       Question               Answer
----------- ---------------------- ------
1           I like to go to school Yes
1           Learning is fun        Yes
2           I like to go to school Yes
2           Learning is fun        Yes

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270431

I would use cross apply:

select t.sr_no, v.*
from t cross apply
     (values ('I like to go to school', [I like to go to school]),
             ('Learning is fun', [Learning is fun])
     ) v(question, answer);

Upvotes: 1

Related Questions