|
Execute the following
Microsoft SQL Server T-SQL script to demonstrate of swapping rows into columns:
USE tempdb
GO
-- drop table CustomerStage
CREATE TABLE CustomerStage (
RecordID INT,
Tag VARCHAR(20),
[Value] VARCHAR(50))
GO
INSERT CustomerStage
VALUES(1,'Name','Nicole Leslie Kidman')
INSERT CustomerStage
VALUES(1,'Address','124 Las Vegas Blvd.')
INSERT CustomerStage
VALUES(1,'City','Bevelry Hills')
INSERT CustomerStage
VALUES(1,'State','CA')
INSERT CustomerStage
VALUES(1,'Zipcode','90210')
INSERT CustomerStage
VALUES(1,'Email','nicolekidman@aol.com')
INSERT CustomerStage
VALUES(2,'Name','Kevin Brown')
INSERT CustomerStage
VALUES(2,'Address','124 Washington Street')
INSERT CustomerStage
VALUES(2,'City','Bevelry Hills')
INSERT CustomerStage
VALUES(2,'State','CA')
INSERT CustomerStage
VALUES(2,'Zipcode','90210')
INSERT CustomerStage
VALUES(2,'Email','kevinbrown@aol.com')
INSERT CustomerStage
VALUES(3,'Name','Jessica Eve Simpson')
INSERT CustomerStage
VALUES(3,'Address','524 Sunset Road')
INSERT CustomerStage
VALUES(3,'City','Bevelry Hills')
INSERT CustomerStage
VALUES(3,'State','CA')
INSERT CustomerStage
VALUES(3,'Zipcode','90210')
INSERT CustomerStage
VALUES(3,'Email','jessicasimpson@aol.com')
INSERT CustomerStage
VALUES(4,'Name','Paris Judy Hilton')
INSERT CustomerStage
VALUES(4,'Address','524 Canyon Road')
INSERT CustomerStage
VALUES(4,'City','Hollywood')
INSERT CustomerStage
VALUES(4,'State','CA')
INSERT CustomerStage
VALUES(4,'Zipcode','90211')
INSERT CustomerStage
VALUES(4,'Email','parishilton@aol.com')
GO
SELECT RecordID,
[Name],
Address,
City,
State,
Zipcode,
Email
FROM dbo.CustomerStage
PIVOT
(min([Value])
FOR Tag IN ( [Name],[Address],[City],[State],[Zipcode],[Email] ) ) AS PivotToColumns
ORDER BY RecordID
GO
Related article:
http://www.sqlusa.com/bestpractices/training/scripts/pivotunpivot/
|
