Как транспонировать таблицу в sql

Частичное транспонирование таблицы

Добрый день.
подскажите как можно решить данную проблему:

по тех заданию бывшего сотрудника ДИТ сделал нам сводную таблицу всех заявок менеджеров, но в странном для меня формате, а именно:
визуально выглядит как будто часть таблицы размещена перпендикулярно и в итоге select-ом по ID я получаю огромный набор строк

id	date	столбец1	столбец2
1	03.03.2022	фио	иванов сергей
1	03.03.2022	проверил	ББ
1	03.03.2022	дата монтажа	04.03.2022
1	03.03.2022	поставщик	ООО АМАР

возможно ли развернуть часть таблицы или вывести данные каким то другим способом в строку?

сейчас с помощь where по Столбцам , я могу получить только одно значение

1

03.03.2022

фио

иванов сергей

а для анализа хочется видеть данные вот так:

id	date	фио	проверил	дата монтажа	поставщик
1	03.03.2022	иванов сергей	ББ	04.03.2022	ООО АМАР

заранее спасибо за любую консультацию.

94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Транспонирование нескольких строк/столбцов таблицы при использовании динамического SQL
Доброго времени суток! Просьба помочь с написанием скрипта для транспонирования таблицы с.

Частичное транспонирование таблицы
Доброго времени суток, форумчане! Имеется таблица с ID товаров и таблица со значением свойств.

