parsing the rows on commas - 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

General SQL Server Forums

New to SQL Server Programming

parsing the rows on commas

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2011-11-30 : 12:40:44

Table A

Col1 col2 Col3 col4 col5
12 456 A, B, C, D Ware, TELL, RAW QUEST, TIME, RAN, PEEK
13 987 W,E,R, D YES, TAP, WEE, TEE NEWS, JACK, LAN, BELL

I have this table, I need to get the data like this

12 456 A WARE QUEST
12 456 B TELL TIME
12 456 C RAW RAN
12 456 D PEEK
13 987 W YES NEWS
13 987 E TAP JACK
13 987 R WEE LAN
13 987 D TEE BELL

Basically, I want all the comma delimited from col3 and corresponmding comma delimited from col4 so A is related to ware and B is related to TELL and C is related to RAW.

Also, i want the col5 to be parsed to seperate rows.

Any help will be appreciated.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-30 : 13:01:32

what have you tried?

Have you tried normalizing the data model?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2011-11-30 : 13:11:16

The data model is not normalized. I am trying to do that. This is the legacy data that I getting from some other old database.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-30 : 13:15:18

you need a table udf

I'm not clear what rules you have to make the final results

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2011-11-30 : 13:24:26

I just need a table called Table A
and all the comma delimited column data in seperate rows. The only thing is whoever designed the original database have col3 related to col4

if col3 has A in it, I want WARE in col4, I don't want TELL in Col4, if Col3 has A in it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-11-30 : 14:28:39

An example of the table UDF that Brett referred to is available here: http://www.sqlservercentral.com/articles/Tally+Table/72993/

Copy and run the script in Fig. 21 on that page to install the function on your database. Then, your query would be something like this:

SELECT 
	t1.col1,
	t1.col2,
	c3.*
FROM
	TableA t1
	CROSS APPLY  dbo.DelimitedSplit8K(col3,',') C3

That handles only one of the comma-delimited columns. You can replicate that for each of the other comma-delimited columns and join all three.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources