2 comma seperated list - SQL Server

Hello

I have a stored procedure in sql server 2012 which I pass in a comma separated value, loop it and update a table...all good.

but now I have 2 lists to handle now which they need to be paired in order, the lists contains....E.G

list 1 -SER123, SER456, SER789
list 2 - MOD123, MOD456, MOD789

The table for example will have 3 columns - Order, Serial, Module

the position of these 2 lists are paired (SER123- MOD123) so when I update the table they need to be in the same row.

See the attachment of how I do just now with one list.

Any help greatly appreciated.

comma-sep-SP.docx

Thanks
LVL 1
razza_bAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
Is there a particular reason why you need to pass comma-separated strings? Can you explore using table-valued parameters to your procedure?
0
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
To combine two list in the exact way

SER123- MOD123

You have to first find the way you can join them.

Fist Case: If you are sure that you will have the data in the list in the perfect order. means you can directly map the first value from the list1 to first value in list2 and so for the second and third and other values if you have.

use the below query. I have put a logic modify as per your need.

CREATE TABLE OrderLog
( SalesOrder INT,
Module VARCHAR(20),
Date DATETIME)

DECLARE
   @SalesOrder nvarchar(20),
   @ModuleList nvarchar(max)='MOD123, MOD456, MOD789',
   @SerList nvarchar(max)='SER123, SER456, SER789'
,
			 @xml AS XML,
			 @id INT, @maxid INT
   
			select @xml='<root><param>'+replace(@ModuleList,',','</param><param>')+'</param></root>'

			if object_id('tempdb.sys.#params') is not null
				drop table #params
			
			select 
				identity(int,1,1)			as [ID], 
				P.value('.','varchar(16)')	as [param]
			into #params
			from 
				@xml.nodes('/root/param') P(p)


--SELECT * FROM #params

				select @xml='<root><param>'+replace(@SerList,',','</param><param>')+'</param></root>'

			if object_id('tempdb.sys.#params') is not null
				drop table #params1
			
			select 
				identity(int,1,1)			as [ID], 
				P.value('.','varchar(16)')	as [param]
			into #params1
			from 
				@xml.nodes('/root/param') P(p)


if object_id('tempdb.sys.#FinalParam') is not null
				drop table #FinalParam
			

