CODEWITHSUNDEEP
sql-servert-sql
Michael Thung
Michael Thung

Reputation: 15

how to make your data horizontal

I have 2 identical data in 2 row and I intend to make this data become 1 row. for example I have this data sample :

Name    Status  Bank
Thung   Active  ABC Bank
Thung   Hold    ABC Bank

can I make something like this ?

Name    Status 1    Bank 1  Status 2    Bank 2
Thung   Active  ABC Bank    Hold    ABC Bank

Upvotes: 0

Views: 399

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

SQL Fiddle

MS SQL Server 2017 Schema Setup:

create table MyTable(Name varchar(max),BStatus varchar(max),Bank varchar(max))
insert into MyTable (Name,BStatus,Bank)values('Thung','Active', 'ABC Bank')
insert into MyTable (Name,BStatus,Bank)values('Thung','Hold', 'ABC Bank')

Query 1:

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='ABC Bank' THEN Bank END) AS Bank1,
(CASE WHEN Bank='ABC Bank' 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

Results:

|  Name | Status1 | Status2 |    Bank1 |    Bank2 |
|-------|---------|---------|----------|----------|
| Thung |  Active |    Hold | ABC Bank | ABC Bank |

Upvotes: 4

Related Questions