Ako recimo početna tabela izgleda ovako:
Code:
vreme vrv dan
Closed 1 Sunday
8:00AM-5:00PM 1 Monday
8:00AM-5:00PM 2 Tuesday
8:00AM-5:00PM 3 Wednesday
8:00AM-5:00PM 4 Thursday
8:30AM-5:00PM 1 Friday
Closed 2 Saturday
Za ovakav odgovor:
Code:
8:00AM-5:00PM Monday, Tuesday, Wednesday, Thursday
8:30AM-5:00PM Friday
Closed Sunday, Saturday
Rešenje bi bilo:
Code:
WITH X (vreme, CNT, lista, vrv, LEN)
AS (
SELECT vreme, COUNT(*) OVER (PARTITION BY vreme),
CAST(dan AS VARCHAR(100)), vrv, 1
FROM test
UNION ALL
SELECT X.vreme, X.CNT,
CAST(X.lista + ', ' + E.dan AS VARCHAR (100)),
E.vrv, X.LEN+1
FROM test E, X
WHERE E.vreme = X.vreme AND E.vrv > X.vrv
)
SELECT vreme, lista
FROM X
WHERE LEN = CNT
ORDER BY 1
Ovo je ovako na brzinu, moglo bi to verovatno i bolje da se izvede. U početni uslov je dodata još jedna kolona (vrv) jer sam ovo već imao ovako rešeno, ali mislim da je vrlo približno traženom i dosta za početak