ReCursive/While Loop

Question

I am trying to build a view that looks at a table that has three columns; Building, Lane, Lot. I need to be able to loop through the table dynamically to display the Building, Lane and Lot all on one 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 a recursion union but received a message that I had exceded the max amount of 100. I also tried a loop but it just kept going and did not concantenate as I had hoped. So far in the table there are over 300 lot numbers for one building in one lane with the potential of huders more.

I do not understand the question. Maybe formatting sample and results better would help. — FrankPl, Aug 10 '13 at 00:50
Does SQL Server have an equivalent to MySQL's GROUP_CONCAT() ? — Izkata, Aug 10 '13 at 01:03
You can find some help in this [link](http://www.sqlservercentral.com/Forums/Topic1260952-338-1.aspx) — Nilesh, Aug 10 '13 at 01:26
Come on, this question gets asked multiple times a day. [Example](http://stackoverflow.com/questions/18155349/concatenate-a-single-column-into-a-comma-delimited-list/). — Aaron Bertrand, Aug 10 '13 at 02:10

user123 · Accepted Answer · 2013-08-10T02:08:37.710

It looks like you're looking for something similar to the below script (see this SQL Fiddle example):

DECLARE @row TABLE (
    A VARCHAR(1),
    AA VARCHAR(1),
    B VARCHAR(4)
)

INSERT INTO @row VALUES(1,1,1001)
INSERT INTO @row VALUES(1,1,1002)
INSERT INTO @row VALUES(1,1,1003)
INSERT INTO @row VALUES(1,2,1001)
INSERT INTO @row VALUES(2,1,1001)
INSERT INTO @row VALUES(2,1,1002)
INSERT INTO @row VALUES(2,1,1003)
INSERT INTO @row VALUES(2,1,1004)
INSERT INTO @row VALUES(2,1,1005)
INSERT INTO @row VALUES(2,2,1001)
INSERT INTO @row VALUES(2,2,1002)
INSERT INTO @row VALUES(3,1,1001)

SELECT *
FROM @row

SELECT r1.A
    , r1.AA
    , STUFF((SELECT ', ' + r2.B AS [text()] 
    FROM @row r2
    WHERE r1.A = r2.A AND r1.AA = r2.AA
    ORDER BY r2.B 
    FOR XML PATH('')),1,1,'') AS "Row"
FROM @row r1
GROUP BY A, AA

So for your query, it will look something like this:

SELECT r1.Building
    , r1.Lane
    , STUFF((SELECT ', ' + r2.Lot AS [text()] 
    FROM YourTable r2
    WHERE r1.Building = r2.Building AND r1.Lane = r2.Lane
    ORDER BY r2.Lot
    FOR XML PATH('')),1,1,'') AS "Row"
FROM YourTable r1
GROUP BY Building, Lane

ReCursive/While Loop

1 Answers1