PIVOT table with two rows per pivot field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

PIVOT table with two rows per pivot field

Author

Topic

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-02-18 : 05:37:59

I want to create a pivot table with if possible two rows per person - one for the oldvalue and one for the newvalue. Script to create an example is below - including a Select/Pivot statement which produces a row per record (help!).

I want the result set to look as follows (oldvalue in the first row for each person and newvalue in the second row for each person)

FullName Address1 Phone Period JobTitle
Andrea Summer NULL 5673 NULL Tester
Andrea Summer 22 Road 234 NULL Prover
John Edge NULL 234563456 NULL Grower
John Edge 47 Crescent 875876456 NULL Farmer
Peter James 33 Street 1234 NULL Assistant
Peter James 34 Street 2345 NULL Boss

/*****************
Create a Pivot table
*****************/
CREATE TABLE [dbo].[PeopleValues](
[PeopleValues_ID] [uniqueidentifier] NOT NULL,
[FieldName] [nvarchar](30) NULL,
[People_ID] [uniqueidentifier] NOT NULL,
[FullName] [nvarchar](30) NULL,
[OldValue] [nvarchar](30) NULL,
[NewValue] [nvarchar](30) NULL)

INSERT INTO [dbo].[PeopleValues]([PeopleValues_ID], [FieldName], [People_ID],[FullName],[OldValue],[NewValue])
SELECT NewID(), 'Address1', '311ADD0C-CC8A-45E3-8AEF-0A77957F9B56', 'Peter James', '33 Street', '34 Street' UNION ALL
SELECT NewID(), 'Address1', '4E5EDE5C-3A22-4E73-8C0D-0AD8F9F00402', 'Andrea Summer', Null, '22 Road' UNION ALL
SELECT NewID(), 'Address1', 'C85BA2EE-CC95-4C61-AF83-108244555EDB', 'John Edge', Null, '47 Crescent' UNION ALL
SELECT NewID(), 'Phone', '311ADD0C-CC8A-45E3-8AEF-0A77957F9B56', 'Peter James', '1234', '2345' UNION ALL
SELECT NewID(), 'Phone', '4E5EDE5C-3A22-4E73-8C0D-0AD8F9F00402', 'Andrea Summer', '5673', '234' UNION ALL
SELECT NewID(), 'Phone', 'C85BA2EE-CC95-4C61-AF83-108244555EDB', 'John Edge', '234563456', '875876456' UNION ALL
SELECT NewID(), 'JobTitle', '311ADD0C-CC8A-45E3-8AEF-0A77957F9B56', 'Peter James', 'Assistant', 'Boss' UNION ALL
SELECT NewID(), 'JobTitle', '4E5EDE5C-3A22-4E73-8C0D-0AD8F9F00402', 'Andrea Summer', 'Tester', 'Prover' UNION ALL
SELECT NewID(), 'JobTitle', 'C85BA2EE-CC95-4C61-AF83-108244555EDB', 'John Edge', 'Grower', 'Farmer'

--And the following which does a bad partial job !!

SELECT * FROM [PeopleValues]
PIVOT
(
Max(OldValue)
FOR [FieldName] IN ([Address1],[Phone], [Period], [JobTitle])
)
AS p

UNION ALL
SELECT * FROM [PeopleValues]
PIVOT
(
Max(NewValue)
FOR [FieldName] IN ([Address1],[Phone], [Period], [JobTitle])
)
AS p
Order by fullname

--thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 05:48:22

[code]
SELECT * FROM (SELECT [FieldName],[FullName],[OldValue] FROM [PeopleValues])t
PIVOT
(
Max(OldValue)
FOR [FieldName] IN ([Address1],[Phone], [Period], [JobTitle])
)
AS p

UNION ALL
SELECT * FROM (SELECT [FieldName],[FullName],[NewValue] FROM [PeopleValues])t
PIVOT
(
Max(NewValue)
FOR [FieldName] IN ([Address1],[Phone], [Period], [JobTitle])
)
AS p
Order by fullname

output
------------------------------
FullName Address1 Phone Period JobTitle
Andrea Summer NULL 5673 NULL Tester
Andrea Summer 22 Road 234 NULL Prover
John Edge 47 Crescent 875876456 NULL Farmer
John Edge NULL 234563456 NULL Grower
Peter James 33 Street 1234 NULL Assistant
Peter James 34 Street 2345 NULL Boss

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-02-18 : 06:01:49

Thank you Visakh - exactly what I wanted. Is it possible to do this without hand coding the values from FieldName in the For clause? again thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 06:07:23

yup. see the below link

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Buzzard724
Yak Posting Veteran

66 Posts

Posted - 2010-02-18 : 06:13:07

OK - so now my ignorance about stored procedures - is it possible to use code like the example in your blog - to construct a View - in other words could this be a View Script?

CREATE VIEW DPRecords AS
EXEC dynamic_pivot
SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
Year(OrderDate)',
Count(OrderDate)'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-18 : 06:46:02

quote:
Originally posted by Buzzard724

OK - so now my ignorance about stored procedures - is it possible to use code like the example in your blog - to construct a View - in other words could this be a View Script?

CREATE VIEW DPRecords AS
EXEC dynamic_pivot
SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
Year(OrderDate)',
Count(OrderDate)'

Nope. you need to return it as a stored procedure resultset

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources