sql server - Merging multiple rows from one table into multiple colums in a SQL Query Result Set -

i pulling basic query new report , have come across little snag.

i have 2 tables buildings (b) , pictures (p) key in both buildingnumber. have simple inner join between 2 tables. problem having have 183 buildings in b, there multiple pictures each building, , number of pictures not consistent each building. when result set, returning 260 rows. return 1 row each building , have column each picture associated building. keep in mind not have rights alter or create tables.

my data this:

building table (b):

buildingnumber   bldgname   floors   sqft    0001           science       5     50000   0002           engineering   4     40000 

picture table (p):

buildingnumber     pictureurl     0001                url1    0001                url2    0001                url3      0002                url1     0002                url2 

so desired result set this:

buildingnumber   bldgname   floors   sqft   pictureurl1   pictureurl2   pictureurl3      0001         science        5     50000       url1        url2         url3     0002         engineering    4     40000       url1        url2          null 

you can use pivot function result, function converts rows of data columns.

if have limited number of pictureurls each building, can hard-code query:

select buildingnumber, bldgname, floors, sqft,   pictureurl1, pictureurl2, pictureurl3 (   select b.buildingnumber, b.bldgname, b.floors, b.sqft,     p.pictureurl,     col = 'pictureurl'+             cast(row_number() over(partition b.buildingnumber                                     order b.buildingnumber) varchar(10))   building b   inner join picture p     on b.buildingnumber = p.buildingnumber ) d pivot (   max(pictureurl)   col in (pictureurl1, pictureurl2, pictureurl3) ) piv; 

see sql fiddle demo. if have unknown number of values, have @ using dynamic sql. create sql string executed final result:

declare @cols nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select distinct ',' + quotename('pictureurl'+                                                       cast(row_number() over(partition buildingnumber                                                                               order buildingnumber) varchar(10)))                      picture             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = 'select buildingnumber, bldgname, floors, sqft,' + @cols + '                           (               select b.buildingnumber, b.bldgname, b.floors, b.sqft,                 p.pictureurl,                 col = ''pictureurl''+                         cast(row_number() over(partition b.buildingnumber                                                 order b.buildingnumber) varchar(10))               building b               inner join picture p                 on b.buildingnumber = p.buildingnumber             ) x             pivot              (                 max(pictureurl)                 col in (' + @cols + ')             ) p '  execute sp_executesql @query; 

see sql fiddle demo. both give result:

| buildingnumber |    bldgname | floors |  sqft | pictureurl1 | pictureurl2 | pictureurl3 | |              1 |     science |      5 | 50000 |        url1 |        url2 |        url3 | |              2 | engineering |      4 | 40000 |        url1 |        url2 |      (null) |