CODEWITHSUNDEEP
sqlsql-servert-sql
Ali Ryder
Ali Ryder

Reputation: 91

How to transpose rows/columns without aggregation?

I know questions similar to this have been asked but I can't quite find what I'm looking for. I know about PIVOT but I don't think it does what I need.

I have survey data stored in a table that looks like this:

Survey Question Answer
1 1 Blue
1 2 Puppy
1 3 Strawberry
2 1 Red
2 2 Kitten
2 3 Chocolate

And I would like it transposed to this:

Survey Q1 Q2 Q3
1 Blue Puppy Strawberry
2 Red Kitten Chocolate

I know about the PIVOT function but that seems to require numeric data and aggregate functions. Is there a shortcut to do this? I know how to do it the long way, just wondering if there's a more efficient solution.

Using SQL Server 2016. Thanks!

Upvotes: 0

Views: 581

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

You can use conditional aggregation as well:

select survey,
       max(case when question = 1 then answer end) as q1,
       max(case when question = 2 then answer end) as q2,
       max(case when question = 3 then answer end) as q3
from t
group by survey;

The max() and min() aggregation functions work on (just about) all data types, including strings.

That said, you could do this without aggregation:

select t1.survey, t1.answer as q1, t2.answer as q2, t3.answer as q3
from t t1 join
     t t2
     on t2.survey = t1.survey join
     t t3
     on t3.survey = t1.survey
where t1.question = 1 and t2.question = 2 and t3.question = 3;

I prefer the conditional aggregation approach because it is more versatile and less prone to error.

Upvotes: 3

AlwaysLearning
AlwaysLearning

Reputation: 8809

Pivot doesn't actually require numeric data to work with aggregate functions, e.g.:

create table dbo.SurveyData (
  Survey int,
  Question int,
  Answer varchar(10)
);
insert dbo.SurveyData (Survey, Question, Answer)
values
  (1, 1, 'Blue'),
  (1, 2, 'Puppy'),
  (1, 3, 'Strawberry'),
  (2, 1, 'Red'),
  (2, 2, 'Kitten'),
  (2, 3, 'Chocolate');

select pvt.*
from (
  select Survey, 'Q'+cast(Question as varchar(10)) as Question, Answer
  from dbo.SurveyData
) Source
pivot (max(Answer) for Question in ( [Q1], [Q2], [Q3] )) pvt;
Survey Q1 Q2 Q3
1 Blue Puppy Strawberry
2 Red Kitten Chocolate

Upvotes: 2

Related Questions