converting values from single column to comma separate string based on other columns

Question

converting values from single column to comma separate string based on other columns

saum70

SSC Eights!

Points: 993
More actions
March 1, 2018 at 3:58 am

#336425

Hi,
I have used 'XML path' to convert values of one column to comma separate string, based on the other columns.
for e.g.
OrderId - Items
1 - Chesse
1 - Butter
1 - Salt
Using XML path, the above is converted into comma separate string for e.g.
OrderId - Items
1 - Cheese,Butter,Salt
Which is the other better and faster way to do the same. The table that i will refer will contain 6000000 records.
Kindly guide
Regards,
Saumik V

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

saum70 - Thursday, March 1, 2018 3:58 AM
Hi,
I have used 'XML path' to convert values of one column to comma separate string, based on the other columns.
for e.g.
OrderId - Items
1 - Chesse
1 - Butter
1 - Salt
Using XML path, the above is converted into comma separate string for e.g.
OrderId - Items
1 - Cheese,Butter,Salt
Which is the other better and faster way to do the same. The table that i will refer will contain 6000000 records.
Kindly guide
Regards,
Saumik V

With no code, no table definition, no sample data and only a vague explanation of desired output it is hard to say. But most likely what you have is the "best".

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

XML Path is usually the fastest way to create comma-separated lists. However, you shouldn't store them in a table and you shouldn't be concatenating all those values at once.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

saum70 SSC Eights! Points: 993 More actions · Answer 3

Luis Cazares - Thursday, March 1, 2018 8:35 AM
XML Path is usually the fastest way to create comma-separated lists. However, you shouldn't store them in a table and you shouldn't be concatenating all those values at once.

Thanx...no...i am not storing the valuea physically...they r for display purpose.