Pivot Needed - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Pivot Needed

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2012-09-27 : 00:33:04

I have the following 3 example tables and data:



declare @Employee table (
ID int,
Name varchar(20)
)

insert into @Employee (
ID,
Name
)
values 
(1, 'Bob'),
(2, 'Doug'),
(3, 'Bill')

declare @CustomFields table (
ID int,
Descrip varchar(50)
)

insert into @CustomFields (
ID,
Descrip
)
values 
(1, 'Custom Field 1'),
(2, 'Custom Field 2')

declare @CustomFieldsData table (
ID int,
EmployeeId int,
CustomFieldId int,
DataDesc varchar(50))


insert into @CustomFieldsData (
ID,
EmployeeId,
CustomFieldId,
DataDesc
)
values 
(1, 1, 1, 'BobTest1'),
(2, 1, 2, 'BobTest2'),
(3, 2, 1, 'DougTest1'),
(3, 2, 2, 'DougTest2')

SELECT 
	E.Name,
	CF.Descrip,
	CFD.DataDesc
FROM	
	@Employee E,
	@CustomFields CF,
	@CustomFieldsData CFD
WHERE
	E.ID IN (1,2) AND
	E.ID = CFD.EmployeeId AND
	CFD.CustomFieldId = CF.ID

The statement returns the data as follows:


Name	Descrip	           DataDesc
Bob	Custom Field 1     BobTest1
Bob	Custom Field 2	   BobTest2
Doug	Custom Field 1	   DougTest1
Doug	Custom Field 2	   DougTest2

I need the data to be returned like so:


Name	Custom Field 1     Custom Field 2
Bob	BobTest1           BobTest2
Doug	DougTest1          DougTest2

I looked into a pivot function, but can't figure out how it would work in this case.
Is a Pivot needed or is there an easier way to return the data as show above.

Thanks!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-09-27 : 00:56:10

Hi, Check this code

SELECT Name, [Custom Field 1], [Custom Field 2]
FROM
( SELECT
E.Name,
CF.Descrip,
CFD.DataDesc
FROM
@Employee E,
@CustomFields CF,
@CustomFieldsData CFD
WHERE
E.ID IN (1,2) AND
E.ID = CFD.EmployeeId AND
CFD.CustomFieldId = CF.ID )p
PIVOT
( max(DataDesc) FOR Descrip IN ([Custom Field 1], [Custom Field 2]) )as pvt

--
Chandu