Solved

2 comma seperated list - SQL Server

Posted on 2016-09-22
12
45 Views
Last Modified: 2016-09-23
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
Comment
Question by:razza_b
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 13

Expert Comment

by:Nakul Vachhrajani
ID: 41811981
Is there a particular reason why you need to pass comma-separated strings? Can you explore using table-valued parameters to your procedure?
0
 
LVL 12

Accepted Solution

by:
Arifhusen Ansari earned 500 total points
ID: 41812008
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
 
LVL 1

Author Comment

by:razza_b
ID: 41812025
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 12

Expert Comment

by:Arifhusen Ansari
ID: 41812032
Please refer the logic i have posted.

It has the logic to combine two column with hyphen.

Thanks
0
 
LVL 1

Author Comment

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

Thanks :)
0
 
LVL 12

Assisted Solution

by:Arifhusen Ansari
Arifhusen Ansari earned 500 total points
ID: 41812045
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
 
LVL 1

Author Comment

by:razza_b
ID: 41812049
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
 
LVL 1

Author Comment

by:razza_b
ID: 41812054
Its ok I got it...very nice...
0
 
LVL 1

Author Comment

by:razza_b
ID: 41812057
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
 
LVL 1

Author Closing Comment

by:razza_b
ID: 41812058
thank you!!
0
 
LVL 12

Expert Comment

by:Máté Farkas
ID: 41812071
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
 
LVL 1

Author Comment

by:razza_b
ID: 41812104
WOW now that's a very nice way to do it....I like it!!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question