Блог

Я пытаюсь получить таблицу, которая выглядит следующим образом:

 Columns : [District] [Name] [Status1] [Status2] [Status3]

Data: DistrictA    MrChan   1      1       1 

Data: DistrictB    MrFoo    1     0       2

Data: DistrictB    MsLucy   0     1       0
(sorry the table turns out unexpected after posting)

select StatusID, StatusCode from 
BookingStatus retrieves all categorical statuses Status1, Status2, Status3


select userid, DistrictA, StatusID from 
UnitBooking retrieve multiple rows which represent booking transactions. 
  

В приведенном выше примере Ms Lucy выполнила 1 бронирование, у нее будет 1 строка в UnitBooking. У мистера Foo есть 3 строки, а у мистера Чана также есть 3 строки.

Пример данных:

 select [userid], [username], [District], [StatusID] from UnitBooking

[1],[MrChan],[DistrictA],[1]

[1],[MrChan],[DistrictA],[2]

[1],[MrChan],[DistrictA],[3]

[2],[MrFoo],[DistrictB],[1]

[2],[MrFoo],[DistrictB],[3]

[2],[MrFoo],[DistrictB],[3]

[3],[MsLucy],[DistrictB],[2]


select [StatusID], [StatusCode] from BookingStatus

[1],[Status1]

[2],[Status2]

[3],[Status3]
  

Что такое T-SQL, который выдает результирующий набор?

Большое спасибо

 select 
district,
username,
sum(case when bs.statusid=1 then 1 else 0 end) 'status1',
sum(case when bs.statusid=2 then 1 else 0 end) 'status2',
sum(case when bs.statusid=3 then 1 else 0 end) 'status13'
from
unitbooking ub
join
BookingStatus Bs
on bs.statusid=ub.statusid
group by district,username
  

Я придумал SQL, который работает для моего отчета, но я не разбираюсь в том, что делают функция STUFF и часть пути XML, за исключением того, что это работает. Далее:

 DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)

SELECT @cols = STUFF((  SELECT ', SUM(CASE WHEN ub.BookingStatus=' 
                      Cast(ItemID as nvarchar(128)) 
                      ' THEN 1 ELSE 0 END) ' 
                      QUOTENAME(StatusCode)
                    FROM customtable_BookingStatus
                    --WHERE ItemID Not In (1)
                    GROUP BY StatusCode
                            ,ItemID
                    ORDER BY ItemID
                    FOR XML PATH('')
                    ,TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = 'SELECT usr.FullName, usi.District, '
         'cnt.[OPEN] as [DUALLANGUAGE], cnt.BOOKED, cnt.CONFIRMED, cnt.SOLD from customtable__User usr
          left join customtable_Public_User_Info usi on usi.UserID = usr.UserID
          inner join '
         '(SELECT ub.UserID,'   @cols 
         'from customtable_UnitBooking ub
          join customtable_BookingStatus bs on bs.ItemID = ub.BookingStatus
          group by ub.UserId, ub.BookingStatus) '
         'cnt on cnt.UserID = usr.UserID'
--Print @query
EXEC sp_executesql @query;
  

Вот некоторые дополнительные удобства:

-Поддержка новых записей в таблице BookingStatus динамически. Все еще необходимо явно указать имя нового столбца в final @query.

-Технически имена нового столбца также могут быть динамическими внутри @query путем добавления дополнительной функции STUFF с аналогичной сигнатурой, которая извлекает простые столбцы, разделенные запятыми. Вот фрагмент:

 DECLARE @colsForQuery AS NVARCHAR(MAX)
SELECT @colsForQuery = STUFF((  SELECT ',cnt.'   QUOTENAME(StatusCode) 
                    FROM customtable_BookingStatus
                    --WHERE ItemID Not In (1)
                    GROUP BY StatusCode
                            ,ItemID
                    ORDER BY ItemID
                    FOR XML PATH('')
                    ,TYPE
                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
  

выдает это:

  cnt.[OPEN],cnt.[BOOKED],cnt.[SOLD],cnt.[CONFIRMED]
  

-Мой отчет имеет двойной язык, но не простой, если все столбцы полностью динамические

-Опустите столбцы, используя «ГДЕ ItemId отсутствует в (1)» или удалите его в @query

-Следите за столбцами, которые конфликтуют с ключевым словом T-SQL, например, я должен поместить [OPEN], потому что его ключевое слово

В целом я думаю, что gameiswar — правильный ответ. Я просто расширяю его решение на основе URL, предложенного в комментариях.