sql - ReCursive/While Loop -

i trying build view looks @ table has 3 columns; building, lane, lot. need able loop through table dynamically display building, lane , lot on 1 row.

sample:

>building       lane     lot >   1           1001    56789 >   1           1002    12489 >   1           1002    37159 >   1           1002    71648 >   3           3001    27489 >   3           3001    67154 >   3           3002    47135 >   3           3003    84271 >   3           3003    96472 >   3           3003    94276 

results

>  building lane    lots >     1     1001    56789 >     1     1002    12489, 37159, 71648 >     3     3001    27489, 67154 >     3     3002    47135 >     3     3003    84271, 96472, 94276 

i tried recursion union received message had exceded max amount of 100. tried loop kept going , did not concantenate had hoped. far in table there on 300 lot numbers 1 building in 1 lane potential of huders more.

it looks you're looking similar below script (see sql fiddle example):

declare @row table (     varchar(1),     aa varchar(1),     b varchar(4) )  insert @row values(1,1,1001) insert @row values(1,1,1002) insert @row values(1,1,1003) insert @row values(1,2,1001) insert @row values(2,1,1001) insert @row values(2,1,1002) insert @row values(2,1,1003) insert @row values(2,1,1004) insert @row values(2,1,1005) insert @row values(2,2,1001) insert @row values(2,2,1002) insert @row values(3,1,1001)  select * @row  select r1.a     , r1.aa     , stuff((select ', ' + r2.b [text()]      @row r2     r1.a = r2.a , r1.aa = r2.aa     order r2.b      xml path('')),1,1,'') "row" @row r1 group a, aa 

so query, this:

select r1.building     , r1.lane     , stuff((select ', ' + r2.lot [text()]      yourtable r2     r1.building = r2.building , r1.lane = r2.lane     order r2.lot     xml path('')),1,1,'') "row" yourtable r1 group building, lane