CODEWITHSUNDEEP
sqlsql-server
jagdish pawar
jagdish pawar

Reputation: 1

convert multiple rows into columns with data in sql server

I tried to convert rows into columns. And multiple rows value into columns.

i have table structure like below.

duns    FieldCode   FieldData
650052418   aq      356
650052418   aq      356
650052418   ar      22
650052418   ar      22
650052418   as      66657833
650052418   as      66658282
650052418   cd      2812
650052418   cd      2813
650199482   aq      356
650199482   aq      356
650199482   ar      79
650199482   ar      79
650199482   as      26868100
650199482   as      26862365
650199482   at      1
650199482   at      2
650199482   cd      2834
650199482   cd      2834
862286734   aq      356
862286734   aq      356
862286734   ar      124
862286734   ar      124
862286734   as      6790000
862286734   as      4081247
862286734   at      1
862286734   at      2
862286734   cd      2879
862286734   cd      2879

i need out put as below.

duns        aq  ar  as         at     cd    
650052418   356 22  66657833          2812  
650052418   356 22  66658282          2813  
650199482   356 79  26868100    1     2834
650199482   356 79  26862365    2     2834
862286734   356 124 6790000     1     2879
862286734   356 124 4081247     2     2879

Upvotes: 0

Views: 82

Answers (1)

Ajay
Ajay

Reputation: 774

select * from (
select t1.dun,t1.FeildCode,t1.FeildData from 
(select *,ROW_NUMBER()over(partition by dun,FeildCode order by dun) rno 
from @tab
) t1 where t1.rno = 1
) t
pivot(max(t.FeildData) for t.FeildCode in ([aq],[ar],[as],[at],[cd]) ) as p

union all
select * from (
select t1.dun,t1.FeildCode,t1.FeildData from 
(select *,ROW_NUMBER()over(partition by dun,FeildCode order by dun) rno 
from @tab
) t1 where t1.rno = 2
) t
pivot(max(t.FeildData) for t.FeildCode in ([aq],[ar],[as],[at],[cd]) ) as p
order by dun

Result :

dun         aq  ar  as          at      cd
650052418   356 22  66657833    NULL    2812
650052418   356 22  66658282    NULL    2813
650199482   356 79  26862365    2       2834
650199482   356 79  26868100    1       2834
862286734   356 124 6790000     1       2879
862286734   356 124 4081247     2       2879

Upvotes: 1

Related Questions