0

I have two tables: Accounts and Property. The Accounts table has a field called "accountID". The Property table has fields called "accountID" and "propertyName".

I need to return a list of all the accountIDs and with all of its associated properties in a comma separated concatenated column... For example I have 3 account ID's: 101, 102, 103

accountID 101 has three properties: house, car, boat
accountID 102 has four properties: plane, yacht, motorcycle oyster
accountId 103 has one property: fish

I need the return to look like: 

accountID   properties
101         house, car, boat
102         plane, yacht, motorcycle, oyster
103         fish
Dour High Arch
  • 21,081
  • 29
  • 74
  • 89
Trung Tran
  • 11,579
  • 39
  • 105
  • 187
  • What you are looking for is called a pivot table. It will depend on which database you are using as to how to implement it. – Adam Zuckerman Apr 17 '14 at 23:28
  • If you are using MySQL then your question has been answered [here](http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field), if you are using SQL Server your question has been answered [here](http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows-from-a-su). If neither then tell us which database you are using. – Tony Apr 17 '14 at 23:32

1 Answers1

0

Something like this?

SELECT `accountID`, GROUP_CONCAT(`properyName` SEPARATOR ', ') as `properties`
FROM `Accounts`
JOIN `Property` USING (`accountID`)
GROUP BY `accountID`
Niet the Dark Absol
  • 311,322
  • 76
  • 447
  • 566