Convert values to 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 2005 Forums

Transact-SQL (2005)

Convert values to multiple rows

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-11-12 : 06:57:26

I want to convert comma seperated values into rows.



TID     TCodes          TValues
1      OEL, LEO, SER     458
2      LSE               58
3      LEO, OEL          63

OUTPUT

TID     TCodes          TValues
1       OEL              458
1       LEO              458
1       SER              458
2       LSE               58
3       LEO               63
3       OEL               63

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 07:04:41

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=46188
OR
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

PBUH

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-11-12 : 07:50:18

Thanks you sachin..
but i dont want to parse values..
i want my comma seperated values in repeatative rows

can you please post query.. it will be more helpful to me..

Thank again...

Sachin.Nand

2937 Posts

Posted - 2010-11-12 : 08:01:52

Then what is this?

quote:
I want to convert comma seperated values into rows.

TID TCodes TValues
1 OEL, LEO, SER 458
2 LSE 58
3 LEO, OEL 63

OUTPUT

TID TCodes TValues
1 OEL 458
1 LEO 458
1 SER 458
2 LSE 58
3 LEO 63
3 OEL 63

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-12 : 08:29:55

Refer this
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

Madhivanan

Failing to plan is Planning to fail

keyursoni85
Posting Yak Master

233 Posts

Posted - 2010-11-13 : 01:35:21

Thank you very very much madhivanan

quote:
Originally posted by madhivanan

Refer this
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-15 : 04:58:54

quote:
Originally posted by keyursoni85

Thank you very very much madhivanan

quote:
Originally posted by madhivanan

Refer this
http://www.sqlteam.com/article/parsing-csv-values-into-multiple-rows

Madhivanan

Failing to plan is Planning to fail

You are welcome

Madhivanan

Failing to plan is Planning to fail

dineshrajan_it
Posting Yak Master

217 Posts

Posted - 2010-12-03 : 02:22:58

Another approach using xml

declare @tab table
(
TID int identity(1,1),
TCodes varchar(100),
TValues int
)

insert into @tab(TCodes,TValues)
select * from
(
values
('OEL,LEO,SER',458)
,('LSE',58)
,('LEO,OEL',63)
)t(TCodes,TValues)

select t1.TValuesXML from @tab t
cross apply
(
SELECT [TValuesXML]=s.i.value('local-name(.)','varchar(100)')
FROM
(
select TID, [TValuesXML]=CAST('<' + REPLACE(TCodes,',','/><') + '/>' AS XML)
FROM
@tab T1

) T1
CROSS APPLY
T1.TValuesXML.nodes('/*')s(i)
WHERE T1.TID = t.TID
)t1

Iam a slow walker but i never walk back

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources