In order to parse an array passed in as a parameter in a stored procedure I use a stored procedure ripped from here: http://vyaskn.tripod.com/
To be more precise, it is method 5:
SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
(
SELECT LTRIM(RTRIM(SUBSTRING(Orde
FROM
(
SELECT ',' + @OrderList + ',' AS OrderID
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.OrderID)
WHERE SUBSTRING(OrderID, number, 1) = ','
) as Derived
ON o.OrderID = Derived.OrderID
I use this sp to insert records in a table and instead of one column per insert (row) I need to do more.
For example, my input string would be something like:
col1_val1,col2_val1,col3_v
However, I can't get my head around how to build this query. I have tried a cross join to multiple Numbers table but that generates a cartesian product (I cannot get a distinct-like result).
Is there any way around it?
Thanks
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Fair enough, but as far as I can see the idea is to use the pivot table instead of the expensive string parsing (such as the specified UDF does) to optimize the whole process.
How is the proposed method any better than just parsing the string (using a while and the substring) and doing the inserts in the while loop?
do you reckon there is no way it can be done just using the join to the numbers table (so no loop around to parse the string)?
Hi,
Just try: (Hope that it is some thing more better)
Declare @OrderList varchar(1000)
SET @OrderList ='10248,3,5,10259,3,4'
SELECT Distinct o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
(
SELECT LTRIM(RTRIM(SUBSTRING(Orde
(LEN(SUBSTRING(OrderID,1,n
LEN(REPLACE(SUBSTRING(Orde
LEN(',') as Num
FROM
(
SELECT ',' + @OrderList + ',' AS OrderID
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.OrderID)
WHERE SUBSTRING(OrderID, number, 1) = ','
) as Derived
ON o.OrderID = CASE WHEN Derived.Num % 3 = 1 THEN Derived.OrderID END
JOIN
(
SELECT LTRIM(RTRIM(SUBSTRING(Orde
(LEN(SUBSTRING(OrderID,1,n
LEN(REPLACE(SUBSTRING(Orde
LEN(',') as Num
FROM
(
SELECT ',' + @OrderList + ',' AS OrderID
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.OrderID)
WHERE SUBSTRING(OrderID, number, 1) = ','
) as Derived1
ON o.ShipVia = CASE WHEN Derived1.Num % 3 = 2 THEN Derived1.OrderID END
JOIN
(
SELECT LTRIM(RTRIM(SUBSTRING(Orde
(LEN(SUBSTRING(OrderID,1,n
LEN(REPLACE(SUBSTRING(Orde
LEN(',') as Num
FROM
(
SELECT ',' + @OrderList + ',' AS OrderID
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.OrderID)
WHERE SUBSTRING(OrderID, number, 1) = ','
) as Derived2
ON o.EmployeeID = CASE WHEN Derived2.Num % 3 = 0 THEN Derived2.OrderID END
Hi,
Using temp table:
Declare @OrderList varchar(1000)
SET @OrderList ='10248,3,5,10259,3,4'
SELECT LTRIM(RTRIM(SUBSTRING(Orde
(LEN(SUBSTRING(OrderID,1,n
LEN(REPLACE(SUBSTRING(Orde
LEN(',') as Num
INTO #tmp
FROM
(
SELECT ',' + @OrderList + ',' AS OrderID
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.OrderID)
WHERE SUBSTRING(OrderID, number, 1) = ','
SELECT Distinct o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
#tmp as Derived
ON o.OrderID = CASE WHEN Derived.Num % 3 = 1 THEN Derived.OrderID END
JOIN
#tmp as Derived1
ON o.ShipVia = CASE WHEN Derived1.Num % 3 = 2 THEN Derived1.OrderID END
JOIN
#tmp Derived2
ON o.EmployeeID = CASE WHEN Derived2.Num % 3 = 0 THEN Derived2.OrderID END
Hi,
I have taken Count String Occurrence Function from
http://www.sql-server-help
Business Accounts
Answer for Membership
by: Rajesh_mjPosted on 2007-11-14 at 02:59:41ID: 20278914
Hi,
First create dbo.SplitOrderIDs specified in the method 6
http://vyaskn.tripod.com/
Then just try this:
(I have used orderid,ShipVia,EmployeeID
declare @OrderList varchar(1000),@Col1 varchar(1000),@Col2 varchar(1000),@Col3 varchar(1000)
SET @OrderList ='10248,3,5,10259,3,4'
drop table #newtable
Select IDENTITY(int, 1,1) AS ID_Num, OrderID INTO #NewTable from dbo.SplitOrderIDs(@OrderLi
select * from #NewTable
Select @col1 = '',@col2 = '',@Col3 =''
select @Col1 =@Col1+cast(OrderID as varchar(10))+',' from #NewTable where Id_Num % 3 = 1
select @Col2 =@Col2+cast(OrderID as varchar(10))+',' from #NewTable where Id_Num % 3 = 2
select @Col3 =@Col3+cast(OrderID as varchar(10))+',' from #NewTable where Id_Num % 3 = 0
SELECT Distinct o.OrderID, CustomerID, EmployeeID, OrderDate
FROM dbo.Orders AS o
JOIN
(
SELECT LTRIM(RTRIM(SUBSTRING(Colu
FROM
(
SELECT ',' + @col1 + ',' AS Column1
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.Column1)
WHERE SUBSTRING(Column1, number, 1) = ','
) as Derived1
ON o.OrderID = Derived1.Column1
JOIN
(
SELECT LTRIM(RTRIM(SUBSTRING(Colu
FROM
(
SELECT ',' + @col2 + ',' AS Column2
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.Column2)
WHERE SUBSTRING(Column2, number, 1) = ','
) as Derived2
ON o.ShipVia = Derived2.Column2
JOIN
(
SELECT LTRIM(RTRIM(SUBSTRING(Colu
FROM
(
SELECT ',' + @col3 + ',' AS Column3
) AS InnerQuery
JOIN
Numbers n
ON
n.Number < LEN(InnerQuery.Column3)
WHERE SUBSTRING(Column3, number, 1) = ','
) as Derived3
ON o.EmployeeID = Derived3.Column3