Transpose results of a sql query

Question

How to transpose this display.

SeqNo   Step        Date        By
1       Quoted      2018-03-01  Person1
2       Checked     2018-03-02  Person2
3       Authorized  2018-03-02  Person3
4       Approved    2018-03-03  Person4

Into this display.

1               2               3               4
Quoted          Checked         Authorized      Approved
2018-03-01      2018-03-02      2018-03-02      2018-03-03
Person1         Person2         Person3         Person4

Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — Koby Douek, Mar 28 '18 at 06:56

score 4 · Accepted Answer · answered Mar 28 '18 at 07:15

You'll have to use combination of unpivot and pivot syntax to achieve your result and also end up casting everything in to same compatible datatype like varchar(max)

live demo here

See query below

create table srcTable (SeqNo int,  Step varchar(10), [Date] date,  [By] varchar(10));
insert into srcTable values
(1,'Quoted','2018-03-01','Person1')
,(2,'Checked','2018-03-02','Person2')
,(3,'Authorized','2018-03-02','Person3')
,(4,'Approved','2018-03-03','Person4')

select [1],[2],[3],[4] from
(
    select 
        SeqNo, 
        [c2]=cast(Step as varchar(max)) ,
        [c3]=cast([Date] as varchar(max)),
        [c4]=cast([By] as varchar(max))
    from 
         srcTable
    )s
unpivot
( 
    value for data in ([c2],[c3],[c4])
)up
pivot
(
    max(value) for SeqNo in ([1],[2],[3],[4])
   )p

Transpose results of a sql query

1 Answers1