Using SQL - Convert column to rows - not a straight forward pivot

Question

Post reply

Using SQL - Convert column to rows - not a straight forward pivot

abi

SSC-Addicted

Points: 454

December 16, 2022 at 10:32 pm

#4126740

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.

December 23, 2022 at 1:06 pm

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

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

abi SSC-Addicted Points: 454 More actions · Answer 2

Thank you.

I tweaked it a little and implemented this for my case and it worked.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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)