PIVOT question

Last Post 04 Jul 2007 06:14 PM by SQLUSA. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
SQLUSA
New Member
New Member

--
04 Jul 2007 02:52 PM
Assume you have a staging table with 2 columns:
Tag varchar(20), Value varchar(50)

Population:

Name Charlie Brown
Address: 123 Main
City Los Alamos
State CA
Zipcode 11111


How do you flip it over to columns using PIVOT.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
Professional SQL Server 2005 Training - SQLUSA: http://www.sqlusa.com/
SQLUSA
New Member
New Member

--
04 Jul 2007 06:14 PM
For the sake of argument assuming only a single address in the staging table.

How about this?

select Name, Addess, City, State, Zipcode
from dbo.NameAddressStage
PIVOT(min(Value) for Tag in ([Name], [Address], [City], [State], [Zipcode])) as aPivot

SQLUSA
New Member
New Member

--
05 Jul 2007 04:54 AM
Assume there is 3rd columnt RecordNum int - same value for the 5 rows of a name and address.

Is there a clean way to flip it do it with PIVOT? Or fake it using the MIN (or max) aggregate function?

Kalman Toth
http://www.sqlusa.com
SQLUSA
New Member
New Member

--
05 Jul 2007 05:19 AM
Here is the WORKING code.

MY QUESTION: is there a clean way? Kind of faking it with the MIN function.....

Kalman Toth, Database Architect
SQL Server 2008 Training - http://www.sqlusa.com

==============================================================
use tempdb
go
-- drop table NameAddressStage
create table NameAddressStage
( RecordNum int,
Tag varchar(20),
[Value] varchar(50)
)
go

insert NameAddressStage values (1, 'Name', 'Bonnie Brown')
insert NameAddressStage values (1, 'Address', '124 Washington Street')
insert NameAddressStage values (1, 'City', 'Bevelry Hills')
insert NameAddressStage values (1, 'State', 'CA')
insert NameAddressStage values (1, 'Zipcode', '90210')

insert NameAddressStage values (2, 'Name', 'Kevin Brown')
insert NameAddressStage values (2, 'Address', '124 Washington Street')
insert NameAddressStage values (2, 'City', 'Bevelry Hills')
insert NameAddressStage values (2, 'State', 'CA')
insert NameAddressStage values (2, 'Zipcode', '90210')

insert NameAddressStage values (3, 'Name', 'Jessica Eve Simpson')
insert NameAddressStage values (3, 'Address', '524 Sunset Road')
insert NameAddressStage values (3, 'City', 'Bevelry Hills')
insert NameAddressStage values (3, 'State', 'CA')
insert NameAddressStage values (3, 'Zipcode', '90210')
go


select RecordNum, [Name], Address, City, State, Zipcode
from dbo.NameAddressStage
PIVOT(min([Value])
for Tag in ([Name], [Address], [City], [State], [Zipcode]))
as aPivot
You are not authorized to post a reply.

Acceptable Use Policy