Create a table from CSV data - 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)

Create a table from CSV data

Author

Topic

garethrichardadams
Starting Member

2 Posts

Posted - 2008-08-13 : 09:17:37

Hi all,

I've got a table with a persons details (id, firstname, lastname, email) and a comma separated list of the products they own (e.g. tv,dvd player, vcr).

I want a function that returns a new table with the persons id and a product, with one row for each product that they own.

E.g.

Original table row:

id| FName | SName | Email | Products
1 | Gareth | Adams | gareth.adams@spam.com | dvd,tv,vcr

New table:
pid| product
1 | dvd
1 | tv
1 | vcr

I can easily do this for a specific row using a function:

select * from dbo.fn_GetProductTable(1) (where 1 is the id of the person)

but I can't call this for all rows because the following is failing:

select * from subscriptions
inner join (select * from dbo.fn_GetProductTable(id)) on pid = id

Does anyone have any suggestions?

Thanks

Gareth

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-13 : 09:45:57

make use of either one of the function here and CROSS APPLY
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830&SearchTerms=CSVTable
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 09:47:59

use this function:-

CREATE FUNCTION ParseValues  
(@String varchar(8000)  
)  
RETURNS @RESULTS TABLE  
(ID int identity(1,1),  
Val int  
)  
AS  
BEGIN  
DECLARE @Value varchar(100)  
  
WHILE @String is not null  
BEGIN  
SELECT @Value=CASE WHEN CHARINDEX(',',@String) >0 THEN LEFT(@String,CHARINDEX(',',@String)-1) ELSE @String END,  
@String=CASE WHEN CHARINDEX(',',@String) >0 THEN SUBSTRING(@String,CHARINDEX(',',@String)+1,LEN(@String)) ELSE NULL END  
INSERT INTO @RESULTS (Val)   
SELECT @Value   
END  
RETURN  
END

and apply like this

SELECT t.id,b.Val
FROM YourTable t
CROSS APPLY dbo.ParseValues(t.Products) b

garethrichardadams
Starting Member

2 Posts

Posted - 2008-08-13 : 10:24:19

Works perfectly!!! Thanks so much!

(And thanks to Microsoft for creating such a useful operator!)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources