0

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
Improve this question
3
  • When a duns has several rows, how do you know which rows go get the aq, ar, as, at and cd values from? 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 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. Commented Jul 24, 2019 at 12:27

1 Answer 1

Reset to default
1 
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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..

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.