Make the data horizental

C# Corner

ManMan

2y728SQL Server

I have this data sample :

Name Status Bank
XXXX Active BankA
XXXX Hold BankB

How can I make something like this ?

Name Status 1 Bank 1 Status 2 Bank 2
XXXX Active BankA Hold BankB

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Amira Bedhiafi2y ago

Accepted answer

with CTE AS (select *,
(CASE WHEN BStatus='Active' THEN BStatus  END) AS Status1,
(CASE WHEN BStatus = 'Hold' THEN BStatus END) AS Status2,
(CASE WHEN Bank='BankA' THEN Bank END) AS Bank1,
(CASE WHEN Bank='BankB' THEN Bank END) AS Bank2,
ROW_NUMBER() OVER (PARTITION BY BStatus,Bank Order By Name) as rn
from MyTable
group by Name,BStatus,Bank              )

select c.Name
,max(c.Status1) AS Status1
,max(c.Status2) AS Status2
,max(c.Bank1) AS Bank1
,max(c.Bank2) AS Bank2
from cte c
where rn=1
group by c.Name,c.Bank