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
 how to use pivot in this scenario????
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/18/2013 :  10:54:57  Show Profile  Reply with Quote
I have an requirement where i need to show Employee Table and CustomerMeta Table joins In CustomerMeta Table (CustID)
Reference to Employee Table and Metavalues table Metavalues table is like master table.
In Application i will get multiple select box selection (DrivingLicense,Passport etc;) so that data will be inserted
in comma(',') seperated values So in my desired output i need to show as i need to show comma seperated values
As columns and they have inserted in CustomerMeta Table (DrivingLicense,Passport) they need to show as YES OR NO as
i shown in output....Suggest me


Metavalues table :

MetaID  Metavaluedescription  
1       Driving License       
2       Passport
3       AadharCard
4	    EducationalProof	
5       ResidentialProof


CustomerMeta Table :


CustID MetaTypeID  MetaTypeName
2      1,2,3,4,5   DrivingLicense,Passport,AadharCard,EducationalProof,ResidentialProof
3      1,2,3		DrivingLicense,Passport,AadharCard


Employee Table


EmpID CustID EmPname 
1001   2		Mohan
1002   3        ramu


Desired OutPut :


EmpID CustID  EmPname  DrivingLicense  Passport AadharCard EducationalProof ResidentialProof
1001   2		Mohan   YES				NO		YES			NO					NO
1002   3		ramu    NO				YES		NO			NO					NO


P.V.P.MOhan

Edited by - mohan123 on 04/19/2013 03:18:33

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/18/2013 :  13:59:43  Show Profile  Reply with Quote

create table Metavalues
(
MetaID int,
 Metavaluedescription  varchar(100)
 )
 insert Metavalues
values(1,       'DrivingLicense'),
(2       ,'Passport'),
(3       ,'AadharCard'),
(4	    ,'EducationalProof')	,
(5       ,'ResidentialProof')


create table CustomerMeta
(
CustID int,
MetaTypeID varchar(100),
MetaTypeName varchar(1000)
)
insert CustomerMeta
values(2,      '1,2,3,4,5',   'DrivingLicense,Passport,AadharCard,EducationalProof,ResidentialProof'),
(3,      '1,2,3',		'DrivingLicense,Passport,AadharCard')


create table Employee1
(
EmpID int,
CustID int,
EmPname varchar(100)
)
insert Employee1
values(1001,   2,		'Mohan'),
(1002,   3        ,'ramu')

DECLARE @MetavaluesList varchar(max)
SELECT @MetavaluesList= STUFF((SELECT ',[' + Metavaluedescription + ']' FROM Metavalues ORDER BY MetaID FOR XML PATH('')),1,1,'')

DECLARE @SqlQuery varchar(max)
SET @SqlQuery='
SELECT *
FROM
(
SELECT e.*,Metavaluedescription
FROM CustomerMeta m
JOIN Employee1 e
ON e.CustID = m.CustID
JOIN Metavalues v
ON  '','' + m.MetaTypeName + '','' LIKE ''%,'' + v.Metavaluedescription + '',%'' 
)m
PIVOT (COUNT(Metavaluedescription) FOR Metavaluedescription IN (' + @MetavaluesList + '))p'

EXEC(@sqlquery)

DROP TABLE CustomerMeta
DROP TABLE Employee1
DROP TABLE Metavalues


output
---------------------------------------------------------------------------------------------------------------------
EmpID	CustID	EmPname	DrivingLicense	Passport	AadharCard	EducationalProof	ResidentialProof
---------------------------------------------------------------------------------------------------------------------
1001	2	Mohan	1	        1	        1	        1	                1
1002	3	ramu	1	        1	        1	        0	                0



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/19/2013 :  02:00:34  Show Profile  Reply with Quote
Do you want YES/NO based on count?

SELECT EmpID, CustID,
CASE WHEN [DrivingLicense] >0 THEN 'YES' ELSE 'NO' END [DrivingLicense],
CASE WHEN [Passport] >0 THEN 'YES' ELSE 'NO' END [Passport],
CASE WHEN [AadharCard] >0 THEN 'YES' ELSE 'NO' END [AadharCard],
CASE WHEN [EducationalProof] >0 THEN 'YES' ELSE 'NO' END [EducationalProof],
CASE WHEN [ResidentialProof] >0 THEN 'YES' ELSE 'NO' END [ResidentialProof]
FROM
(
SELECT e.*,Metavaluedescription
FROM CustomerMeta m
JOIN Employee1 e
ON e.CustID = m.CustID
JOIN Metavalues v
ON  ',' + m.MetaTypeName + ',' LIKE '%,' + v.Metavaluedescription + ',%' 
)m
PIVOT (COUNT(Metavaluedescription) FOR Metavaluedescription IN ([DrivingLicense],[Passport],[AadharCard],[EducationalProof],[ResidentialProof]))p


--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  02:06:43  Show Profile  Reply with Quote
thanks visakh and chandu but please suggest me the recent posted query

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/19/2013 :  02:16:22  Show Profile  Reply with Quote
whats the recent posted query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  02:18:16  Show Profile  Reply with Quote
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184687

P.V.P.MOhan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2215 Posts

Posted - 04/19/2013 :  02:28:43  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184687
P.V.P.MOhan



Check reply in the above link

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/19/2013 :  02:33:39  Show Profile  Reply with Quote
Why are you opening multiple threads for same question? why not keep every related posts in same thread?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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