Время от времени мы сталкиваемся с этими редкими проблемами SQL, когда мы хотели бы сделать что-то необычное. Одной из таких вещей является поворот строк в столбцы.
Недавний вопрос о переполнении стека Валианте задал именно этот вопрос. Исходя из этой таблицы:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
|
+------+------------+----------------+-------------------+ | dnId | propNameId | propertyName | propertyValue | +------+------------+----------------+-------------------+ | 1 | 10 | objectsid | S- 1 - 5 - 32 - 548 | | 1 | 19 | _objectclass | group | | 1 | 80 | cn | Account Operators | | 1 | 82 | samaccountname | Account Operators | | 1 | 85 | name | Account Operators | | 2 | 10 | objectsid | S- 1 - 5 - 32 - 544 | | 2 | 19 | _objectclass | group | | 2 | 80 | cn | Administrators | | 2 | 82 | samaccountname | Administrators | | 2 | 85 | name | Administrators | | 3 | 10 | objectsid | S- 1 - 5 - 32 - 551 | | 3 | 19 | _objectclass | group | | 3 | 80 | cn | Backup Operators | | 3 | 82 | samaccountname | Backup Operators | | 3 | 85 | name | Backup Operators | +------+------------+----------------+-------------------+ |
… мы хотели бы преобразовать строки в столбцы как таковые:
1
2
3
4
5
6
7
|
+------+--------------+--------------+-------------------+-------------------+-------------------+ | dnId | objectsid | _objectclass | cn | samaccountname | name | +------+--------------+--------------+-------------------+-------------------+-------------------+ | 1 | S- 1 - 5 - 32 - 548 | group | Account Operators | Account Operators | Account Operators | | 2 | S- 1 - 5 - 32 - 544 | group | Administrators | Administrators | Administrators | | 3 | S- 1 - 5 - 32 - 551 | group | Backup Operators | Backup Operators | Backup Operators | +------+--------------+--------------+-------------------+-------------------+-------------------+ |
Идея состоит в том, что нам нужен только один ряд на отдельный dnId
, а затем мы хотим преобразовать пары свойство-имя-значение в столбцы, по одному столбцу на имя свойства.
Использование Oracle или SQL Server PIVOT
Вышеуказанное преобразование на самом деле довольно просто с Oracle и SQL Server , которые поддерживают ключевое слово PIVOT
в табличных выражениях.
Вот как можно получить желаемый результат с помощью SQL Server:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
|
SELECT p.* FROM ( SELECT dnId, propertyName, propertyValue FROM myTable ) AS t PIVOT( MAX (propertyValue) FOR propertyName IN ( objectsid, _objectclass, cn, samaccountname, name ) ) AS p; |
И тот же запрос с немного другим синтаксисом в Oracle:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
|
SELECT p.* FROM ( SELECT dnId, propertyName, propertyValue FROM myTable ) t PIVOT( MAX (propertyValue) FOR propertyName IN ( 'objectsid' as "objectsid" , '_objectclass' as "_objectclass" , 'cn' as "cn" , 'samaccountname' as "samaccountname" , 'name' as "name" ) ) p; |
Как это работает?
Важно понимать, что PIVOT
(очень похоже на JOIN
) — это ключевое слово, которое применяется к ссылке на таблицу для ее преобразования. В вышеприведенном примере мы по существу преобразуем производную таблицу t
чтобы сформировать сводную таблицу p
. Мы могли бы пойти дальше и присоединить p
к другой производной таблице следующим образом:
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
|
SELECT * FROM ( SELECT dnId, propertyName, propertyValue FROM myTable ) t PIVOT( MAX (propertyValue) FOR propertyName IN ( 'objectsid' as "objectsid" , '_objectclass' as "_objectclass" , 'cn' as "cn" , 'samaccountname' as "samaccountname" , 'name' as "name" ) ) p JOIN ( SELECT dnId, COUNT (*) availableAttributes FROM myTable GROUP BY dnId ) q USING (dnId); |
Вышеупомянутый запрос теперь позволяет находить те строки, для которых нет пары имя / значение в каждом столбце. Давайте предположим, что мы удалили одну из записей из исходной таблицы, приведенный выше запрос может теперь вернуть:
1
2
3
4
5
|
| DNID | OBJECTSID | _OBJECTCLASS | CN | SAMACCOUNTNAME | NAME | AVAILABLEATTRIBUTES | |------|--------------|--------------|-------------------|-------------------|-------------------|---------------------| | 1 | S- 1 - 5 - 32 - 548 | group | Account Operators | Account Operators | Account Operators | 5 | | 2 | S- 1 - 5 - 32 - 544 | group | Administrators | ( null ) | Administrators | 4 | | 3 | S- 1 - 5 - 32 - 551 | group | Backup Operators | Backup Operators | Backup Operators | 5 | |
jOOQ также поддерживает предложение SQL PIVOT
через его API .
Что делать, если у меня нет PIVOT?
В простых сценариях PIVOT
пользователи других баз данных, кроме Oracle или SQL Server, могут написать эквивалентный запрос, который использует выражения GROUP BY
и MAX(CASE ...)
как описано здесь в этом ответе .
Ссылка: | Вы уже используете SQL PIVOT? Вам следует! от нашего партнера JCG Лукаса Эдера в блоге JAVA, SQL и JOOQ . |