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
|
WITH liste AS
(
SELECT 'user_1' AS user_code , 'app_A' as app
UNION ALL
SELECT 'user_1' AS user_code , 'app_B' as app
UNION ALL
SELECT 'user_1' AS user_code , 'app_C' as app
UNION ALL
SELECT 'user_2' AS user_code , 'app_A' as app
UNION ALL
SELECT 'user_2' AS user_code , 'app_B' as app
UNION ALL
SELECT 'user_3' AS user_code , 'app_A' as app
UNION ALL
SELECT 'user_3' AS user_code , 'app_C' as app
UNION ALL
SELECT 'user_4' AS user_code , 'app_A' as app
UNION ALL
SELECT 'user_4' AS user_code , 'app_B' as app
UNION ALL
SELECT 'user_5' AS user_code , 'app_B' as app
),
ranked AS
(
SELECT user_code
, CAST(app AS NVARCHAR(MAX)) AS app
, ROW_NUMBER() over (PARTITION BY user_code ORDER BY app) AS rang
, COUNT(*) over (PARTITION BY user_code) AS Nb
FROM liste
),
concatenation AS
(
SELECT user_code, app, rang, nb
FROM ranked
WHERE rang = 1
UNION ALL
SELECT c.user_code, c.app + ' | ' + r.app, r.rang, c.nb
FROM concatenation c
INNER JOIN ranked r ON r.user_code = c.user_code
AND r.rang = c.rang + 1
)
SELECT *
FROM concatenation
WHERE rang = nb |
Partager