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.
| Author |
Topic |
|
Johnph
Posting Yak Master
103 Posts |
 Posted - 2013-06-28 : 11:53:36
|
| I have data that looks like this (3 columns):[103] [A] [1] [103] [B] [test][103] [B] [nest] I need it to do this:[103] [A] [1] [103] [B] [test, nest]This is based off of the first two column values, if they match then it needs to concatenate. Also, there could be 4 rows with the same columns, so 4 concatenates may take place.Thanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-28 : 12:02:07
|
| [code]SELECT Col1,Col2,STUFF((SELECT ',' + Col3 FROM Table WHERE Col1 = t.Col1 AND Col2 = t.Col2 FOR XML PATH('')),1,1,'') AS Col3FROM (SELECT DISTINCT Col1,Col2 FRM Table)t[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2013-06-28 : 13:36:41
|
| sorry visakh, I don't follow.What is FOR PATH (' ') ? I never never seen that before. Is there a way I can do this output without XML stuff? |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-28 : 13:46:17
|
quote:
Originally posted by Johnph
sorry visakh, I don't follow.What is FOR PATH (' ') ? I never never seen that before. Is there a way I can do this output without XML stuff?
While the query does use the framework and plumbing in the SQL Server XML, there is really no XML in the final result. You are tricking the XML subsystem into creating a concatenated string by asking it to create an XML document with no node names.Give that a try and you will be pleased with the results. Starting with SQL 2005, this is the recommended and most widely used approach to string concatenation. |
|
|
|
Johnph
Posting Yak Master
103 Posts |
Posted - 2013-06-28 : 13:51:51
|
| What I am asking, cannot be done via SQL? and a XML subsystem is needed? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-28 : 13:58:44
|
quote:
Originally posted by Johnph
What I am asking, cannot be done via SQL? and a XML subsystem is needed?
No need of any XML subsystem. You just need SQL Server with version 2005 or above------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-06-28 : 14:08:19
|
| I used the word "subsystem" just conceptually. It is not a plugin or choice that you have to make when installing SQL Server. It is part of SQL Server installation, just like any other T-SQL data type or feature.There are other ways of concatenating the rows of a table. The two methods that people often used before the advent of XML PATH approach were a) using a while loop - you will find a lot of examples if you google, and b) the quirky update.Using while loop has the problem that it is comparitively slow, and hence not very scalable. Quirky update is, well, quirky - i.e., uses some undocumented features. Jeff Moden's article is good: http://www.sqlservercentral.com/articles/T-SQL/68467/ |
|
|
|
|
|
|
|
|
Complex SQL Logic