1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
|
DECLARE @tempTable TABLE
(
t1ID INT,
t3ID INT
)
INSERT INTO @tempTable
SELECT
table1.t1ID,
table2.t3ID
FROM
table1
INNER JOIN table2 ON table1.t1ID = table2.t1ID;
WITH R (id, rk, val) AS
(
SELECT
t1ID,
1,
CAST (MIN(t3ID) AS VARCHAR(255))
FROM @tempTable H
GROUP BY t1ID
UNION ALL
SELECT
D.id,
D.rk,
CAST (MIN(H.val) AS VARCHAR(255)) + ',' + CAST (MIN(D.val) AS VARCHAR(255))
FROM
R H,
(
SELECT
t1ID AS id,
DENSE_RANK() OVER (PARTITION BY t1ID ORDER BY t3ID) AS RK,
t3ID AS VAL
FROM @tempTable
) AS D
WHERE
D.id = H.id
AND
D.rk = H.rk + 1
)
SELECT
id,
MAX(val)
FROM R
GROUP BY id |
Partager