2

I have a table of information in a SQL Server database and another table of group info which I'd like to concatenate into the first table.

e.g.

table1: 

1 | 'foo' | 'bar,baz'

and

table2: 

1 | 'bar'
1 | 'baz'

I want to replace the need for that third column in table1 by doing a GROUP BY CONCAT with table2. The only issue is after I am done developing this project I'm conscious I will need to port the SQL to Oracle, so perhaps doing a separate SELECT for one column of the gridview is a better alternative?

Thanks a lot.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
deed02392
  • 4,799
  • 2
  • 31
  • 48

1 Answers1

1

Easy MS SQL concat can be done with converting rows you need to XML and back to nvarchar. Example: Imagine you have 2 tables - one for Contacts (with PK ID) and second with ContactTypes (with ContactID as FK, could have multiple rows for 1 contact). You need to concat all types to 1 row. Here is trick to do this:

SELECT c.ContactID, CONVERT(VARCHAR(MAX), /* concatenate all Types to varchar */
(
                  SELECT ct.ContactTypeDescription+ ', '
                  FROM   ContactTypes ct
                  JOIN Contacts c1 ON  ct.ContactID = c1.ContactID 
                   FOR XML PATH('')
            )) AS ContactTypes, * 
            FROM Contacts c

This should make output like this: 

1 | Type1, Type2,  ... | ...
2 | Type3, Type10, ... | ...

etc.

Dunno about Oracle port, hope this helps...

Anton G
  • 71
  • 5
  • OK, I came up with a solution using this. I hope this won't cause a nightmare when I rewrite the queries for Oracle. One question here though, is there a way to remove the trailing ', '? – deed02392 Jan 23 '12 at 09:53
  • Sure, for example you can use substring or Left(field, Len(field)-2). – Anton G Jan 23 '12 at 10:30