How to split this comma seperated and show as rows

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to split this comma seperated and show as rows

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 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 02:27:35

[code]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 + ',%'[/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 MVP
http://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	ImageTypeID
010000715	4	chaitanya	Chinthapalli	454
010000026	2	chaitanya	Kishore	447,448,451,453

how to split this one

P.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 MVP
http://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 + ',%' 
)m
PIVOT (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	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

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 MVP
http://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 detail

P.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	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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-19 : 03:35:14

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

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-19 : 03:39:41

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

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.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

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 part
LEFT 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 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

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 MMV
ON (',' + 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 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

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 name

P.V.P.MOhan

Previous Page&nsp; Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources