Комбинация запросов T-SQL из доступных и выбранных в одну строку

У меня две таблицы. Одна из них – таблица library а другая – реляционная таблица (см. Ниже). В table1 перечислены все возможные списки рассылки (или что-то еще), table2 указывает, что контакт «Джон» указан в списке рассылки 1,3,4. Можно ли написать запрос, чтобы получить все доступные элементы списка рассылки и выбранные элементы в одной строке? Не могли бы вы помочь?

вывод в одной строке:

 John, A:1, B:0, C:1, D:1, E:0 Mary, A:1, B:0, C:0, D:0, E:1 

table1 (таблица библиотек)

 Id Name 1 A 2 B 3 C 4 D 5 E 

таблица2 (отношение таблица)

 contact Mail-id John 1 John 3 John 4 Mary 1 Mary 5 

Перекрестно присоедините отдельный контакт в таблице2 с таблицей 1, а затем присоедините результат к таблице2

Если вы хотите, чтобы список рассылки в одном столбце делился запятой, то for xml path() трюка for xml path() . Попробуй это.

 ;WITH cte AS (SELECT b.contact, a.NAME + CASE WHEN c.[Mail-id] IS NOT NULL THEN ':1' ELSE ':0' END AS aval_mailinglist FROM tab1e1 a CROSS JOIN (SELECT DISTINCT contact FROM table2) b LEFT JOIN table2 c ON a.Id = c.[Mail-id] and b.contact=c.contact) SELECT contact, stuff((SELECT ',' + aval_mailinglist FROM cte b WHERE a.contact = b.contact FOR xml path('')),1,1,'') Mailing_list FROM cte a group by contact 

Если вы хотите получить результат в разных столбцах, используйте Pivot

 DECLARE @cols VARCHAR(max)='', @sql NVARCHAR(max) SELECT @cols += NAME FROM (SELECT DISTINCT Quotename(Isnull(NAME, '')) + ',' NAME FROM table1)a SELECT @cols = LEFT(@cols, Len(@cols) - 1) PRINT @cols SET @sql=';WITH cte AS (SELECT b.contact, a.NAME + CASE WHEN c.[Mail-id] IS NOT NULL THEN '':1'' ELSE '':0'' END AS aval_mailinglist, a.name FROM table1 a CROSS JOIN (SELECT DISTINCT contact FROM table2) b LEFT JOIN table2 c ON a.Id = c.[Mail-id] and b.contact=c.contact) SELECT * FROM cte a pivot (max(aval_mailinglist) for name in (' + @cols + ') ) piv' --print @sql EXEC Sp_executesql @sql 

SQLFIDDLE DEMO

Давайте будем гением компьютера.