SELECT CONCAT(#params1.param,'-',#params.param) [param] ,#params1.ID as id

INTO #FinalParam
FROM #params 

INNER JOIN #params1
ON #params.ID=#params1.ID

SELECT * FROM #FinalParam




			SELECT 
				@id = 1, 
				@maxid = 3  --Put the hard coded value to execute the script

			
			WHILE @id <= @maxid
				BEGIN
				print @id
					DECLARE @MOD_ID VARCHAR(MAX)
					SELECT 
						@MOD_ID = [param]
					FROM 
						#FinalParam
					WHERE 
						id = @id

								INSERT INTO OrderLog (SalesOrder, Module, Date)
								VALUES (@SalesOrder, @MOD_ID, GETDATE())

						SELECT @id += 1
ENd
SELECT * FROM OrderLog
DROP TABLE OrderLog

Open in new window




Second Case: If you are not sure and you can not map the first row from list1 to first row in list2.

You have to define the exact way you can join two table.

From you data what i can assume is you want to map like SER123- MOD123

and SER456- MOD456. Means you can use the last three digit to make a join to other list to find the exact match.

If it's correct you can use following approach.

CREATE TABLE OrderLog
( SalesOrder INT,
Module VARCHAR(20),
Date DATETIME)

DECLARE
   @SalesOrder nvarchar(20),
   @ModuleList nvarchar(max)='MOD123, MOD456, MOD789',
   @SerList nvarchar(max)='SER123, SER456, SER789'
,
			 @xml AS XML,
			 @id INT, @maxid INT
   
			select @xml='<root><param>'+replace(@ModuleList,',','</param><param>')+'</param></root>'

			if object_id('tempdb.sys.#params') is not null
				drop table #params
			
			select 
				identity(int,1,1)			as [ID], 
				LTRIM(RTRIM(P.value('.','varchar(16)')))	as [param]
			into #params
			from 
				@xml.nodes('/root/param') P(p)


--SELECT * FROM #params

				select @xml='<root><param>'+replace(@SerList,',','</param><param>')+'</param></root>'

			if object_id('tempdb.sys.#params') is not null
				drop table #params1
			
			select 
				identity(int,1,1)			as [ID], 
				LTRIM(RTRIM(P.value('.','varchar(16)')))	as [param]
			into #params1
			from 
				@xml.nodes('/root/param') P(p)

--SELECT * FROM #params
--SELECT * FROM #params1

if object_id('tempdb.sys.#FinalParam') is not null
				drop table #FinalParam
			

SELECT CONCAT(#params1.param,'-',#params.param) [param] ,#params1.ID as id

INTO #FinalParam
FROM #params 

INNER JOIN #params1
ON RIGHT(#params.[param],3)=RIGHT(#params1.[param],3)

--SELECT * FROM #FinalParam




			SELECT 
				@id = 1, 
				@maxid = 3  --Put the hard coded value to execute the script

			
			WHILE @id <= @maxid
				BEGIN
				print @id
					DECLARE @MOD_ID VARCHAR(MAX)
					SELECT 
						@MOD_ID = [param]
					FROM 
						#FinalParam
					WHERE 
						id = @id

								INSERT INTO OrderLog (SalesOrder, Module, Date)
								VALUES (@SalesOrder, @MOD_ID, GETDATE())

						SELECT @id += 1
ENd
SELECT * FROM OrderLog
DROP TABLE OrderLog

Open in new window


It's a logic please do the changes as per your need.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
razza_bAuthor Commented:
Thanks for reply guys.

Arif Ansari - The first case is nearly what I need except I cant have the 2 values concat in same column with hyphen.

The table will consist of Order, Serial, Module, Date and they need to be put into their specified column.

:)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
Please refer the logic i have posted.

It has the logic to combine two column with hyphen.

Thanks
0
razza_bAuthor Commented:
sorry Arif Ansari....not with the values concatenated into same column, need to be split apart into the respective column...

Thanks :)
0
Arifhusen AnsariBusiness Intelligence Developer and AnalystCommented:
means you want two different column like

SalesOrder   List1         List2           Date
1                     SER123    MOD123    2016-09-23 13:03:08.440


You can do so even

Use this query to insert data ni #FinalParam

SELECT #params1.param Service ,#params.param AS Module , #params.ID as id

INTO #FinalParam
FROM #params 

INNER JOIN #params1
ON RIGHT(#params.[param],3)=RIGHT(#params1.[param],3)

Open in new window


and use the column in final query
0
razza_bAuthor Commented:
Yes exactly like that...
SalesOrder   List1         List2           Date
 1                     SER123    MOD123    2016-09-23 13:03:08.440

sorry where and what do I replace to use this?
0
razza_bAuthor Commented:
Its ok I got it...very nice...
0
razza_bAuthor Commented:
SalesOrder      Serial      Module              Date
test1234              SER123      MOD123              2016-09-23 01:18:31.040
test1234        SER456      MOD456       2016-09-23 01:18:31.057
test1234              SER789      MOD789       2016-09-23 01:18:31.067
0
razza_bAuthor Commented:
thank you!!
0
Máté FarkasDatabase Developer and AdministratorCommented:
ALTER  PROCEDURE [dbo].[MyProc]
   @SalesOrder nvarchar(20),
   @SerialList nvarchar(max),
   @ModuleList nvarchar(max)
AS 
DECLARE @xml AS XML;
   
select @xml = '<p>' + replace(@SerialList, ',' ,'</p><p>') + '</p>';

SELECT IDENTITY(INT, 1, 1) AS [ID], 
       P.value('.','varchar(16)') AS [Param]
INTO #Serials
FROM @xml.nodes('p') P(p);


SELECT @xml = '<p>' + replace(@ModuleList, ',' ,'</p><p>') + '</p>';

SELECT IDENTITY(INT, 1, 1) AS [ID], 
       P.value('.','varchar(16)') AS [Param]
INTO #Modules
FROM @xml.nodes('p') P(p);


INSERT INTO OrderLog (SalesOrder, Serial, Module, Date)
SELECT @SalesOrder, s.Param, m.Param, GETDATE()
FROM #Serials s
INNER JOIN #Modules m ON s.ID = m.ID;

Open in new window

0
razza_bAuthor Commented:
WOW now that's a very nice way to do it....I like it!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.