Search This Blog

Monday, September 2, 2013

SYBASE: CONSOLIDATE Multiple rows into comma separated field


CREATE TABLE #tbl (ad_id INT, ac_id VARCHAR(255), dt INT, rel INT)

INSERT INTO #tbl VALUES (1,'10',111,1)
INSERT INTO #tbl VALUES (2,'11',112,0)
INSERT INTO #tbl VALUES (3,'12',113,0)
INSERT INTO #tbl VALUES (3,'13',114,1)
INSERT INTO #tbl VALUES (3,'14',115,1)
INSERT INTO #tbl VALUES (4,'15',116,1)
INSERT INTO #tbl VALUES (4,'16',117,1)
INSERT INTO #tbl VALUES (4,'17',118,1)

SELECT * , dt1 = MIN(dt), flg = 0
INTO #tbl2
FROM #tbl
WHERE rel = 1
GROUP BY ad_id
HAVING rel = 1


UPDATE #tbl2
        SET flg = 1
    FROM #tbl2
    WHERE dt = dt1

WHILE EXISTS (SELECT 1 FROM #tbl2
            WHERE  flg = 0)
BEGIN
    UPDATE #tbl2
            SET ac_id = t1.ac_id +',' + t2.ac_id
                ,flg = 2
        FROM #tbl2 t1, #tbl2 t2
        WHERE t1.ad_id = t2.ad_id
        AND t1.dt = t1.dt1
        AND t2.dt = (SELECT MIN(dt) FROM #tbl2
                        WHERE ad_id = t1.ad_id
                        AND dt > t1.dt)

    DELETE #tbl2
        FROM #tbl2 t1
        WHERE t1.dt = (SELECT MIN(dt) FROM #tbl2
                        WHERE ad_id = t1.ad_id
                        AND dt > t1.dt1)
END           

SELECT * FROM #tbl2

No comments:

Post a Comment