Solved

2 comma seperated list - SQL Server

Posted on 2016-09-22
12
32 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
12 Comments
 
LVL 11

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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 11

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PL/SQL query 14 46
C# SQL BULK INSERT CLASS 5 35
Sort by Month and Year - SQL 3 22
Sql query to Stored Procedure 6 16
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now