SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot Needed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rypi
Yak Posting Veteran

55 Posts

Posted - 09/27/2012 :  00:33:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 09/27/2012 :  00:56:10  Show Profile  Reply with Quote
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
Go to Top of Page

rypi
Yak Posting Veteran

55 Posts

Posted - 09/27/2012 :  02:26:43  Show Profile  Reply with Quote
Perfect, exactly what I was looking for.

Thanks Chandu!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 09/27/2012 :  02:35:08  Show Profile  Reply with Quote
Welcome

--
Chandu
Go to Top of Page

sathish1980kos
Starting Member

India
1 Posts

Posted - 09/27/2012 :  04:30:15  Show Profile  Reply with Quote
very useful, thanks
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 10/05/2012 :  05:09:25  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
For dynamic number of custom field use http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000