Select SubQuery for Comma Separated Values from Single row

Question

From a Select Query I am getting following values in single row , not in different rows:

Id        Statuses
---     -------------
1       Released,In Progress,Completed

Now I want to write a query so that I should get all the orders that are in Statuses:

Select * from Orders where Status in (Select Statusesfrom StatusTable)

What you get from StatusTable is a single string, not a list of values. — Giorgos Betsos
– Giorgos Betsos, Commented Sep 22, 2016 at 15:50
upload some sample data from two tables.So the question is more clear. — M_K
– M_K, Commented Sep 22, 2016 at 15:52
Use dynamic sql or write a function to split those values and return in Single Column output — techspider
– techspider, Commented Sep 22, 2016 at 15:52
Any chance you could fix the problem instead of dealing with bad data? The problem is because you are violating 1NF by storing multiple values in a single column. Now you are struggling to get the data you want because of it. If at all possible fix the data and your queries will be simple. — Sean Lange
– Sean Lange, Commented Sep 22, 2016 at 16:24

S3S · Accepted Answer · 2016-09-22 15:57:03Z

1

Adopt one of these splitters. You can write your own but you will see a lot has gone into researching these:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

This splits your value into a table with a single column. You can join on this after you run the function on it like:

Select o.* from Orders o
inner join splitTable s on o.keyColumn = s.Column

answered Sep 22, 2016 at 15:57

S3S

25.2k5 gold badges29 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Cappelletti · Accepted Answer · 2016-09-23 00:44:46Z

After much discussion (as seen below), and testing, I modified my Parsing Function. The new function will parse 250,000 strings generating 1,889,108 rows in 400ms.

Declare @YourTable table (Id int,Statuses varchar(250))
Insert into @YourTable values 
(1,'Released,In Progress,Completed'),
(2,'Released,In Progress')

Select A.ID
      ,B.* 
from @YourTable A
Cross Apply (Select * from [dbo].[udf-Str-Parse-8K](A.Statuses,',')) B
Order By ID,Key_PS

Returns

ID  Key_PS  Key_Value   Key_Pos
1   1       Released    1
1   2       In Progress 10
1   3       Completed   22
2   1       Released    1
2   2       In Progress 10

The UDF if needed

CREATE FUNCTION [dbo].[udf-Str-Parse-8K](@String varchar(8000), @Delimiter varchar(50))
Returns Table 
As

--Usage: Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')
--       Select * from [dbo].[udf-Str-Parse-8K]('The quick brown fox',' ')

Return (
   with cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
        cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a, cte1 b, cte1 c, cte1 d) A ),
        cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
        cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

   Select Key_PS    = Row_Number() over (Order By A.N)
         ,Key_Value = Substring(@String, A.N, A.L) 
         ,Key_Pos   = A.N
   From   cte4 A
)

Just a couple of tweaks from http://www.sqlservercentral.com/articles/Tally+Table/72993/

This is a multi statement table valued function. They have been routinely known to be even worse than scalar functions on even moderate datasets. There are many inline table valued functions better than this. sqlperformance.com/2012/07/t-sql-queries/split-strings
@SeanLange Yes you are correct, but the performance is still respectable. 30,000 records 145ms.
I know it is one your wrote and it is quite good but a proper inline function is right at your fingertips...why keep posting one that is not as fast?
@SeanLange 10 runs each (same 30K as mentioned above). Your XML version is an average of 24ms faster. The only thing is often require the row number. So from now on, I will be using one or the other depending on my needs. Thanks for the link and the nudge.
Certainly not my XML version but glad you did some testing. The time you really start to notice the difference is when you have a million rows or you have a larger number of elements per row. When either, or both, of those start happening the performance differences really start to show themselves. On small datasets with only a few elements per row the difference is negligible.

Unnikrishnan R · Accepted Answer · 2016-09-22 16:33:43Z

0

Use STRING_SPLIT() function , if you are using SQL Server 2016.

     SELECT *
     FROM Orders 
      WHERE Status IN (SELECT Value FROM  StatusTable
                         CROSS APPLY STRING_SPLIT(Statuses,','))

answered Sep 22, 2016 at 16:33

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

Collectives™ on Stack Overflow

Select SubQuery for Comma Separated Values from Single row

3 Answers 3

Comments

12 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

12 Comments

Comments

Related