RSS

Convert Row To Column

26 Dec

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

 
Leave a comment

Posted by on December 26, 2011 in Script

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s