Как транспонировать несколько столбцов в несколько строк?
Сложно сформулировать, покажу на примере.
В таблице несколько полей с датами и им соответствуют поля со значениями, но мне не нужно, чтобы для каждого типа значений(val1, val2 и тд) были отдельные столбцы в выгрузке, мне нужно, чтобы был один столбец с названием значения и один со значениями, поэтому, я написал запрос:
select val1_date as date, 'val1' as val_name, count(val1) as value from . group by 1,2 union select val2_date as date, 'val2' as val_name, count(val2) as value from . group by 1,2
Получаем соответственно:
А вопрос собственно в том, как это написать нормально, без использования юнион, одним запросом.
- Вопрос задан более трёх лет назад
- 1412 просмотров
4 комментария
Простой 4 комментария
Как транспонировать результаты sql-запроса
А вопрос заключается в том как этот результат повернуть, либо как написать правильный запрос. Ковырялся с UNION и с PIVOT не получилось.
Отслеживать
задан 31 июл 2015 в 9:45
659 1 1 золотой знак 7 7 серебряных знаков 20 20 бронзовых знаков
Подозреваю, что это гораздо труднее, чем кажется с первым взглядом.
31 июл 2015 в 9:50
используете mysql?
31 июл 2015 в 9:57
Тестовое задание на позицию Junior Developer. Видимо подразумевается независимость от субд. Остальные задачи были простыми. Я использую Firebird и MS.
31 июл 2015 в 9:57
31 июл 2015 в 10:17
Сейчас попробую
31 июл 2015 в 10:26
3 ответа 3
Сортировка: Сброс на вариант по умолчанию
Используйте CASE или PIVOT. Здесь, фактически, ваш случай.
Отслеживать
ответ дан 31 июл 2015 в 11:02
11.5k 16 16 серебряных знаков 16 16 бронзовых знаков
при помощи интернета и какой-то. получилось такое. Это только для MySQL, как я понимаю, из-за group_concat. Но, может, чем поможет
drop table if exists t1; create table t1 (id int, value char(1)); insert into t1 values (2, 'a'), (3, 'a'), (4, 'b'), (5, 'c'); select group_concat(if(v='a', c, null)) a, group_concat(if(v='b', c, null)) b, group_concat(if(v='c', c, null)) c from (select value v, Count(value) c from t1 group by value ) temp a b c 2 1 1 Отслеживать
ответ дан 31 июл 2015 в 10:41
16.4k 2 2 золотых знака 15 15 серебряных знаков 24 24 бронзовых знака
да, group_concat в ms sql нету. Читал про него когда гуглил решение.
31 июл 2015 в 10:55
Не претендую на изящность решения, но вот вариант с курсором:
DECLARE @T2 table (id int, value char(1)) INSERT INTO @T2 values (2, 'a'), (3, 'a'), (4, 'b'), (5, 'c') DECLARE @vals varchar(10) DECLARE @cnts varchar(10) DECLARE @v char(1) DECLARE @c int DECLARE @cur cursor SET @cur = cursor local for SELECT value, COUNT(value) FROM @T2 GROUP BY value OPEN @cur FETCH NEXT FROM @cur INTO @v, @c WHILE @@FETCH_STATUS = 0 BEGIN IF @vals IS NULL SET @vals = @v ELSE SET @vals = @vals + ' ' + @v IF @cnts IS NULL SET @cnts = CAST(@c as varchar(10)) ELSE SET @cnts = @cnts + ' ' + CAST(@c as varchar(10)) FETCH NEXT FROM @cur INTO @v, @c END CLOSE @cur DEALLOCATE @cur -- ну и собственно результат: SELECT @vals SELECT @cnts Частичное транспонирование таблицы
Добрый день.
подскажите как можно решить данную проблему:
по тех заданию бывшего сотрудника ДИТ сделал нам сводную таблицу всех заявок менеджеров, но в странном для меня формате, а именно:
визуально выглядит как будто часть таблицы размещена перпендикулярно и в итоге 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
Сообщений: 40
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
Транспонирование запроса в T-SQL
select zz.fio, MAX(st1) stat1, MAX(st2) stat2, MAX(st3) stat3
from
(select fio,
case when STAT=’Stat1′ then kolvo else NULL END as st1,
case when STAT=’Stat2′ then kolvo else NULL END as st2,
case when STAT=’Stat3′ then kolvo else NULL END as st3
from
(
select fio, STAT, COUNT(*) kolvo
from dbo.Table_3
group by fio, stat
) z
) zz
group by fio
Решение 2 (сжали до двух ходов):
WITH fioStat(fio, stat, kolvo) as (
select fio, STAT, COUNT(*) kolvo
from dbo.Table_3
group by fio, stat
)
SELECT fio,
max(case when STAT=’Stat1′ then kolvo else NULL END) as st1,
max(case when STAT=’Stat2′ then kolvo else NULL END) as st2,
max(case when STAT=’Stat3′ then kolvo else NULL END) as st3
from fioStat
group by fio
Решение 3 (с подсчетом сумм):
WITH fioStat(fio, stat, kolvo) as (
select fio, STAT, COUNT(*) kolvo
from dbo.Table_3
group by fio, stat
)
SELECT fio,
max(case when STAT=’Stat1′ then kolvo else NULL END) as st1,
max(case when STAT=’Stat2′ then kolvo else NULL END) as st2,
max(case when STAT=’Stat3′ then kolvo else NULL END) as st3
from fioStat
group by fio
union
select ‘Total:’,
sum(case when STAT=’Stat1′ then kolvo else NULL END) as st1,
sum(case when STAT=’Stat2′ then kolvo else NULL END) as st2,
sum(case when STAT=’Stat3′ then kolvo else NULL END) as st3
from fioStat
fio st1 st2 st3
Den 3 2 1
Paul 4 2 2
Total: 7 4 3
* Работает для заранее известного числа столбцов.