Pivoting in SQL Server: A Guide on How to Use SQL for Pivot Operations

In my opinion, conditional aggregation is a better approach. This can be achieved by following the steps below. You can also refer to the working example provided in the db<>fiddle. However, if you insist on using explicit column names, it can be done but it would be more complicated. Therefore, if you only have two variables to work with and need to pivot them, it would be more logical to use the mentioned method.


Solution:

To achieve your desired outcome, employing a pivot and lead function is recommended.

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 |
+------+---+---+---+
*/

Please note that in this query, the rows are ordered using the id in the lead function.

Frequently Asked Questions