Using SQL - Convert column to rows - not a straight forward pivot
-
Hello All,
I have below sample table. Code is also included.
create table Trade_test
(portfoliocode nvarchar(10),
tradedate varchar(10),
tradeamount varchar(10)
);
insert into Trade_test
values('A','01012021','100')
insert into Trade_test
values('A','01012022','1000')
insert into Trade_test
values('B','01012018','100')
insert into Trade_test
values('C','01012019','100')I need to pivot the data to have below output.
I have tried few things but not getting required output. Please advise.
-
Before the pivot it needs to enumerate the rows partitioned within portfoliocode(s). Once you have the 1's and 2's (if any) you could pivot using conditional aggregation, or the built-in PIVOT operator. I never use the built-in PIVOT operator because it's less flexible in many ways (only 1 aggregate function type per pivot) and there's no performance advantage to it. For a conditional aggregation approach you could try something like this
with rn_cte(portfoliocode, tradedate, tradeamount, rn) as (
select *, row_number() over (partition by portfoliocode
order by tradedate)
from #Trade_test)
select portfoliocode,
max(case when rn=1 then tradedate else '' end) tradedate1,
max(case when rn=2 then tradedate else '' end) tradedate2,
max(case when rn=1 then tradeamount else '' end) tradeamount1,
max(case when rn=2 then tradeamount else '' end) tradeamount2
from rn_cte
group by portfoliocode
order by portfoliocode;Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Thank you.
I tweaked it a little and implemented this for my case and it worked.
- abi wrote:
Thank you.
I tweaked it a little and implemented this for my case and it worked.
Can you post your "tweaked" code? Others may learn from it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply