delimiter column into multiple rows

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 2008 Forums

Transact-SQL (2008)

delimiter column into multiple rows

Author

Topic

tracmonali
Starting Member

16 Posts

Posted - 2012-04-18 : 07:57:03

Hello,

I have a table with one column having few rows of data with delimiter "comma". I wish to look for this info and then split this column into 2/3 rows (as required). In short 1NF temp table will be created.

Example:

createdate policy# empid accessdate
10/10/2010 123,456,789 Id123 10/20/2012
4/4/2004 963 ID678 9/9/2009

The policy# col has data with comma for 1 row and 2nd row is good. I wish to split the 1st record into 3 rows.

createdate policy# empid accessdate
10/10/2010 123 Id123 10/20/2012
10/10/2010 456 Id123 10/20/2012
10/10/2010 789 Id123 10/20/2012
4/4/2004 963 ID678 9/9/2009

ANy help appreciated

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-18 : 11:22:25

you could try this


declare @tracmonali table(createdate datetime, policy# varchar(max), empid varchar(255), accessdate datetime)

INSERT INTO @tracmonali
SELECT '10/10/2010', '123,456,789', 'Id123', '10/20/2012'
UNION
SELECT '4/4/2004', '963', 'ID678', '9/9/2009'

SELECT createdate, empid, accessdate, Tags.val.value('.', 'VARCHAR(MAX)') AS policy#
  FROM(  
		  SELECT createdate, 
				 CAST('<t>' + REPLACE(policy#, ',', '</t><t>') + '</t>' AS XML) AS TAG, 
				 empid, 
				 accessdate
			FROM @tracmonali
			WHERE  policy# LIKE '%,%'  
	  ) TAB 
 CROSS APPLY TAG.nodes('/t') as Tags(val)
 UNION
select createdate, empid, accessdate, policy#
  From @tracmonali
 WHERE  policy# NOT LIKE '%,%'

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

tracmonali
Starting Member

16 Posts

Posted - 2012-04-18 : 12:14:34

Thanks yosiasz. This is helpful, but only for 2 rows. Is it possible to make it dynamic? The select stmts are static. what if there are more than one row with delimiter data? How can I avoid cursors to access all rows of the data and check if such data is present?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-04-18 : 12:54:07

the cursor is just for sample data for testing purposes. you can do it sin cursors. for dynamic did you test the code given and extending the comma delimited values?

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

tracmonali
Starting Member

16 Posts

Posted - 2012-04-19 : 13:55:46

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources