how to cut the data with based on comma

Question

Post reply

how to cut the data with based on comma

dastagirid

SSCommitted

Points: 1810
More actions
September 9, 2009 at 4:14 am

#385328

i have a table like
this is users table
id name productIds
-------------------------------------------
1 giri 1,2,3
2 nani 1,2
this is product table
-------------------------
productIds productname
----------------------------------
1 a
2 b
3 c
i need query for bellow output:
id name productIds
-------------------------------------------
1 giri a,b,c
2 nani a,b
how can i get this type of out put by cutting the productIDs based on comma and display it ..I need a lot of this logic help me
Thanks
Dastagiri.D

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

There are a few ways , try this

drop table #t1

go

create table #t1

(

RowId integer,

ValueStr nvarchar(50)

)

go

insert into #t1 values(1,'1, 2 , 3,4')

insert into #t1 values(2,'4')

insert into #t1 values(3,'25,26,27,28,29,30,31,32')

insert into #t1 values(4,'2 ')

go

Drop function CutCsv

go

Create function CutCsv(@CSVString varchar(max))

returns table

as

return(

with Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),

Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),

Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),

Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),

Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),

SpacesPos(sIndex)

as

(

Select n+1

from nums

where n spacesPos.SIndex)-1

from spacesPos

)

Select Item = ltrim(rtrim(substring(@CSVString,StartPos,(EndPos-StartPos))))

from cteSpaceDelta

where EndPos is not null

)

go

Select RowId from #t1 cross apply dbo.CutCsv(ValueStr) ValuesOut

where ValuesOut.Item = '2'

go

Clear Sky SQL
My Blog[/url]

mcha7628 SSC Veteran Points: 265 More actions · Answer 2

This worked out well:

drop table #t1

go

create table #t1

(

RowId integer,

ValueStr nvarchar(50)

)

go

insert into #t1 values(1,'1,2,3')

insert into #t1 values(2,'2')

insert into #t1 values(3,'2,3')

insert into #t1 values(4,'2')

drop table #t2

go

create table #t2

(ID int,

Alpha varchar(1))

go

insert into #t2 values(1,'a')

insert into #t2 values(2,'b')

insert into #t2 values(3,'c')

SELECT #t1.RowID, #t2.Alpha

FROM #t1, #t2

WHERE ',' + #t1.ValueStr + ',' LIKE '%,' + cast(#t2.ID as nvarchar) + ',%'

order by #t1.RowID

I modified an example from this site:

http://www.projectdmx.com/tsql/sqlarrays.aspx

Sorry, I missed the point

mcha7628 SSC Veteran Points: 265 More actions · Answer 3

I fixed my previous query:

drop table #t1

go

create table #t1

(

RowId integer,

SomeName nvarchar(6),

ValueStr nvarchar(50)

)

go

insert into #t1 values(1,'giri','1,2,3')

insert into #t1 values(2,'nani','1,2')

drop table #t2

go

create table #t2

(ID int,

Alpha varchar(1))

go

insert into #t2 values(1,'a')

insert into #t2 values(2,'b')

insert into #t2 values(3,'c')

DROP TABLE #temp

SELECT #t1.RowID,#t1.SomeName, #t2.Alpha,

RANK() OVER (partition by rowid, #t1.SomeName ORDER BY alpha) AS RANKING

into #temp

FROM #t1, #t2

WHERE ',' + #t1.ValueStr + ',' LIKE '%,' + cast(#t2.ID as nvarchar) + ',%'

order by #t1.RowID

SELECT RowID, SomeName, replace(rtrim([1] + ' '+isnull([2],'')+' '+isnull([3],'')),' ',',') as ValueStr

FROM #temp

PIVOT

(

MAX(alpha)

FOR ranking IN ([1],[2],[3])

)

AS Alias

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 4

dastagirid (9/9/2009)
i have a table like
this is users table
id name productIds
-------------------------------------------
1 giri 1,2,3
2 nani 1,2
this is product table
-------------------------
productIds productname
----------------------------------
1 a
2 b
3 c
i need query for bellow output:
id name productIds
-------------------------------------------
1 giri a,b,c
2 nani a,b
how can i get this type of out put by cutting the productIDs based on comma and display it ..I need a lot of this logic help me

Now that you have some viable answers to try to fix the stated problem, my suggestion would be to fix the real problem... have the powers that be normalize the data in the tables so that you don't need to use such convoluted solutions in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dastagirid SSCommitted Points: 1810 More actions · Answer 5

hi thanks for sending this...

I fixed that problem

Thanks
Dastagiri.D