Hi,
Can anyone help me with this query.

I have a table GassBottleSize with colums Gas, Bottlesize, id and values

ID      Gas                     BottleSize
------------------------------------
1        Trimix1845            50
2        Trimix1845            100
3        Trimix1170            50
4        Ean32                    100

Output I want is:

Gas                    BottleSize
----------------------------
Trimix1845        50,100
Trimix1170        50
Ean32                100

Thanks
D.

You might just find this interesting:

http://racster.blogspot.com/2006/08/dataphor-solving-humpty-dumpty-problem.html

The ideal approach should be to retrieve the resultset to the client side
and format the results appropriately. To do such stuff in the server
reliably, you'll have to write some "complex" code. Some ideas can be found
at:
http://www.projectdmx.com/tsql/rowconcatenate.aspx

No, if you are using SQL 2005 just use FOR XML - its a piece of cake and the
benefit is you can keep it in the server and only a few lines of code.

Show quoteHide quote

Did you get a chance to look at the various options described in that url?
The for xml trick is already suggested there.

If you are using SQL Server 2005 you can use the FOR XML extensions, I
discus it here:
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/11/429.aspx

Example


create table mailing_list (
   individual_name nvarchar(100) not null,
   list_name nvarchar(10) not null
)

insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'tony r', 'List
C' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
A' )
insert mailing_list ( individual_name, list_name ) values( 'joe r', 'List
B' )
insert mailing_list ( individual_name, list_name ) values( 'alex r', 'List
A' )

select distinct
   individual_name,
   list = substring(
      ( select ', ' + list_name as [text()]
         from mailing_list m2
         where m2.individual_name = m1.individual_name
         for xml path(''), elements )
      , 3, 100 )
from mailing_list m1



Gives this result :-

alex r      List A

joe r       List A, List B

tony r      List A, List B, List C


Show quoteHide quote

Marking (Not just Finding) Duplicate Values ......
Select part of a string
UPDATE in a SQL script file is not working.
Problem with WHERE clause
SET/SELECT truncate string