0

I have two tables. One with properties and one with buildings. Each property is associated with 0 to theoretically infinite amounts of buildings. Right now i have a code like this:

Select Property.ID, Building.Number
From Properties
Left Join Buildings on Buildings.pID = Property.ID

This returns a table of all buildings with their associated property. This however means, that all properties appear as many times, as they have buildings.

What i want is a result, where each property has its buildings in the same row as it self, so it becomes a result of properties, with their buildings, and not a result of buildings, with their properties.

EDIT: I should probably specify, that this is a server i only have read access on.

Mohring
  • 13
  • 6
  • 3
    what DBMS are you using? – Gilad Green Aug 31 '16 at 10:53
  • I don't know. This is not server i have direct access to. I access it through the company's own proprietary software. – Mohring Aug 31 '16 at 10:58
  • You can try with GROUP_CONCAT ( http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat ) or use PIVOT Table ( http://www.artfulsoftware.com/infotree/qrytip.php?id=78 & http://stackoverflow.com/a/7675121/2039790 ) – Volvox Aug 31 '16 at 10:58
  • 2
    Basically this question depending on the RDBMS is a duplicate of: [my sql](http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql). [sql server](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) or [oracle](http://stackoverflow.com/questions/13451605/how-to-use-group-concat-in-a-concat-in-mysql) – Gilad Green Aug 31 '16 at 11:00
  • Add sample table data, and the expected result. – jarlh Aug 31 '16 at 11:14
  • Added `sql-server` tag based on the comment "that works" for a SQL Server specific answer. –  Aug 31 '16 at 11:36

2 Answers2

0

If the properties really are able to have an infinite amount of buildings then this will become really hard. You will have to create a table with an infinite amount of rows (one for each potential building), and set all of them to NULL except where there are buildings.

You can't have a dynamic amount of columns. It will have to be set beforehand. There are suboptimal workarounds, but they are all very contrary to database normalization.

edit: if it's just for the results of a query you could use PIVOT()

Iain
  • 387
  • 2
  • 12
0

In sql server, you can use STUFF fuctionality for combining the resultset. 

SELECT  P.ID
       ,STUFF((SELECT ', ' + CAST(b.Number AS VARCHAR(10)) [text()]
         FROM Buildings b 
         WHERE b.pID = P.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') BuildingNumbers
FROM Properties p
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
  • Thank you, this works! What i really want, is for the b.Numbers to be split into individual cells, rather than a single cell. Do you know of a way to that? – Mohring Aug 31 '16 at 11:08
  • building numbers are specific to the property right?? so i think this is the best way.. – Unnikrishnan R Aug 31 '16 at 11:12