SQL Server Pivot on string data?

Question

I have a table like this:

And I want to change table structure to this format:

I used this query :

Declare @SQL varchar(max) = Stuff((Select Distinct ',' + QuoteName([Q]) Frommytable  Order by 1 For XML Path('')),1,1,'') 
Select  @SQL = 'Select [user],' + @SQL + '
From mytablle
 Pivot ( sum(Answer) For [Q] in (' + @SQL + ') ) p'

Exec(@SQL);

But it didn't work as we can't use sum function for [Answer] (it's string). What is your guide for this problem?

Well if it's a string then what aggregation do you want? Perhaps MAX? Which version of SQL Server are you on? — Charlieface
– Charlieface, Commented Jan 2, 2022 at 9:09
Please avoid posting images of data, we cannot cut and paste from an image, sample data should be consumable text in your question, ideally as create and insert statements, or alternatively a DB<>Fiddle — Stu
– Stu, Commented Jan 2, 2022 at 9:38
Does this answer your question? SQL Server - PIVOT on CASE statement — Stu
– Stu, Commented Jan 2, 2022 at 9:42
My data is string! Min or Max doesn't work for it. I want just data. my question is similar SQL Server - PIVOT on CASE statement but my data is not number so we cannot use numerical function — Amirhossein Omidifard
– Amirhossein Omidifard, Commented Jan 2, 2022 at 10:58

Patricio Contreras Pardo · Accepted Answer · 2022-01-02 15:43:03Z

3

You can use a pivot and lead function to get your result.

Check here for reference:

Microsoft Docs - Lead function

Microsoft Docs - Pivot

Here you have two examples:

select 
    [user],
    max([x]) [x],
    max([y]) [y],
    max([z]) [z]
from tbl
pivot (max(answer) for q in ([x],[y],[z])) p
group by [user]

-- Result
/*
+------+---+---+---+
| user | x | y | z |
+------+---+---+---+
| i1   | 1 | 2 | 5 |
| i2   | 0 | 4 | 5 |
| i3   | 1 | 4 | 6 |
+------+---+---+---+
*/

with t as (
    select 
    [user],
    [x],
    Lead([y],1) over(partition by [user] order by [id]) [y],
    Lead([z],2) over(partition by [user] order by [id]) [z]
    from tbl
    pivot (max(answer) for q in ([x],[y],[z])) p
)
select * from t
where [x] is not null

-- Result
/*
+------+---+---+---+
| user | x | y | z |
+------+---+---+---+
| i1   | 1 | 2 | 5 |
| i2   | 0 | 4 | 5 |
| i3   | 1 | 4 | 6 |
+------+---+---+---+
*/

Note: this query uses the id to order rows in the lead function.

edited Jan 2, 2022 at 15:43

answered Jan 2, 2022 at 15:19

Patricio Contreras Pardo

5072 gold badges4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amirhossein Omidifard Over a year ago

thank you very much. the first code worked without any errors but display all cells "Null". I don't what is the problem

Collectives™ on Stack Overflow

SQL Server Pivot on string data?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related