Hi

I'd like to get the results below from this sort of data. I might also have
additional tables that need another 'prods' type column.

Thanks
Andrew

---------------

declare @docs table (docID int primary key, docname varchar(25))
declare @prods table (prodID int primary key, docID int, prodname
varchar(25))


insert @docs values (1, 'doc1')
insert @docs values (2, 'doc2')
insert @docs values (3, 'doc3')

insert @prods values (1, 1, 'prod1')
insert @prods values (2, 2, 'prod2')
insert @prods values (3, 1, 'prod3')
insert @prods values (4, 2, 'prod4')
insert @prods values (5, 3, 'prod5')
insert @prods values (6, 2, 'prod6')


/*

docID  docname prods
-----  ------- -------------------
1      doc1    prod1, prod3
2      doc2    prod2, prod4, prod6
3      doc3    prod5


*/

In general, a recommended approach is to extract the resultset outside the
server and massage the data to appropriate display format using some client.
Regarding the workarounds for forcing this at the server, 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

For all types of static and dynamic crosstabs server side it's recommended
you check out RAC.Powerful and easy.

www.rac4sql.net

BUG(?): Distinct + variables TSQL
SQL 2005 updating data in SQL 2000 DB via linked server returns error
ORDER BY DATETIME field BUG?
aggregate function
search in only non tagged words