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.
Author |
Topic |
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-18 : 10:54:57
|
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 valuesAs columns and they have inserted in CustomerMeta Table (DrivingLicense,Passport) they need to show as YES OR NO asi shown in output....Suggest me Metavalues table : MetaID Metavaluedescription 1 Driving License 2 Passport3 AadharCard4 EducationalProof 5 ResidentialProof CustomerMeta Table : CustID MetaTypeID MetaTypeName2 1,2,3,4,5 DrivingLicense,Passport,AadharCard,EducationalProof,ResidentialProof3 1,2,3 DrivingLicense,Passport,AadharCard Employee TableEmpID CustID EmPname 1001 2 Mohan1002 3 ramu Desired OutPut :EmpID CustID EmPname DrivingLicense Passport AadharCard EducationalProof ResidentialProof1001 2 Mohan YES NO YES NO NO1002 3 ramu NO YES NO NO NO P.V.P.MOhan |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-18 : 13:59:43
|
[code]create table Metavalues(MetaID int, Metavaluedescription varchar(100) ) insert Metavaluesvalues(1, 'DrivingLicense'),(2 ,'Passport'),(3 ,'AadharCard'),(4 ,'EducationalProof') ,(5 ,'ResidentialProof')create table CustomerMeta(CustID int,MetaTypeID varchar(100),MetaTypeName varchar(1000))insert CustomerMetavalues(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 Employee1values(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.*,MetavaluedescriptionFROM CustomerMeta mJOIN Employee1 eON e.CustID = m.CustIDJOIN Metavalues vON '','' + m.MetaTypeName + '','' LIKE ''%,'' + v.Metavaluedescription + '',%'' )mPIVOT (COUNT(Metavaluedescription) FOR Metavaluedescription IN (' + @MetavaluesList + '))p'EXEC(@sqlquery)DROP TABLE CustomerMetaDROP TABLE Employee1DROP TABLE Metavaluesoutput---------------------------------------------------------------------------------------------------------------------EmpID CustID EmPname DrivingLicense Passport AadharCard EducationalProof ResidentialProof---------------------------------------------------------------------------------------------------------------------1001 2 Mohan 1 1 1 1 11002 3 ramu 1 1 1 0 0[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 02:00:34
|
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.*,MetavaluedescriptionFROM CustomerMeta mJOIN Employee1 eON e.CustID = m.CustIDJOIN Metavalues vON ',' + m.MetaTypeName + ',' LIKE '%,' + v.Metavaluedescription + ',%' )mPIVOT (COUNT(Metavaluedescription) FOR Metavaluedescription IN ([DrivingLicense],[Passport],[AadharCard],[EducationalProof],[ResidentialProof]))p --Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 02:06:43
|
thanks visakh and chandu but please suggest me the recent posted queryP.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-19 : 02:16:22
|
whats the recent posted query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 02:18:16
|
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184687P.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 02:28:43
|
quote: Originally posted by mohan123 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=184687P.V.P.MOhan
Check reply in the above link--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-19 : 02:33:39
|
Why are you opening multiple threads for same question? why not keep every related posts in same thread?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|