Справка по SQL-запросу с таблицей мостов

Вы можете сделать что-то вроде этого:

select userid, username
,      max(case when permissionid=10 then rights end) as permission10_rights
,      max(case when permissionid=11 then rights end) as permission11_rights
,      max(case when permissionid=12 then rights end) as permission12_rights
from   userpermissions
group by userid, username;

Вы должны явно добавить аналогичный столбец max (...) для каждого идентификатора разрешения.

Вы можете создать временный table_flatuserpermissions:

UserID
PermissionID1
Rights1
PermissionID2
Rights2
...etc to as many permission/right combinations as you need

Вставьте записи в эту таблицу из Table_user со всеми пустыми полями разрешений и прав.

Обновите записи в этой таблице из table_userpermissions - вставьте первую запись и установите PermissionID1 и Rights1, вторую запись для пользователя обновите PermissionsID2 & Rights2 и т. д.

Затем вы запрашиваете эту таблицу для создания отчета.

Лично я бы просто придерживался столбцов UserId, UserName, PermissionID, Rights, которые у вас есть сейчас.

Возможно, вместо числовых значений замените PermissionID и Rights каким-нибудь текстом.

Возможно, отсортируйте таблицу по PermissionID, User вместо User, PermissionID, чтобы аудитор мог проверить пользователей по каждому типу разрешений.

Короткий ответ:

Нет.

Вы не можете динамически добавлять столбцы в свой запрос.

Помните, что SQL - это язык, основанный на наборах. Вы запрашиваете наборы и объединяете наборы вместе.

То, что вы выкапываете, является рекурсивным списком и требует, чтобы список был соединен горизонтально, а не вертикально.

Вы можете, вроде как, подделать это с помощью набора самоподключений, но для этого вы должны знать все возможные разрешения, прежде чем писать запрос ... это то, что предлагали другие предложения.

Вы также можете перенести набор записей обратно на другой язык, а затем перебрать его, чтобы сгенерировать правильные столбцы.

Что-то типа:

SELECT Table_User.userID, userName, permissionid, rights
FROM Table_User
        LEFT JOIN Table_UserPermissions ON Table_User.userID =Table_UserPermissions.userID
ORDER BY userName

А затем отобразите все разрешения для каждого пользователя, используя что-то вроде (Python):

userID = recordset[0][0]
userName = recordset[0][1]
for row in recordset:
   if userID != row[0]:
       printUserPermissions(username, user_permissions)
       user_permissions = []
       username = row[1]
       userID = row[0]

    user_permissions.append((row[2], row[3]))

printUserPermissions(username, user_permissions)

Если это приемлемо, стратегия, которую я использовал как для проектирования, так и для реализации, заключается в том, чтобы выгрузить запрос без преобразования в Excel или Access. Оба имеют гораздо более дружелюбный интерфейс для вращения данных, и гораздо большему количеству людей комфортно в такой среде.

Когда у вас есть понравившийся дизайн, проще подумать о том, как скопировать его в TSQL.

Похоже, что функция поворота была разработана для ситуаций, когда вы можете использовать агрегатную функцию для одного из полей. Например, если бы я хотел знать, какой доход приносит каждый продавец для компании x. Я могу суммировать поле цены из таблицы продаж. Затем я узнал бы продавца и его доход от продаж. Для разрешений, однако, нет смысла суммировать / подсчитывать / и т. д. В поле permissionId или поле «Права».

Вы можете посмотреть следующий пример создания кросс-таблиц в SQL:

http://www.databasejournal.com/features/mssql/article.php/3521101/Cross-Tab-reports-in-SQL-Server-2005.htm

Похоже, что есть новые операции, которые были включены как часть SQL Server 2005, называемые PIVOT и UNPIVOT.

Если вы используете MySQL, я бы посоветовал вам использовать group_concat (), как показано ниже.

select UserId, UserName, 
       group_concat(PermissionId) as PermIdList,
       group_concat(Rights SEPARATOR ',') as RightsList
