|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Changing resultset to 1 row and 1 columnHow can i change the resultset of a query from being a column and several rows to just 1 column and 1 row? eg select * from tb1 The resultset is as below: Col1 ------ ab ac ad I would like it to come as below (that is as 1 column and 1 row): Col1 ------------ ab, ac, ad Col1 is varchar(5) in tb1. Thank you in advance. check this link...
http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html what you need seems to be very similar Hope this helps. Omnibuzz,
There are several drawbacks to that approach and it is better to avoid it. Many instances of failure are noted by several in this newsgroup's archives: http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/91ab5ca6453480d9 http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/70917b5ca789ea77 http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/371167d94545e1d7 -- Anith Thanks for the links Anith. I knew there was a problem with the group by
clause, thats why I added a disclaimer that it all depends on the query execution plan. Anyways.. I was trying to reply to this... then it became so big that I thought I will put it in my blog for reference :) Check this out and let me know if I make sense. http://omnibuzz-sql.blogspot.com/2006/07/resolution-for-concatenate-column.html -Omnibuzz (The SQL GC) http://omnibuzz-sql.blogspot.com/ See if this helps.
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e AMB Show quote "MittyKom" wrote: > Hi All > > How can i change the resultset of a query from being a column and several > rows to just 1 column and 1 row? eg > > select * from tb1 > The resultset is as below: > > Col1 > ------ > ab > ac > ad > > I would like it to come as below (that is as 1 column and 1 row): > Col1 > ------------ > ab, ac, ad > > Col1 is varchar(5) in tb1. Thank you in advance. > There are a couple of different approaches. I'll provide you a couple of
references and you can select the one that works best for you. How to rotate a table in SQL Server http://support.microsoft.com/default.aspx?scid=kb;en-us;175574 Concatenate One Field to Itself for string SQL 2000 http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html SQL 2005 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx -- Show quoteArnie Rowland Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message news:F455E6C5-255E-4F51-90CD-452C484DC50B@microsoft.com... > Hi All > > How can i change the resultset of a query from being a column and several > rows to just 1 column and 1 row? eg > > select * from tb1 > The resultset is as below: > > Col1 > ------ > ab > ac > ad > > I would like it to come as below (that is as 1 column and 1 row): > Col1 > ------------ > ab, ac, ad > > Col1 is varchar(5) in tb1. Thank you in advance. > Hello MittyKom,
I think there is no other solution than using a cursor. -> load the query result in a cursor -> the concatenate each line together in a temporary variable -> then return the temporary variable Show quote "MittyKom" <Mitty***@discussions.microsoft.com> wrote in message news:F455E6C5-255E-4F51-90CD-452C484DC50B@microsoft.com... > Hi All > > How can i change the resultset of a query from being a column and several > rows to just 1 column and 1 row? eg > > select * from tb1 > The resultset is as below: > > Col1 > ------ > ab > ac > ad > > I would like it to come as below (that is as 1 column and 1 row): > Col1 > ------------ > ab, ac, ad > > Col1 is varchar(5) in tb1. Thank you in advance. >  |
|||||||||||||||||||||||
Other interesting topics