SQL Server transpose from rows to columns (pivot)

Question

I'm trying to use pivot function in sqlserver to make the transpose happen.

Here is the existed table:

id	account	time
123	abc	2023/1/2
123	def	2023/2/1
123	fsd	2023/2/22
456	ioj	2023/2/1
456	dju	2023/2/10

Ideally, after the transpose, the output will look like below (only transpose maximum 3 records to the column):

id	account1	time1	account2	time2	account3	time3
123	abc	2023/1/2	def	2023/2/1	fsd	2023/2/22
456	ioj	2023/2/1	dju	2023/2/10

I'm aware that using an inner join can make this happen, but wonder how I can achieve this by using "pivot" in sql server? Thanks.

Have you read the documentation for the `pivot` function? What exactly are you having trouble with? Show us your attempt! — Stu, Mar 13 '23 at 22:09
Does this answer your question? [SQL Dynamic Pivot with a twist](https://stackoverflow.com/questions/75674598/sql-dynamic-pivot-with-a-twist) — siggemannen, Mar 13 '23 at 22:25

score 1 · Answer 1 · answered Mar 13 '23 at 22:23

This is a bit trickier cause you have multiple columns and levels. What you could do is:

;with t AS (
    select *
    from (
        VALUES  (123, N'abc', N'2023/1/2')
        ,   (123, N'def', N'2023/2/1')
        ,   (123, N'fsd', N'2023/2/22')
        ,   (456, N'ioj', N'2023/2/1')
        ,   (456, N'dju', N'2023/2/10')
    ) t (id,account,time)
)
select id
,   MAX(CASE WHEN sort = 1 THEN account END) AS account1
,   MAX(CASE WHEN sort = 1 THEN time END) AS time1
,   MAX(CASE WHEN sort = 2 THEN account END) AS account2
,   MAX(CASE WHEN sort = 2 THEN time END) AS time2
,   MAX(CASE WHEN sort = 3 THEN account END) AS account3
,   MAX(CASE WHEN sort = 3 THEN time END) AS time3
from (
    select row_number() over(partition by id order by account, time) AS sort
    ,   *
    FROM    t
  ) x
group by id

You create a sort column which allows you to place each field in correct column "slot", then you do a MAX CASE WHEN to get the value you need.

SQL Server transpose from rows to columns (pivot)

1 Answers1