from Table_user join Table_UserPermissions on 
     Table_User.UserId = Table_UserPermissions.UserId=
GROUP BY Table_User.UserId

Это вернет

UserId UserName PermIdList  RightsList
1      John     10,11,12    1,2,3

Быстрый поиск в Google по запросу «mssql group_concat» выявил несколько различных хранимых процедур (я), (II) для MSSQL, которые могут обеспечивать такое же поведение.

Для этого типа преобразования данных вам потребуется выполнить как UNPIVOT, так и PIVOT данных. Если вы знаете значения, которые хотите преобразовать, вы можете жестко запрограммировать запрос, используя статическую сводку, в противном случае вы можете использовать динамический sql.

Создать таблицы:

CREATE TABLE Table_User
    ([UserId] int, [UserName] varchar(5))
;

INSERT INTO Table_User
    ([UserId], [UserName])
VALUES
    (1, 'John'),
    (2, 'Joe'),
    (3, 'James')
;

CREATE TABLE Table_UserPermissions
    ([UserId] int, [PermissionId] int, [Rights] int)
;

INSERT INTO Table_UserPermissions
    ([UserId], [PermissionId], [Rights])
VALUES
    (1, 10, 1),
    (1, 11, 2),
    (1, 12, 3),
    (2, 11, 2),
    (2, 12, 3),
    (3, 10, 2)
;

Статический PIVOT:

select *
from
(
  select userid,
    username,
    value,
    col + '_'+ cast(rn as varchar(10)) col
  from
  (
    select u.userid,
      u.username,
      p.permissionid,
      p.rights,
      row_number() over(partition by u.userid 
                        order by p.permissionid, p.rights) rn
    from table_user u
    left join Table_UserPermissions p
      on u.userid = p.userid
  ) src
  unpivot
  (
    value
    for col in (permissionid, rights)
  ) unpiv
) src
pivot
(
  max(value)
  for col in (permissionid_1, rights_1, 
              permissionid_2, rights_2, 
              permissionid_3, rights_3)
) piv
order by userid

См. SQL Fiddle с демонстрацией

Динамический PIVOT:

Если у вас неизвестное количество permissionid и rights, вы можете использовать динамический sql:

DECLARE 
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(c.name +'_'+ cast(t.rn as varchar(10)))
                    from
                    (
                      select row_number() over(partition by u.userid 
                                order by p.permissionid, p.rights) rn
                      from table_user u
                      left join Table_UserPermissions p
                        on u.userid = p.userid
                    ) t
                    cross apply sys.columns as C
                   where C.object_id = object_id('Table_UserPermissions') and
                         C.name not in ('UserId')
                   group by c.name, t.rn
                   order by t.rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query 
  = 'select *
     from
     (
       select userid,
         username,
         value,
         col + ''_''+ cast(rn as varchar(10)) col
       from
       (
         select u.userid,
           u.username,
           p.permissionid,
           p.rights,
           row_number() over(partition by u.userid 
                             order by p.permissionid, p.rights) rn
         from table_user u
         left join Table_UserPermissions p
           on u.userid = p.userid
       ) src
       unpivot
       (
         value
         for col in (permissionid, rights)
       ) unpiv
     ) x1
     pivot
     (
       max(value)
       for col in ('+ @colspivot +')
     ) p
     order by userid'

exec(@query)

См. SQL Fiddle с демонстрацией

Результат для обоих:

| USERID | USERNAME | PERMISSIONID_1 | RIGHTS_1 | PERMISSIONID_2 | RIGHTS_2 | PERMISSIONID_3 | RIGHTS_3 |
---------------------------------------------------------------------------------------------------------
|      1 |     John |             10 |        1 |             11 |        2 |             12 |        3 |
|      2 |      Joe |             11 |        2 |             12 |        3 |         (null) |   (null) |
|      3 |    James |             10 |        2 |         (null) |   (null) |         (null) |   (null) |