CODEWITHSUNDEEP
sqlsql-servert-sql
Eliseo Jr
Eliseo Jr

Reputation: 141

Transpose results of a sql query

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

Upvotes: 1

Views: 62

Answers (1)

DhruvJoshi
DhruvJoshi

Reputation: 17146

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

Upvotes: 4

Related Questions