2 comma seperated list - SQL Server

razza_b
razza_b used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Nakul VachhrajaniTechnical Architect, Capgemini India

Commented:
Is there a particular reason why you need to pass comma-separated strings? Can you explore using table-valued parameters to your procedure?
Business Intelligence Developer and Analyst
Top Expert 2015
Commented:
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.

Author

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.

:)
Ensure you’re charging the right price for your IT

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

Arifhusen AnsariBusiness Intelligence Developer and Analyst
Top Expert 2015

Commented:
Please refer the logic i have posted.

It has the logic to combine two column with hyphen.

Thanks

Author

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

Thanks :)
Arifhusen AnsariBusiness Intelligence Developer and Analyst
Top Expert 2015
Commented:
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

Author

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?

Author

Commented:
Its ok I got it...very nice...

Author

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

Author

Commented:
thank you!!
Máté FarkasDatabase Developer and Administrator

Commented:
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

Author

Commented:
WOW now that's a very nice way to do it....I like it!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial