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 split this comma seperated and show as rows
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  01:57:00  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 split those comma seperated
and for every MetaTypeID MetaTypeName as a row as showed in desired 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 MetaTypeID  MetaTypeName
1001   2      Mohan   1       Driving License 
1001   2      Mohan   2       Passport
1001   2      Mohan   3       AadharCard
1001   2      Mohan   4	    EducationalProof
1001   2      Mohan   5       ResidentialProof
1002   3        ramu   1       Driving License 
1002   3        ramu   2       Passport 
1002   3        ramu   3       AadharCard


P.V.P.MOhan

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/19/2013 :  02:27:35  Show Profile  Reply with Quote
SELECT e.*,Metavaluedescription, v.MetaID
FROM CustomerMeta m
JOIN Employee1 e
ON e.CustID = m.CustID
JOIN Metavalues v
ON  ',' + m.MetaTypeName + ',' LIKE '%,' + v.Metavaluedescription + ',%'


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/19/2013 :  02:36:21  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

quesenberry
Starting Member

USA
1 Posts

Posted - 04/19/2013 :  02:36:45  Show Profile  Reply with Quote

unspammed

Edited by - quesenberry on 04/19/2013 02:37:07
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  02:47:11  Show Profile  Reply with Quote

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 + ',%' 


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	ImageTypeID
010000715	4	chaitanya	Chinthapalli	454
010000026	2	chaitanya	Kishore	447,448,451,453


how to split this one

P.V.P.MOhan

Edited by - mohan123 on 04/19/2013 02:47:51
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/19/2013 :  02:50:10  Show Profile  Reply with Quote
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 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:50:48  Show Profile  Reply with Quote
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 + ',%' 
)m
PIVOT (COUNT(Metavaluedescription) FOR Metavaluedescription IN ([DrivingLicense],[Passport],[AadharCard],[EducationalProof],[ResidentialProof]))p



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

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  02:52:58  Show Profile  Reply with Quote
Hi visakh it is not splitting comma seperated values just showing the plain result


ProfileID	Cust_ID	FirstNAME	Lastname	ImageTypeID
010000715	4	chaitanya	Chinthapalli	454
010000026	2	chaitanya	Kishore	447,448,451,453



have to split and use this one in pivot table

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

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/19/2013 :  02:53:00  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/19/2013 :  02:55:33  Show Profile  Reply with Quote
>> reply for (Mohan123 Posted - 04/19/2013 : 02:52:58)
You have to select MetaId from Master table, but not the MetaTypeID

--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  03:10:15  Show Profile  Reply with Quote
okay i wll explain you clearly this post and yesterday is complete i will explain you in full detail

P.V.P.MOhan

Edited by - mohan123 on 04/19/2013 03:10:58
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  03:30:23  Show Profile  Reply with Quote
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

CL.ProfileID,
CD.Cust_ID,
CB.FirstName  As FirstNAME,
CB.LastName As Lastname



						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	ImageTypeID
010000026	2	chaitanya	Kishore	447,448,451,453
010000026	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       	Passport
451       	AadharCard
453	    	EducationalProof	
455       	ResidentialProof


So these rows to be refered as columns along with the joined columns like profile,custid,name


Atlast using pivot and any suggested query my out put should look like this one


ProfileID CustID  FirstName  DrivingLicense  Passport AadharCard EducationalProof ResidentialProof
1001   2		Mohan   		YES				NO		YES			NO					NO
1002   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.*,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



how to put my added columns as above mentioned query ..suggest me

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

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/19/2013 :  03:35:14  Show Profile  Reply with Quote
FROM
(
SELECT e.*, Here add your columns, Metavaluedescription
FROM CustomerMeta m
JOIN Employee1 e
ON e.CustID = m.CustID
JOIN Metavalues v
ON ',' + m.MetaTypeName + ',' LIKE '%,' + v.Metavaluedescription + ',%'
)m


--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  03:39:41  Show Profile  Reply with Quote
hi chandu
this 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 this

ProfileID	Cust_ID	FirstNAME	Lastname	ImageTypeID
010000026	2	chaitanya	Kishore	447,448,451,453
010000026	2	chaitanya	Kishore	447,448,451,453



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

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/19/2013 :  04:44:29  Show Profile  Reply with Quote
--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.Metavaluedescription
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 AND 
    ',' + CD.MetaTypeName + ',' LIKE '%,' + MMV.Metavaluedescription + ',%' 
)m
PIVOT (COUNT(Metavaluedescription) FOR Metavaluedescription IN ([DrivingLicense],[Passport],[AadharCard],[EducationalProof],[ResidentialProof]))p


NOTE: Refer your respective column instead of CD.MetaTypeName
--
Chandu

Edited by - bandi on 04/19/2013 04:46:31
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/19/2013 :  04:51:53  Show Profile  Reply with Quote
If the above is not working , then try with the following LEFT JOIN part
LEFT JOIN Mst_Meta_Values MMV
ON CKK.KaKSealImage_ID = MMV.Meta_ID AND
(',' + MMV.Meta_ID + ',' LIKE '%,' + CKK.ImageTypeID + ',%')


--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  05:50:46  Show Profile  Reply with Quote
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 this

ProfileID	Cust_ID	FirstNAME	Lastname	10th Memo	DrivingLicense	PanCard	Passport	VoteID	AadharCard	AppointmentLetter	EducationalProof	ResidentialProof
010000026	2	chaitanya	Kishore	NO	NO	NO	NO	NO	NO	NO	NO	NO
010000715	4	chaitanya	Chinthapalli	NO	NO	NO	NO	NO	NO	NO	NO	NO


everything is coming as NO there is no sign of YES

suggest me

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

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/19/2013 :  06:06:23  Show Profile  Reply with Quote
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 MMV
ON (',' + CD.MetaTypeName + ',' LIKE '%,' + MMV.Metavaluedescription + ',%')

--
Chandu

Edited by - bandi on 04/19/2013 06:08:58
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  06:10:24  Show Profile  Reply with Quote
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 this

ProfileID	Cust_ID	FirstNAME	Lastname	10th Memo	DrivingLicense	PanCard	Passport	VoteID	AadharCard	AppointmentLetter	EducationalProof	ResidentialProof
010000026	2	chaitanya	Kishore	NO	NO	NO	NO	NO	NO	NO	NO	NO
010000715	4	chaitanya	Chinthapalli	NO	NO	NO	NO	NO	NO	NO	NO	NO


everything is coming as NO there is no sign of YES

suggest me

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

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 04/19/2013 :  06:15:22  Show Profile  Reply with Quote
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

Edited by - bandi on 04/19/2013 07:00:35
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/19/2013 :  06:35:16  Show Profile  Reply with Quote
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 name

P.V.P.MOhan
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.19 seconds. Powered By: Snitz Forums 2000