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!
Pivot Needed
Posted - 09/27/2012 : 00:33:04
