Display rows based on column value in sql server 2008

Question

i have column col1 and col2

Col1             col2
abc,def          xyz,xyz
abc1,def1        xyz1,xyz1

i need output as below

Col1             col2
abc              xyz,xyz
def              xyz,xyz
abc1             xyz1,xyz1
def1             xyz1,xyz1

that is if col1 contains 2 value (abc,def) and col2 contains 2 value(xyz,xyz) then i need 4 rows. likewise col1 and col2 contains 2 values then i need 9 rows.

please help me to get the output in sql server

Please rewrite the question. The title says one thing, the text another. What is clear is that the table breaks even the 1st Normal Form - storing multiple values in the same cell. Just don't do this. The reason is that it's impossible to search for such values without checking the *entire* table — Panagiotis Kanavos, Mar 03 '17 at 15:40
Read [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutly yes!** — Zohar Peled, Mar 03 '17 at 15:43
Not sure why somebody downvoted this question. It is clear what the OP desires. It is also clear that the table structure is the root of the problem. But in my opinion this question contains all of the information required to answer it, as such the question is a well written one. It deserves a +1 from me. — Sean Lange, Mar 03 '17 at 15:59
OP - If you are deadset on using the horrible design you will need to deal with the knowledge that your queries are going to suffer horrible performance for the life of this design. But you can quite easily retrieve your information in the format you are asking by using a string splitter. Here is a great article with a number of such splitters. http://sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange, Mar 03 '17 at 16:01

score 2 · Accepted Answer · answered Mar 03 '17 at 16:02

A little XML and and a CROSS APPLY

Option 1: Without a Split/Parse Function

Declare @YourTable table (Col1 varchar(25),col2 varchar(25))
Insert Into @YourTable values
('abc,def','xyz,xyz'),
('abc1,def1','xyz1,xyz1')

Select col1 = B.RetVal
      ,col2 = A.col2
 From @YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(A.Col1,',','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
                Cross Apply x.nodes('x') AS B(i)
             ) B

Returns

col1    col2
abc     xyz,xyz
def     xyz,xyz
abc1    xyz1,xyz1
def1    xyz1,xyz1

Option 2: with a Split/Parse Function

Select col1 = B.RetVal
      ,col2 = A.col2
 From @YourTable A
 Cross Apply [dbo].[udf-Str-Parse](A.col1,',') B

The UDF if interested

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (  
    Select RetSeq = Row_Number() over (Order By (Select null))
          ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
    From  (Select x = Cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as X
    Cross Apply x.nodes('x') AS B(i)
);
--Thanks Shnugo for making this XML safe
--Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--Select * from [dbo].[udf-Str-Parse]('this,is,<test>,for,< & >',',')

score 0 · Answer 2 · answered Mar 03 '17 at 16:36

Here is another example:

;WITH A(Col1,col2)AS(
    SELECT 'abc,def','xyz,xyz' UNION all
    SELECT 'abc1,def1','xyz1,xyz1'
)
SELECT d.n,a.col2 FROM a
CROSS APPLY(VALUES(CONVERT(XML,'<n>'+REPLACE(col1,',','</n><n>')+'</n>'))) c(x)
CROSS APPLY(SELECT y.n.value('.','varchar(10)') FROM c.x.nodes('n') y(n)) d(n)

n          col2
---------- ---------
abc        xyz,xyz
def        xyz,xyz
abc1       xyz1,xyz1
def1       xyz1,xyz1

Display rows based on column value in sql server 2008

2 Answers2