Транспонирование таблицы
Есть внешний отчет, который выдает следующий результат. Нужно преобразовать таблицу(провести.

Транспонирование таблицы БД
Дана известная таблица в БД, размера 3 на 3. Транспонировать её (поменять местами столбцы и строки).

Транспонирование таблицы
Добрый день! Имеется таблица в неудобном виде: Наименование Булка Склад школы 3 Склад.

Регистрация: 21.03.2022
Сообщений: 41

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

CREATE TABLE #tt ( id INT , date_ DATE, s1 VARCHAR(50) , s2 VARCHAR(50), ) INSERT #tt (id,date_,s1,s2) VALUES( 1,'20220303','фио','иванов сергей'), (1,'20220303','проверил','ББ'), (1,'20220303','дата монтажа','04.03.2022'), (1,'20220303','поставщик','ООО АМАР') SELECT id,date_, (SELECT s2 FROM #tt t2 WHERE t2.s1 = 'фио' AND t1.id = t2.id AND t1.date_ = t2.date_ ) AS 'фио', (SELECT s2 FROM #tt t3 WHERE t3.s1 = 'проверил' AND t1.id = t3.id AND t1.date_ = t3.date_ ) AS 'проверил', (SELECT s2 FROM #tt t4 WHERE t4.s1 = 'дата монтажа' AND t1.id = t4.id AND t1.date_ = t4.date_ ) AS 'дата монтажа', (SELECT s2 FROM #tt t5 WHERE t5.s1 = 'поставщик' AND t1.id = t5.id AND t1.date_ = t5.date_ ) AS 'поставщик' FROM #tt t1 GROUP BY id,date_

Регистрация: 14.09.2015
Сообщений: 104

спасибо за идею.
ограничился одним запросом с вложенными подзапросами

1 2 3 4 5 6

SELECT top (1) id,date_, (SELECT [столбец1] FROM table1 WHERE [столбец1] = 'фио' AND Id = 1 ) AS 'фио', (SELECT [столбец1] FROM table1 WHERE [столбец1] = 'проверил' AND Id = 1 ) AS 'проверил', (SELECT [столбец1] FROM table1 WHERE [столбец1] = 'дата монтажа' AND Id = 1 ) AS 'дата монтажа', (SELECT [столбец1] FROM table1 WHERE [столбец1] = 'поставщик' AND Id = 1) AS 'поставщик' FROM table1

Транспонирование таблицы SQL: какой запрос может в этом помочь

Транспонирование таблицы SQL — это специфическое преобразование таблицы, после которого столбцы становятся строками, а строки — столбцами. Для такой манипуляции существуют специальные конструкции запросов, где используются операторы PIVOT и UNPIVOT. Использование этих операторов имеет довольно специфический синтаксис , н о с точки зрения эффективност и о ни отлично справляются с возложенной на них задачей.

Эти операторы хороши тем, что могут транспонировать небольшие данные, например , только одну строку или столбец таблицы. Но они также могут транспонировать всю таблицу целиком, то есть все столбцы со всеми строками.

Можно ли транспонировать таблицу SQL другими способами? Да, можно, но это будут сложные самодельные конструкции. Нет смысла заострять на них свое внимание, если для транспонирования присутствуют специальные операторы.

Транспонирование таблицы SQL

Чтобы лучше понять, как происходит транспонирование таблицы SQL, давайте разберем это действие на практике. Представим, что у вас есть некая SQL-таблица вот такого вида:

Дормидонт	Платон	Тимати	Аристарх
Красный	3	7	3	5
Зеленый	10	6	5	7
Голубой	4	4	11	3

Вам нужно транспонировать эту таблицу SQL таким образом, чтобы из нее получилась такая:

Красный	Зеленый	Голубой
Дормидонт	3	10	4
Платон	7	6	4
Тимати	3	5	11
Аристарх	5	7	3

Когда вы создаете такую таблицу в SQL, код будет следующий:

CREATE TABLE transTable([color] varchar(5), [Дормидонт] int, [Платон] int, [Тимати] int, [Аристарх] int);

INSERT INTO transTable

([color], [Дормидонт], [Платон], [Тимати], [Аристарх])

VALUES

(‘Красный’, 3, 7, 3, 5),

(‘Зеленый’, 10, 6, 5, 7),

(‘Голубой’, 4, 4, 11, 3);

Транспонируем нашу SQL-таблицу с помощью специальной конструкции с применением операторов «pivot» и «unpiv o t». Код будет следующим:

select name, [Красный], [Зеленый], [Голубой]

from

(

select color, name, value

from transtable

unpivot

(

value for name in (Дормидонт, Платон, Тимати, Аристарх)

) unpiv

) src

pivot

(

sum(value)

for color in ([Красный], [Зеленый], [Голубой])

) piv

Заключение

Транспонирование таблицы SQL требуется достаточно редко. Транспонирование можно описать как процесс перевода столбцов в строки. Сегодня мы показали простейший, но эффективный способ, как транспонировать SQL-таблицу при помощи специальных операторов «pivot» и «unpivot».

Мы будем очень благодарны

если под понравившемся материалом Вы нажмёте одну из кнопок социальных сетей и поделитесь с друзьями.

Транспонирование таблицы SQL

Очень даже имеет. При таком раскладе придется не просто «транспонировать содержимое» (что можно было бы сделать довольно просто при небольшом объеме данных и без необходимости часто повторять операцию; ну, впрочем, это Вам уже написали), а менять структуру таблицы. Ведь в новой таблице имена колонок (кроме 0:0 ) станут содержимым, а часть содержимого — заголовком колонки.

Получится, на входе у нас есть таблица

id | key | value ---+----------+--------- 1 | someKey1 | someVal1 2 | someKey2 | someVal2 3 | someKey3 | someVal3

из нее получаем

id | 1 | 2 | 3 ------+----------+----------+--------- key | someKey1 | someKey2 | someKey3 value | someVal1 | someVal2 | someVal3

TABLE someTable( 'id' , 'key' , 'value' ) 

TABLE someTable( 'id' , '1' , '2' , '3' ) 

Кажется, так сложностей стало чуть побольше, Вы так не считаете?

Как транспонировать в sql

Для транспонирования таблицы в SQL необходимо использовать оператор PIVOT или функцию MAX или CASE в сочетании с оператором GROUP BY .

Оператор PIVOT позволяет преобразовать строки в столбцы, а столбцы — в строки, используя значения одного столбца в качестве заголовков новых столбцов.

Синтаксис оператора PIVOT выглядит следующим образом:

SELECT * FROM table_name PIVOT ( aggregate_function(column_to_aggregate) FOR column_to_pivot IN (list_of_pivot_values) ) AS alias_name; 

Здесь table_name — это имя таблицы, которую нужно транспонировать aggregate_function — это агрегатная функция, которую нужно применить к столбцу, column_to_aggregate — это имя столбца, который нужно агрегировать, column_to_pivot — это имя столбца, который нужно использовать для создания новых столбцов, list_of_pivot_values — это список значений столбца column_to_pivot, для которых нужно создать новые столбцы, alias_name — это имя для результирующей таблицы

Пример использования оператора PIVOT :

SELECT * FROM ( SELECT product_id, year, sales FROM sales_table ) AS source_table PIVOT ( SUM(sales) FOR year IN (2018, 2019, 2020) ) AS pivot_table; 

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

Если оператор PIVOT недоступен в вашей версии SQL, вы можете использовать функцию MAX или CASE в сочетании с оператором GROUP BY .

Синтаксис функции MAX для транспонирования таблицы выглядит следующим образом:

SELECT column_to_group_by, MAX(CASE column_to_pivot WHEN pivot_value_1 THEN value_to_show ELSE NULL END) AS pivot_value_1, MAX(CASE column_to_pivot WHEN pivot_value_2 THEN value_to_show ELSE NULL END) AS pivot_value_2, . FROM table_name GROUP BY column_to_group_by; 

Здесь column_to_group_by — это имя столбца, по которому нужно группировать данные, column_to_pivot — это имя столбца, который нужно использовать для создания новых столбцов, pivot_value_1 , pivot_value_2 — это значения столбца column_to_pivot , для которых нужно создать новые столбцы, value_to_show — это значение, которое нужно показать в новом столбце