SELECT s.season,
ISNULL(
STUFF(
(
SELECT ',' + m.month
FROM @relation r
LEFT JOIN @months m ON (m.id = r.idMonth)
WHERE r.idSeason = s.id
FOR XML PATH ('')
), 1, 1, ''
), 'No month is selected') AS [Months in the season]
FROM @seasons s
ORDER BY s.id;
SELECT m.month,
ISNULL(
STUFF(
(
SELECT ',' + s.season
FROM @relation r
LEFT JOIN @seasons s ON (s.id = r.idseason)
WHERE r.idmonth = m.id
FOR XML PATH ('')
), 1, 1, ''
), 'No season is selected') AS [Seasons for the month]
FROM @months m
ORDER BY m.id;
declare @seasons as table (id int, season varchar(10)); insert into @seasons values (1, 'Winter'), (2, 'Spring'), (3, 'Summer'), (4, 'Autumn'), (5, 'Dummy');
declare @months as table (id int, month varchar(10)); insert into @months values
(1, 'January'), (2, 'February'), (3, 'March'), (4, 'April'), (5, 'May'), (6, 'June'), (7, 'July'), (8, 'August'), (9, 'September'), (10, 'October'), (11, 'November'), (12, 'December');
declare @relation as table (idSeason int, idMonth int); insert into @relation values (1, 12), (1, 1), (1, 2), (1, 3), (2, 3), (2, 4), (2, 5), (2, 6), (3, 6), (3, 7), (3, 8), (3, 9), (4, 9), (4,10), (4,11), (4,12);
SELECT s.season, ISNULL( STUFF( ( SELECT ',' + m.month FROM @relation r LEFT JOIN @months m ON (m.id = r.idMonth) WHERE r.idSeason = s.id FOR XML PATH ('') ), 1, 1, '' ), 'No month is selected') AS [Months in the season] FROM @seasons s ORDER BY s.id;
SELECT m.month, ISNULL( STUFF( ( SELECT ',' + s.season FROM @relation r LEFT JOIN @seasons s ON (s.id = r.idseason) WHERE r.idmonth = m.id FOR XML PATH ('') ), 1, 1, '' ), 'No season is selected') AS [Seasons for the month] FROM @months m ORDER BY m.id;