Hi all. Can anyone help me? I have 2 tables, typical one to many
relationship related by id.

t1
id
cola
colb
with a row like this:
1 xxx yyy

t2
id
colc
with rows like this:
1 zzz
1 jjj

I need a query to return all rows from t1 with all related values in
colc from t2 in one row.
like this:
1 xxx yyy (zzz jjj in one column)

Can this be done? Thanks a bunch.

That's really something best handled in the client side application.

However, if you are insistent in following this line of madness, here are a
couple of 'kludges'.

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

I say 'kludges' because there are notes about potential problems attempting
to do something like this. SQL Server wasn't designed to deal with
presentation issues.

Take it for what value you get, check/verify thoroughly.

Show quoteHide quote

In general, a recommended approach is to extract the resultset outside the
server and present the data to appropriate display format using some client.
This is due to the rich string management library available to client side
programming languages and the built-in capabilities of report writing tools,
both of which are somewhat lacking in a language like SQL.

To munge it using t-SQL, you can check out the following links:
( For SQL 2005 only )
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/7e5b4c8a9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e

NEWSEQUINTIALID()
Slow Stored Procedure Execution
Join Question
Interesting Question
Update table field removing all non-numeric characters