SQL query to have multiple values seperated by comma in 1 db column to join another table and get the values in a new column seperated by comma.

Question

0.00/5 (No votes)

See more:

Hi All,

I have a table A which has a column with some codes sperated by comma in the column data like below

Centercodenumbers
1,2,3,4

Another Table B with columns and values

CenterCode CenterName
1 --------------- India
2 ---------------- US
3 ---------------- Canada
4 ---------------- ..
5 ----------------- ..
.. ---------------- ..
50 ---------------- IreLand

Now I wanted to join the two tables and get another new column with the data like below

NewCenterValues
India, US, Canada

This had the code values above
Centercodenumbers
1,2,3,4

Can anyone please help..

I have done it using C# to display values like above in the front end using SPLIT(',')

But in this requirement i need to run this select statement on sql and get these values as a db result..

Help needed..!!

Posted 11-Mar-14 21:31pm

Sanman Marathe

Updated 11-Mar-14 21:33pm

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Accepted Answer · 2014-03-11T21:45:00

Solution 1

SQL

select stuff ((select ',' +cast( CenterName as nvarchar(max)) From tableb where centerCode in(1,2,3) for xml path ('')),1,1,'')

and learn xml path

Posted 11-Mar-14 21:45pm

King Fisher

Comments

Sanman Marathe 12-Mar-14 4:09am

Hi,Thanks man for the stuff funtion..!!It really works..

But now i have multiple records in the db like we have passed the parameters (1,2,3).

I have multiple rows with values like (1,2,3) next is (22,34,15) and (45,33,21) and so on..!!

How can i pass those in the above query,,

M aslo getting error "Conversion failed when converting the varchar value ' 10, 4' to data type int." i think which means it is expecting only 1 set of parameter values seprated by comma, but i am passing the values with multiple rows...

How can i pass those in the above query,,Plz help..!!

Sanman Marathe 12-Mar-14 4:34am

Hi,

I actually worte this query below.. I also got error saying substring returned more than 1 value..

declare @parameter int

set @parameter = (Select MedicalCenters from PreClaimRequestor where ClientID = '1300')

select stuff ((select ',' +cast( KaiserClientName as nvarchar(max)) From Kaiserclient where KaiserClientID in(@parameter) for xml path ('')),1,1,'')

King Fisher 12-Mar-14 4:38am

just run this fuction
create FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(

Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END

King Fisher 12-Mar-14 4:41am

declare the parameter as nvarchar(amx)
select stuff ((select ',' +cast( KaiserClientName as nvarchar(max)) From Kaiserclient where KaiserClientID in(select value from dbo.Split(@parameter,',')) for xml path ('')),1,1,'')

Sanman Marathe 12-Mar-14 5:40am

Hi Sir,

I ran the Split function
And the updated query, it still says sunstring returned more than 1 value..

IF i substring as top 1, output comes... but i need all the records..

M googling issue meanwhile ..

Any ideas more sir??

--Sanman

King Fisher 12-Mar-14 6:06am

what is your query explain it clearly

Sanman Marathe 12-Mar-14 6:44am

I got it solved...

I ran the stuff query once and took centercode data result into and excel
and created and multiple stuff query in excel and that way i got 484 lines of "select stuff" query with the needed results output..

A very bad way todo it finally got it :D !!

King Fisher 12-Mar-14 6:58am

if i am right then accept the solution.it may help others

Sanman Marathe 12-Mar-14 7:19am

Sure

King Fisher 12-Mar-14 7:43am

;)