• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 61
  • Last Modified:

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
0
razza_b
Asked:
razza_b
2 Solutions
 
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 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
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Arifhusen AnsariBusiness Intelligence 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 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now