convert multiple rows into columns with data in sql server

Question

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

When a duns has several rows, how do you know which rows go get the aq, ar, as, at and cd values from? — jarlh
– jarlh, Commented Jul 24, 2019 at 12:18
we are arranging the data as per out put showing above. Due to flat structure of database i could not get the output as above — jagdish pawar
– jagdish pawar, Commented Jul 24, 2019 at 12:24
From what I get, he's just outputting all of them. Should there be a "hole", like in the "at" column, he'll probably just write nulls. The problem is when there's a combination of multiple "aq", "ar", "as" from the same "duns", in which case the provided data doesn't give a way to univocally match. Looking at the data, there's probably some ordering involved so the first row of "as" will map with the first row of "cd" and so on so forth. I would also ask if there are other columns in said tables to help perform the matching or, in case there isn't any, what's the ordering criteria. — Filippo Possenti
– Filippo Possenti, Commented Jul 24, 2019 at 12:27

Ajay · Accepted Answer · 2019-07-24 12:36:15Z

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

it works Ajay. Thanks Man. i have added 5 more loops into data. total 8 to 9 rows of data for single duns is coming.
Your welcome, If your are satisfied with my answer then upvote and accept my answer..

Collectives™ on Stack Overflow

convert multiple rows into columns with data in sql server

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related