Convert Row To Column

We can Convert row to column by Twoways (Select Case – Pivot) let’s see How can i make this

Way No One

—Create Table

create Table States
(
id int primary key identity,
City nvarchar (20),
PersonName nvarchar (20)
)

—-Insert Data

insert into States values (‘Egypt’,’MOstafa’)
insert into States values (‘Egypt’,’Sayed’)
insert into States values (‘Egypt’,’Refay’)
insert into States values (‘London’,’Jon’)
insert into States values (‘London’,’Better’)
insert into States values (‘London’,’Cristian’)

—–Convert row to column

SELECT dbo.States.id,
(CASE City WHEN ‘Egypt’ THEN
(PersonName )  ELSE ‘No Name’ END) AS Egypt
,(CASE City WHEN ‘London’ THEN
(PersonName)ELSE ‘No Name’END) AS London
from States

Way No Two

—Create Table

CREATE TABLE [dbo].[Visit_Count_City](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[city] [nvarchar](50) NULL,
[VisitCount] [bigint] NULL
)

—–Insert Data

INSERT INTO visit_count_city VALUES (‘Mostafa’,’Sudia Arabia’,2)
INSERT INTO visit_count_city VALUES (‘Mostafa’,’Egypt’,2)
INSERT INTO visit_count_city VALUES (‘Mostafa’,’India’,2)
INSERT INTO visit_count_city VALUES (‘syaed’,’Sudia Arabia’,2)
INSERT INTO visit_count_city VALUES (‘Ahmed’,’Quter’,2)
INSERT INTO visit_count_city VALUES (‘Ali’,’Egypt’,2)
INSERT INTO visit_count_city VALUES (‘Mohamed’,’India’,2)

—-Convert row to column

SELECT * FROM
( SELECT p.NAME,p.city,p.VisitCount FROM dbo.Visit_Count_City AS p)datatable
PIVOT
(
SUM (VisitCount)
FOR city IN
(Egypt,[Sudia Arabia],Quter,India))PivotTable
go