Any way to do this SELECT ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Any way to do this SELECT ?

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2009-08-03 : 05:53:36

I have a table called Products
this table has ProdID int, ProdName varchar(100), ProdAccessories varchar(1000)
ProdAccessories is comma separated values

ProdID | ProdName | ProdAccessories
1 | Laptop | USB,WebCam,Mouse
2 | Mobile | Case,BlueTooth
3 | TV | DVDPlayer,Antenna

I have a function that returns a table for comma separated value called dbo.f_Split() this can be used silmilar to this
select dbo.f_Split('abcd,efgh,ijkl')
will return a table result
abcd
efgh
ijkl
now what i want is to get ProdName and ProdAccessories in 1 table and list all ProdAccessories
something like this

ProdName | ProdAccessories
Laptop | USB
Laptop | WebCam
Laptop | Mouse
Mobile | Case
Mobile | BlueTooth
TV | DVDPlayer
TV | Antenna
is this doable without cursor?!! and how?

Thanks for your help

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-03 : 05:58:43

[code]
declare @str1 table(productname varchar(max), prtaccess varchar(max))
insert into @str1 select 'Laptop', 'USB,WebCam,Mouse'

SELECT productname,
replace(SUBSTRING(prtaccess,charindex(',',prtaccess,v.number),abs(charindex(',',prtaccess,charindex(',',prtaccess,v.number)+1)-charindex(',',prtaccess,v.number))),',','')as value
FROM @str1 s
inner join master..spt_values AS v ON v.Type = 'P'
AND v.number > 0
AND v.number <= len(prtaccess)
AND substring(',' + prtaccess, v.number, 1) = ','
[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-03 : 05:59:53

use CROSS APPLY


select *
from   Products p
       cross apply dbo.f_Split(p.ProdAccessories)

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-03 : 06:00:29

use fnParseString from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-03 : 06:08:13

Try this too



Declare @Temp Table (ProdID INT, ProdName varchar(32), ProdAccessories varchar(255))
insert into @temp select 1 , 'Laptop' , 'USB,WebCam,Mouse'
insert into @temp select 2 , 'Mobile' , 'Case,BlueTooth'
insert into @temp select 3 , 'TV' , 'DVDPlayer,Antenna'

;WITH csvtbl(i, j,ProdName,ProdAccessories)
AS
(
	SELECT 0, j = CHARINDEX(',', ProdAccessories+','),prodname,prodaccessories FROM @temp
	UNION ALL
	SELECT CAST(j + 1 AS INT), j = CHARINDEX(',', ProdAccessories+',', j + 1) ,prodname,prodaccessories
	FROM csvtbl
	WHERE CHARINDEX(',', ProdAccessories+',', j + 1) <> 0
)

SELECT ProdName,SUBSTRING(ProdAccessories+',', C.i, C.j-i) AS 'ProdAccessories' FROM csvtbl C
ORDER BY PRODNAME

nice123ej
Starting Member

48 Posts

Posted - 2009-08-03 : 06:09:42

Thanks guys
it is working
thanks khtan u r the best

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources