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-19 : 01:57:00
|
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 split those comma seperatedand for every MetaTypeID MetaTypeName as a row as showed in desired 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 MetaTypeID MetaTypeName1001 2 Mohan 1 Driving License 1001 2 Mohan 2 Passport1001 2 Mohan 3 AadharCard1001 2 Mohan 4 EducationalProof1001 2 Mohan 5 ResidentialProof1002 3 ramu 1 Driving License 1002 3 ramu 2 Passport 1002 3 ramu 3 AadharCard P.V.P.MOhan |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 02:27:35
|
[code]SELECT e.*,Metavaluedescription, v.MetaIDFROM CustomerMeta mJOIN Employee1 eON e.CustID = m.CustIDJOIN Metavalues vON ',' + m.MetaTypeName + ',' LIKE '%,' + v.Metavaluedescription + ',%'[/code]--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-19 : 02:36:21
|
You could have got this very easily from my earlier solution. Whats the point in expecting a spoonfed answer for each question? why not try the modifications yourself?You will not learn or gain anything by asking and expecting spoonfed answer for each change you may have. Atleast an attempt should be done before you come and ask fro our help.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
quesenberry
Starting Member
1 Post |
Posted - 2013-04-19 : 02:36:45
|
unspammed |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 02:47:11
|
[CODE]SELECT CL.ProfileID, CD.Cust_ID, CB.FirstName As FirstNAME, CB.LastName As Lastname, CAST(CKK.ImageTypeID AS VARCHAR(MAX)) FROM Cust_BasicInfo CB INNER JOIN Cust_Details CD ON CB.Cust_ID = CD.Cust_ID INNER JOIN Cust_Login CL ON CB.Cust_ID = CL.Cust_ID INNER JOIN Emp_Details ED ON CB.ProfileOwnerEmpID = ED.EmpID INNER JOIN Cust_KaKSealImages CKK ON CL.Cust_ID = CKK.Cust_ID INNER JOIN Mst_Meta_Values MMV ON CKK.ImageTypeID = MMV.Meta_Value_ID AND ',' + CKK.ImageTypeID + ',' LIKE '%,' + MMV.Metavaluedescription + ',%' [/CODE]i implemented this on in my above query in your code and getting error because of it is not spliting any comma seperated values ProfileID Cust_ID FirstNAME Lastname ImageTypeID010000715 4 chaitanya Chinthapalli 454010000026 2 chaitanya Kishore 447,448,451,453 how to split this oneP.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-19 : 02:50:10
|
Please dont just tell you get an error. At least post the error message. How else can we understand what the error is? We dont have access to system neither can see how you;re trying to do it.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 02:50:48
|
so this one is spliited then how to exist this code in pivot table as you mentioned in before post SELECT CL.ProfileID, CD.Cust_ID,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 CL.ProfileID, CD.Cust_ID, CB.FirstName As FirstNAME, CB.LastName As Lastname, CAST(CKK.ImageTypeID AS INT) FROM Cust_BasicInfo CB INNER JOIN Cust_Details CD ON CB.Cust_ID = CD.Cust_ID INNER JOIN Cust_Login CL ON CB.Cust_ID = CL.Cust_ID INNER JOIN Emp_Details ED ON CB.ProfileOwnerEmpID = ED.EmpID INNER JOIN Cust_KaKSealImages CKK ON CL.Cust_ID = CKK.Cust_ID INNER JOIN Mst_Meta_Values MMV ON CKK.ImageTypeID = MMV.Meta_Value_ID AND ',' + CKK.ImageTypeID + ',' LIKE '%,' + MMV.Metavaluedescription + ',%' )mPIVOT (COUNT(Metavaluedescription) FOR Metavaluedescription IN ([DrivingLicense],[Passport],[AadharCard],[EducationalProof],[ResidentialProof]))p P.V.P.MOhan |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 02:52:58
|
Hi visakh it is not splitting comma seperated values just showing the plain result ProfileID Cust_ID FirstNAME Lastname ImageTypeID010000715 4 chaitanya Chinthapalli 454010000026 2 chaitanya Kishore 447,448,451,453 have to split and use this one in pivot tableP.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-19 : 02:53:00
|
please give us the full picture. your earlier requirement and this one is different . why do you want pivot table as well as result in rows. Can you clearly state whats your exact full requirement?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 02:55:33
|
>> reply for (Mohan123 Posted - 04/19/2013 : 02:52:58)You have to select MetaId from Master table, but not the MetaTypeID --Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 03:10:15
|
okay i wll explain you clearly this post and yesterday is complete i will explain you in full detailP.V.P.MOhan |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 03:30:23
|
i need to get some more joins in this query like name and date etc;So here i need to Mst_Meta_Values values rows as 5 columns along with [CODE]CL.ProfileID,CD.Cust_ID,CB.FirstName As FirstNAME,CB.LastName As Lastname[/CODE] SELECT CL.ProfileID, CD.Cust_ID, CB.FirstName As FirstNAME, CB.LastName As Lastname, CKK.ImageTypeID FROM Cust_BasicInfo CB INNER JOIN Cust_Details CD ON CB.Cust_ID = CD.Cust_ID INNER JOIN Cust_Login CL ON CB.Cust_ID = CL.Cust_ID INNER JOIN Emp_Details ED ON CB.ProfileOwnerEmpID = ED.EmpID INNER JOIN Cust_KaKSealImages CKK ON CL.Cust_ID = CKK.Cust_ID LEFT JOIN Mst_Meta_Values MMV ON CKK.KaKSealImage_ID = MMV.Meta_ID After executing this query i am getting result like this one I am getting out put like this one :ProfileID Cust_ID FirstNAME Lastname ImageTypeID010000026 2 chaitanya Kishore 447,448,451,453010000026 2 chaitanya Kishore 447,448,451,453 so now i need to split them and these values are from Mst_Meta_Values table MetaID Metavaluedescription 447 Driving License 448 Passport451 AadharCard453 EducationalProof 455 ResidentialProof So these rows to be refered as columns along with the joined columns like profile,custid,nameAtlast using pivot and any suggested query my out put should look like this one ProfileID CustID FirstName DrivingLicense Passport AadharCard EducationalProof ResidentialProof1001 2 Mohan YES NO YES NO NO1002 3 ramu NO YES NO NO NO 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 how to put my added columns as above mentioned query ..suggest meP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 03:35:14
|
FROM(SELECT e.*, Here add your columns, MetavaluedescriptionFROM CustomerMeta mJOIN Employee1 eON e.CustID = m.CustIDJOIN Metavalues vON ',' + m.MetaTypeName + ',' LIKE '%,' + v.Metavaluedescription + ',%' )m--Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 03:39:41
|
hi chanduthis is my query SELECT CL.ProfileID, CD.Cust_ID, CB.FirstName As FirstNAME, CB.LastName As Lastname, CKK.ImageTypeID FROM Cust_BasicInfo CB INNER JOIN Cust_Details CD ON CB.Cust_ID = CD.Cust_ID INNER JOIN Cust_Login CL ON CB.Cust_ID = CL.Cust_ID INNER JOIN Emp_Details ED ON CB.ProfileOwnerEmpID = ED.EmpID INNER JOIN Cust_KaKSealImages CKK ON CL.Cust_ID = CKK.Cust_ID LEFT JOIN Mst_Meta_Values MMV ON CKK.KaKSealImage_ID = MMV.Meta_ID how can i implement this in your pivot query by executing my query i am getting out put like thisProfileID Cust_ID FirstNAME Lastname ImageTypeID010000026 2 chaitanya Kishore 447,448,451,453010000026 2 chaitanya Kishore 447,448,451,453 P.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 04:44:29
|
--May be this?SELECT ProfileID, Cust_ID, FirstNAME, Lastname,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 CL.ProfileID, CD.Cust_ID, CB.FirstName As FirstNAME, CB.LastName As Lastname, CKK.ImageTypeID, MMV.MetavaluedescriptionFROM Cust_BasicInfo CBINNER JOIN Cust_Details CD ON CB.Cust_ID = CD.Cust_IDINNER JOIN Cust_Login CL ON CB.Cust_ID = CL.Cust_ID INNER JOIN Emp_Details ED ON CB.ProfileOwnerEmpID = ED.EmpIDINNER JOIN Cust_KaKSealImages CKK ON CL.Cust_ID = CKK.Cust_IDLEFT JOIN Mst_Meta_Values MMV ON CKK.KaKSealImage_ID = MMV.Meta_ID AND ',' + CD.MetaTypeName + ',' LIKE '%,' + MMV.Metavaluedescription + ',%' )mPIVOT (COUNT(Metavaluedescription) FOR Metavaluedescription IN ([DrivingLicense],[Passport],[AadharCard],[EducationalProof],[ResidentialProof]))p NOTE: Refer your respective column instead of CD.MetaTypeName --Chandu |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 04:51:53
|
If the above is not working , then try with the following LEFT JOIN partLEFT JOIN Mst_Meta_Values MMV ON CKK.KaKSealImage_ID = MMV.Meta_ID AND (',' + MMV.Meta_ID + ',' LIKE '%,' + CKK.ImageTypeID + ',%')--Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 05:50:46
|
Hi chandu ',' + CD.MetaTypeName + ',' LIKE '%,' + MMV.Metavaluedescription + ',%' this one is working fine rather than latest one but above whole query if i executed i am gettting output like thisProfileID Cust_ID FirstNAME Lastname 10th Memo DrivingLicense PanCard Passport VoteID AadharCard AppointmentLetter EducationalProof ResidentialProof010000026 2 chaitanya Kishore NO NO NO NO NO NO NO NO NO010000715 4 chaitanya Chinthapalli NO NO NO NO NO NO NO NO NO everything is coming as NO there is no sign of YES suggest meP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 06:06:23
|
In this part you have to include the names [10th Memo], [DrivingLicense],...... (as it is in the Metavaluedescription)FOR Metavaluedescription IN ([DrivingLicense],[Passport],[AadharCard],[EducationalProof],[ResidentialProof]))Cross check the results...(Are you getting count>0?)EDIT: Check with LEFT JOIN Mst_Meta_Values MMVON (',' + CD.MetaTypeName + ',' LIKE '%,' + MMV.Metavaluedescription + ',%')--Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 06:10:24
|
Hi chandu ',' + CD.MetaTypeName + ',' LIKE '%,' + MMV.Metavaluedescription + ',%' this one is working fine rather than latest one but above whole query if i executed i am gettting output like thisProfileID Cust_ID FirstNAME Lastname 10th Memo DrivingLicense PanCard Passport VoteID AadharCard AppointmentLetter EducationalProof ResidentialProof010000026 2 chaitanya Kishore NO NO NO NO NO NO NO NO NO010000715 4 chaitanya Chinthapalli NO NO NO NO NO NO NO NO NO everything is coming as NO there is no sign of YES suggest meP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-19 : 06:15:22
|
hi mohan, You have posted same again...Show us the complete query which you have currently...EDIT: Check the reply in 2nd page of this thread. Let us know the result--Chandu |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-04-19 : 06:35:16
|
yes they are the part of Metavaluedescription just like [10th Memo], [DrivingLicense],...... i executed the query what ever you gave the query it is giving all No and in my original query Cust_KaKSealImages CCK need to be used in the place CD.MetaTypeName but here in Cust_KaKSealImages(imageTypeID ) i am getting values of 447,448,448,449 no direct nameP.V.P.MOhan |
|
|
Next Page
|
|
|
|
|