sql — Транспонирование таблицы (строки в столбцы)
Какой запрос может транспонировать таблицу (строки станут столбцами)?
Причем, без использования функции pivot.
Обычный запрос, это просто.
Было:
select * from t1 / NAME VALUE ---- ---------- Вася 427 Петя 379 Маша 254 Паша 881 Коля 481
Стало:
select
sum (case name when 'Вася' then value end) "Вася",
sum (case name when 'Петя' then value end) "Петя",
sum (case name when 'Маша' then value end) "Маша",
sum (case name when 'Паша' then value end) "Паша",
sum (case name when 'Коля' then value end) "Коля"
from t1
/
Вася Петя Маша Паша Коля
---------- ---------- ---------- ---------- ----------
427 379 254 881 481
Почитайте про Pivot. Но и это не полное транспонирование в понимании матриц. Так как таблица в базе данных это не матрица.
На выборку типа
SELECT DaysToManufacture,
AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
DaysToManufacture AverageCost
----------------- -----------
0 5.0885
1 223.88
2 359.1082
4 949.4105
Применив pivot
SELECT 'AverageCost' AS
Cost_Sorted_By_Production_Days,
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture,
StandardCost
FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;
Мы получим
Cost_Sorted_By_Production_Days 0 1 2 3 4 ------------------------------ ----------- ----------- ----------- ----------- ----------- AverageCost 5.0885 223.88 359.1082 NULL 949.44105
4105
Зарегистрируйтесь или войдите
Регистрация через Google Регистрация через Facebook Регистрация через почтуОтправить без регистрации
ПочтаНеобходима, но никому не показывается
Отправить без регистрации
ПочтаНеобходима, но никому не показывается
By clicking “Отправить ответ”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.
Смена таблицы в SQL Server — SQL Server
Twitter LinkedIn Facebook Адрес электронной почты- Статья
В этой статье описывается, как повернуть таблицу в SQL Server.
Оригинальная версия продукта: SQL Server
Исходный номер базы знаний: 175574
Аннотация
В этой статье описывается, как повернуть таблицу SQL Server. Предположим, что у вас есть таблица с именем QTRSALES. Таблица содержит столбцы YEAR, QUARTERи AMOUNT с данными в следующем формате.
Примечание.
| Год | Квартал | Amount |
|---|---|---|
| 1995 | 1 | 125,000.90 |
| 1995 | 2 | 136,000.75 |
| 1995 | 3 | 212,000.34 |
| 1995 | 4 | 328,000.82 |
| 1996 | 3 | 728,000.35 |
| 1996 | 2 | 422,000.13 |
| 1996 | 1 | 328,000.82 |
Теперь предположим, что вы хотите повернуть таблицу, чтобы увидеть данные в следующем формате:
| YEAR | Q1 | В 2-й квартал | В3 | Вопрос 4 |
|---|---|---|---|---|
| 1995 | 125,000. 90 | 136,000.75 | 212,000.34 | 328,000.82 |
| 1996 | 328,000.82 | 422,000.13 | 728,000.35 | 0.00 |
Запрос, который будет использоваться для смены таблицы, приведен в следующем разделе этой статьи.
Пример запроса для смены таблицы
Ниже приведен запрос, который будет использоваться для смены таблицы:
SELECT YEAR, Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR = Q.YEAR),0), Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR = Q.YEAR),0), Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR = Q.YEAR),0), Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR = Q.YEAR),0) FROM QTRSALES Q GROUP BY YEAR
Запрос для больших таблиц
Для больших таблиц этот запрос будет выполняться быстрее:
year=q.year, SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1, SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2, SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3, SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4 FROM qtrsales q GROUP BY year
Ссылка
FROM — использование PIVOT и UNPIVOT
SQL Server — сводное преобразование строк в столбцы (с дополнительными данными строк)
Задавать вопрос
спросил
Изменено 2 года, 11 месяцев назад
Просмотрено 7к раз
Может ли кто-нибудь помочь мне с этим SQL?
Я хочу объединить несколько строк в столбцы по: идентификатору, типу, цвету, дате
Задача: у меня есть несколько столбцов, которые я хочу сохранить/показать в сводной таблице.
идентификатор | тип | цвет | дата | код_страны | расходы ---+--------+--------+------------+---------------+- ------ 1 | отчет | красный | 2020-09-01| США | 1 1 | отчет | красный | 2020-09-01| ЕС | 2 1 | отчет | красный | 2020-09-01| РУ | 3 1 | отчет | красный | 2020-09-01| АП | 4 1 | отчет | синий | 2020-09-02| США | 5 1 | отчет | синий | 2020-09-02| ЕС | 6 1 | отчет | синий | 2020-09-02| РУ | 7 1 | отчет | синий | 2020-09-02| АП | 8 2 | отчет | зеленый | 2020-09-02| США | 9 2 | отчет | зеленый | 2020-09-02| ЕС | 10 2 | отчет | зеленый | 2020-09-02| РУ | 11 2 | отчет | зеленый | 2020-09-02| АП | 12 2 | отчет | синий | 2020-09-03| США | 13 2 | отчет | синий | 2020-09-03| ЕС | 14 2 | отчет | синий | 2020-09-03| РУ | 15 2 | отчет | синий | 2020-09-03| АП | 16
Желаемый результат:
id | тип | цвет | дата | США | ЕС | РУ | АП ---+--------+-------+-------------+----+----+----+- --- 1 | отчет | красный | 2020-09-01 | 1 | 2 | 3 | 4 1 | отчет | синий | 2020-09-02 | 5 | 6 | 7 | 8 2 | отчет | зеленый | 2020-09-02 | 9 | 10 | 11 | 12 2 | отчет | синий | 2020-09-03 | 13 | 14 | 15 | 16
Известная информация:
- всего 4 кода страны.
- значение цвета
Не знаю, какой самый чистый/лучший способ написать этот SQL.
Я пытался использовать
ROW_NUMBER() OVER (РАЗДЕЛ ПО xxx ORDER BY yyy)
и PIVOT , но не смог получить результаты, которые я искал
- sql
- sql-server
- pivot
- unpivot
- partition-by 9 0028
Я думаю, что условная агрегация делает то, что вы хотите:
выберите идентификатор, тип, цвет, дату,
max(случай, когда код_страны = 'US', тогда стоимость заканчивается) как мы,
max(случай, когда код_страны = 'EU', а затем конец стоимости) как eu,
max(случай, когда country_code = 'RU', тогда стоимость заканчивается) как ru,
max(случай, когда код_страны = 'AP', затем окончание стоимости) как AP
от т
группировать по идентификатору, типу, цвету, дате;
Я поигрался и думаю, что получил это:
SELECT id, type, color, date, [US], [EU], [RU], [AP] ОТ ( ВЫБЕРИТЕ идентификатор, тип, цвет, дату, код страны, стоимость ИЗ таблицыX ) т ВРАЩАТЬСЯ ( МАКС(стоимость) FOR country_code IN ( [US], [EU], [RU], [AP] ) ) п
Зарегистрируйтесь или войдите в систему
Зарегистрируйтесь с помощью Google Зарегистрироваться через Facebook Зарегистрируйтесь, используя адрес электронной почты и парольОпубликовать как гость
Электронная почтаТребуется, но никогда не отображается
Опубликовать как гость
Электронная почтаТребуется, но не отображается
Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.
sql server — запрос SQL, который может преобразовать строки в столбец
Задавать вопрос
спросил
Изменено 1 год, 2 месяца назад
Просмотрено 1к раз
| RuModules_Id | Информация |
|---|---|
| 9 | 1,1,49 |
| 16 | 1,0,50 |
| 16 | 2,0,51 |
| 16 | 3,0,52 |
| 16 | 4,0,53 |
| 17 | 1,0,54 |
| 17 | 2,0,55 |
| 17 | 3,0,56 |
| 17 | 4,0,57 |
| 18 | 1,0,58 |
Мне нужно преобразовать таблицу A в таблицу B.
Может ли кто-нибудь помочь мне, как это сделать в SQL-запросе?
- sql
- sql-сервер
- tsql
Возможные подходы: условное агрегирование и оператор отношения PIVOT. Вам нужно пронумеровать строки и сгруппировать их соответствующим образом (даже если у вас более 4 строк для каждых 9 строк).0030 RuModules_Id ):
Оператор с условной агрегацией:
SELECT RuModules_Id, M1 = МАКС (СЛУЧАЙ, КОГДА RN% 4 = 0, ТОГДА информация КОНЕЦ), M2 = МАКС (СЛУЧАЙ, КОГДА RN% 4 = 1, ТОГДА информация КОНЕЦ), M3 = МАКС (СЛУЧАЙ, КОГДА RN % 4 = 1, ТОГДА информация КОНЕЦ), M4 = МАКС (СЛУЧАЙ, КОГДА RN % 4 = 2, ТОГДА Информация КОНЕЦ) ОТ ( SELECT *, ROW_NUMBER() OVER (PARTITION BY RuModules_Id ORDER BY Info) - 1 AS Rn ИЗ данных ) т СГРУППИРОВАТЬ ПО RuModules_Id, Rn/4
Заявление с PIVOT:
SELECT RuModules_Id, M1, M2, M3, M4
ОТ (
ВЫБИРАТЬ
(ROW_NUMBER() OVER (PARTITION BY RuModules_Id ORDER BY Info) - 1) / 4 AS Id,
*,
CASE (ROW_NUMBER() OVER (PARTITION BY RuModules_Id ORDER BY Info) - 1) % 4
КОГДА 0 ТОГДА 'M1'
КОГДА 1 ТОГДА 'M2'
КОГДА 2, ТОГДА 'M3'
КОГДА 3, ТО 'M4'
КОНЕЦ КАК М
ИЗ данных
) т
ВРАЩАТЬСЯ (
МАКС(Информация)
ДЛЯ M IN ([M1], [M2], [M3], [M4])
) п
Зависит от того, какую базу данных SQL вы используете.
Кроме того, в вашей таблице A отсутствует столбец, содержащий «M1», «M2» и т. д. В приведенном ниже решении я ожидаю, что структура данных будет следующей:
ModuleId | RuModules_Id | Информация М1 | 9 | 1,1,49
Если вы используете SQL Server, вы можете выполнить сводку.
Синтаксис примерно такой:
SELECT * FROM (
ВЫБИРАТЬ
[ModuleId]
[RuModules_Id],
[Информация]
ИЗ
) A_sub
ВРАЩАТЬСЯ (
МАКС([Информация])
ДЛЯ [ModuleId]
В (
[М1],
[М2],
[М3],
[М4]
)
) КАК сводная таблица
В приведенном выше примере (не проверенном) будут построены столбцы M1,M2,M3,M4, добавлена строка для каждого уникального значения, найденного в RuModules_Id, и в каждой продаже максимальное значение, найденное в [Info] для конкретной комбинации ModuleId и RuModules_Id будут перечислены.
Невозможно выполнить динамическое решение с необработанным SQL. Вам нужно знать, какие именно столбцы вы создаете (в нашем примере мы создали M1, M2, M3, M4.