CODEWITHSUNDEEP
sqlsql-server-2008split
Munies
Munies

Reputation: 19

Splitting comma in sql

I have one table like

select SupplierID,ProductIDs from T_ProductSupplierTable

the output is

SupplierID  ProductIDs
1             1,2,3
2             2,3,4
3             1,5,2

i need the answer like 

SupplierID  ProductIDs
1           1
1           2
1           3
2           2
2           3
2           4
3           1
3           5
3           2

how to generate the query...?

Upvotes: 0

Views: 46

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Assuming you have a table of product ids, there is a way to do this using ANSI standard SQL which will work in a couple of databases (and come close to working in more):

select s.SupplierId, p.ProductId
from T_ProductSupplierTable s join
     Products p
     on concat(',', s.productIds, ',') like concat('%,', p.ProductId, ',%');

(What varies between databases is the concat() function.)

In any case, I don't actually recommend this approach because most databases have other approaches that are likely to be faster.

EDIT:

In SQL Server:

select s.SupplierId, p.ProductId
from T_ProductSupplierTable s join
     Products p
     on ',' + s.productIds + ',') like '%,' + cast(p.ProductId as varchar(255)) + ',%';

Upvotes: 1

serges_newj
serges_newj

Reputation: 815

Also you can use recursive cte for this. Sample

with cte as (
  select SupplierID, ProductIDs as IDs, cast(null as varchar(8000)) as Product, charindex(',',ProductIDs) as nxt
  from tab
  union all
  select SupplierID, substring(IDs,nxt+1,8000), left(IDs,nxt-1), charindex(',',IDs,nxt+1)-nxt
  from cte where nxt > 0
  union all
  select SupplierID, null, IDs, null
  from cte where nxt <= 0
)
select supplierid, Product from cte where Product is not null

Upvotes: 0

dcieslak
dcieslak

Reputation: 2715

You can use some function ready to use from Internet to split your string and convert to table that later you can join to SubpplierID.

The other option without using function is here Sample in SQL Fiddle

SELECT supplierid,
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS ProductIDs 
FROM
(
SELECT supplierid,CAST('<XMLRoot><RowData>' + REPLACE(ProductIDs ,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
FROM   tab
)t
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)

Upvotes: 0

Related Questions