Сложный запрос с PIVOT

@IlyyaNeustroev · Регистрация: 10.04.2016

Студворк — интернет-сервис помощи студентам

Имеется представление которое выдает необходимый набор строк:

T-SQL
1
2
3
4
5
6
7
8
9
10
SELECT      dbo.beton_uchet.date_b, dbo.beton.name_b, dbo.typebeton.nametypebeton, dbo.beton_uchet.id_mark, SUM(dbo.beton_uchet.razl) AS sumrazl, SUM(dbo.beton_uchet.snyto_godn) AS sumsnytogodn, SUM(dbo.beton_uchet.snyto_brak) 
                         AS sumsnytobrak, SUM(dbo.beton_uchet.ad) AS sumad, SUM(dbo.beton_uchet.sort_godn) AS sumsortgodn, SUM(dbo.beton_uchet.sort_brak) AS sumsortbrak, SUM(dbo.beton_uchet.shl_godn) AS sumshlgodn, 
                         SUM(dbo.beton_uchet.shl_brak) AS sumshlbrak, SUM(dbo.beton_uchet.sdano) AS sumsdano, 
                         SUM(dbo.beton_uchet.sdano_t) AS sumsdanot
FROM            dbo.beton_uchet INNER JOIN
                         dbo.beton ON dbo.beton_uchet.id_mark = dbo.beton.id INNER JOIN
                         dbo.typebeton ON dbo.beton.id_type = dbo.typebeton.id INNER JOIN
                         dbo.user_b ON dbo.beton_uchet.id_master = dbo.user_b.id
GROUP BY dbo.beton_uchet.date_b, dbo.beton_uchet.id_mark, dbo.beton_uchet.smena, dbo.user_b.fio, dbo.beton.name_b, dbo.typebeton.nametypebeton
ORDER BY dbo.beton.name_b, dbo.beton_uchet.date_b, dbo.beton_uchet.smena

В итоге имеем набор данных упорядоченный построчно по дате (см. рис.1). Необходимо транспонировать строки таким образом, чтобы показатели выводились по возрастанию даты (числам месяца) справа от наименования и типа (см. рис.2). Так как даты выбираются из диапазона, то необходим динамический PIVOT. Помогите кто разбирается. Спасибо.

@IlyyaNeustroev · 05.10.2022, 08:02 **[ТС]**

На втором рисунке ошибка, разумеется даты возрастают 01.09.2022, 02.09.2022, 03.09.2022 и т.д.

@IlyyaNeustroev · 06.10.2022, 09:10 **[ТС]**

SQL
1
2
3
4
5
6
SELECT name_b, nametypebeton , [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]
FROM (
SELECT        date_b, smena, sumrazl, sumsnytogodn, sumsnytobrak, sumad, sumsortgodn, sumsortbrak, sumshlgodn, sumshlbrak, sumsdano, fio, name_b, nametypebeton, id_mark, sumsdanot
FROM            dbo.ViewSumBetonUchet
WHERE        (MONTH(date_b) = 08)) AS databeton
PIVOT (SUM(sumrazl) FOR DAY(date_b) IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])) AS testpivot

Пытаюсь выполнить транспонирование по одному столбцу (значение "sumrazl"). Выдаёт ошибку (см. рис.). У меня в качестве столбца по которому выполняется транспонирование указана функция DAY, которая возвращает число месяца (что мне и нужно). Ругается на это (видимо нельзя использовать функцию в этом месте). Как тогда можно это реализовать?

@PaulWist · 06.10.2022, 09:37

Сообщение от IlyyaNeustroev

У меня в качестве столбца по которому выполняется транспонирование указана функция DAY,

Хелп

FOR
[<column that contains the values that will become column headers>]

@IlyyaNeustroev · 06.10.2022, 12:23 **[ТС]**

T-SQL
1
2
3
4
5
6
SELECT name_b, nametypebeton , [2022-01-08],[2022-02-08],[2022-03-08],[2022-04-08],[2022-05-08],[2022-06-08],[2022-07-08],[2022-08-08],[2022-09-08],[2022-10-08],[2022-11-08],[2022-12-08],[2022-13-08],[2022-14-08],[2022-15-08],[2022-16-08],[2022-17-08],[2022-18-08],[2022-19-08],[2022-20-08],[2022-21-08],[2022-22-08],[2022-23-08],[2022-24-08],[2022-25-08],[2022-26-08],[2022-27-08],[2022-28-08],[2022-29-08],[2022-30-08],[2022-31-08]
FROM (
SELECT        date_b, smena, sumrazl, sumsnytogodn, sumsnytobrak, sumad, sumsortgodn, sumsortbrak, sumshlgodn, sumshlbrak, sumsdano, fio, name_b, nametypebeton, id_mark, sumsdanot
FROM            dbo.ViewSumBetonUchet
WHERE        (MONTH(date_b) = 08)) AS databeton
PIVOT (SUM(sumrazl) for date_b in ([2022-01-08],[2022-02-08],[2022-03-08],[2022-04-08],[2022-05-08],[2022-06-08],[2022-07-08],[2022-08-08],[2022-09-08],[2022-10-08],[2022-11-08],[2022-12-08],[2022-13-08],[2022-14-08],[2022-15-08],[2022-16-08],[2022-17-08],[2022-18-08],[2022-19-08],[2022-20-08],[2022-21-08],[2022-22-08],[2022-23-08],[2022-24-08],[2022-25-08],[2022-26-08],[2022-27-08],[2022-28-08],[2022-29-08],[2022-30-08],[2022-31-08])) AS testpivot

Так работает. Однако, вопрос остаётся. Как задать даты динамически? Кроме того, непонятно как выполнить транспонирование по нескольким полям (не только по "sumrazl")?

@pincet · 06.10.2022, 14:52

Сообщение от IlyyaNeustroev

Как задать даты динамически? Кроме того, непонятно как выполнить транспонирование по нескольким полям (не только по "sumrazl")?

это уже динамика
если кровь из носу нужно на сиквел стороне - встречай траблы
яп рассмотрел
- на клиенте разбирать исходный рекордсет (сильно зависит от того, кто клиент)

@IlyyaNeustroev · 07.10.2022, 08:33 **[ТС]**

Написал скрипт формирующий необходимый запрос:

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
use beton;
 
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
 
select @cols = STUFF((SELECT ',' + QUOTENAME(convert(char(10), dbo.ViewSumBetonUchet.date_b, 120)) 
                    from dbo.ViewSumBetonUchet
                    group by date_b
                    order by date_b asc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
 
set @query = 'SELECT name_b, nametypebeton, id_mark,
                    smena, fio,' + @cols + ' from dbo.ViewSumBetonUchet
     
            pivot 
            (
                sum(sumrazl)
                for date_b in (' + @cols + ')
            ) AS testpivot ORDER BY nametypebeton, name_b, smena'
 
/*print @query*/
execute(@query)

Сам запрос, если его распечатать выглядит следующим образом:

T-SQL
1
2
3
4
5
6
7
SELECT name_b, nametypebeton, id_mark,
                    smena, fio,[2022-08-01],[2022-08-02],[2022-08-03],[2022-08-04],[2022-08-05],[2022-08-06],[2022-08-07],[2022-08-08],[2022-08-09],[2022-08-10],[2022-08-11],[2022-08-12],[2022-08-13],[2022-08-14],[2022-08-15],[2022-08-16],[2022-08-17],[2022-08-18],[2022-08-19],[2022-08-20],[2022-08-21],[2022-08-22],[2022-08-23],[2022-08-24],[2022-08-25],[2022-08-26],[2022-08-27],[2022-08-28],[2022-08-29],[2022-08-30],[2022-08-31],[2022-09-16] from dbo.ViewSumBetonUchet
            pivot 
            (
                sum(sumrazl)
                for date_b in ([2022-08-01],[2022-08-02],[2022-08-03],[2022-08-04],[2022-08-05],[2022-08-06],[2022-08-07],[2022-08-08],[2022-08-09],[2022-08-10],[2022-08-11],[2022-08-12],[2022-08-13],[2022-08-14],[2022-08-15],[2022-08-16],[2022-08-17],[2022-08-18],[2022-08-19],[2022-08-20],[2022-08-21],[2022-08-22],[2022-08-23],[2022-08-24],[2022-08-25],[2022-08-26],[2022-08-27],[2022-08-28],[2022-08-29],[2022-08-30],[2022-08-31],[2022-09-16])
            ) AS testpivot ORDER BY nametypebeton, name_b, smena

При его выполнении возникает ошибка:

Сообщение 8114, уровень 16, состояние 1, строка 7
Ошибка при преобразовании типа данных nvarchar к datetime.
Сообщение 473, уровень 16, состояние 1, строка 7
Неправильное значение "2022-08-13" содержится в операторе PIVOT.

Удаляем из запроса все даты после "2022-08-12" и все работает. В базе имеются данные после этой даты. Ошибки быть не должно. Кроме того выводятся не все данные. Во многих полях стоит значение null, а этого быть не должно. Непонятно с чем это может быть связано

@IlyyaNeustroev · 07.10.2022, 08:46 **[ТС]**

Возможно агрегатная функция SUM, встречая слагаемое со значением NULL, всё результирующее значение об-NULL-яет. Пробовал так, не работает:

T-SQL
1
sum(isnull(sumrazl,0))

Откуда там вообще взяться NULL, если у меня в исходных таблицах в качестве значения по умолчанию указаны нули?

@PaulWist · 07.10.2022, 08:54

Сообщение от IlyyaNeustroev

Удаляем из запроса все даты после "2022-08-12" и все работает.

Что возвращает

T-SQL
1
SELECT @@LANGUAGE AS 'Language Name';

@IlyyaNeustroev · 07.10.2022, 08:59 **[ТС]**

русский

@qwertehok · 07.10.2022, 09:06

месяц с днем перепутаны
поэтому ошибка и поэтому данные есть только за 08.08

@PaulWist · 07.10.2022, 09:26

Сообщение от IlyyaNeustroev

русский

T-SQL
1
set dateformat ymd;

затем запрос с pivot.

@IlyyaNeustroev · 07.10.2022, 09:55 **[ТС]**

Сообщение от PaulWist

T-SQL
1
set dateformat ymd;

затем запрос с pivot.

Спасибо. Осталось понять как другие поля вывести в рамках этого PIVOT (не только "sumrazl")

@invm · 07.10.2022, 10:37

Сообщение от IlyyaNeustroev

Осталось понять как другие поля вывести в рамках этого PIVOT (не только "sumrazl")

Никак.
pivot - просто синтаксический сахар. Генерируйте динамически обычный запрос нужного вида.

@IlyyaNeustroev · 11.10.2022, 08:42 **[ТС]**

Написал запрос. Он рабочий, возвращает 10 таблиц с одинаковыми именами столбцов. Как связать эти 10 таблиц в одну у которых данные в столбцах с датами отличаются. Понимаю, что нужно задать псевдонимы для имен столбцов, однако не знаю как это сделать в рамках pivot, ведь они транспонируются по заданным именам столбцов.

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
use beton;
 
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @query2  AS NVARCHAR(MAX),
    @query3  AS NVARCHAR(MAX),
    @query4  AS NVARCHAR(MAX),
    @query5  AS NVARCHAR(MAX),
    @query6  AS NVARCHAR(MAX),
    @query7  AS NVARCHAR(MAX),
    @query8  AS NVARCHAR(MAX),
    @query9  AS NVARCHAR(MAX)
 
 
select @cols = STUFF((SELECT ',' + QUOTENAME(convert(nvarchar(10), ViewSumBetonUchet.date_b, 120)) 
                    from ViewSumBetonUchet
                    WHERE (date_b >= '2022 - 08 - 01') AND (date_b <= '2022 - 08 - 31')
                    group by date_b
                    order by date_b asc
                    
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
 
set dateformat ymd
 
 
set @query = 'SELECT id, name_b, nametypebeton, id_mark,
                    smena, fio,' + @cols + ' from dbo.ViewSumBetonUchet
     
            pivot 
            (
                sum(sumrazlsht) 
                for date_b in (' + @cols +')
            ) AS testpivot ORDER BY nametypebeton, name_b, smena'
 
set @query2 = 'SELECT id, name_b, nametypebeton, id_mark,
                    smena, fio,' + @cols + ' from dbo.ViewSumBetonUchet
     
            pivot 
            (
                sum(sumrazltn) 
                for date_b in (' + @cols + ')
            ) AS testpivot ORDER BY nametypebeton, name_b, smena'
 
set @query3 = 'SELECT id, name_b, nametypebeton, id_mark,
                    smena, fio,' + @cols + ' from dbo.ViewSumBetonUchet
     
            pivot 
            (
                sum(sumbraksht) 
                for date_b in (' + @cols + ')
            ) AS testpivot ORDER BY nametypebeton, name_b, smena'
set @query4 = 'SELECT id, name_b, nametypebeton, id_mark,
                    smena, fio,' + @cols + ' from dbo.ViewSumBetonUchet
     
            pivot 
            (
                sum(sumbraktn) 
                for date_b in (' + @cols + ')
            ) AS testpivot ORDER BY nametypebeton, name_b, smena'
 
set @query5 = 'SELECT id, name_b, nametypebeton, id_mark,
                    smena, fio,' + @cols + ' from dbo.ViewSumBetonUchet
     
            pivot 
            (
                sum(sumshlgodntn) 
                for date_b in (' + @cols + ')
            ) AS testpivot ORDER BY nametypebeton, name_b, smena'
set @query6 = 'SELECT id, name_b, nametypebeton, id_mark,
                    smena, fio,' + @cols + ' from dbo.ViewSumBetonUchet
     
            pivot 
            (
                sum(sumshlgodnsht) 
                for date_b in (' + @cols + ')
            ) AS testpivot ORDER BY nametypebeton, name_b, smena'
 
set @query7 = 'SELECT id, name_b, nametypebeton, id_mark,
                    smena, fio,' + @cols + ' from dbo.ViewSumBetonUchet
     
            pivot 
            (
                sum(sumadsht) 
                for date_b in (' + @cols + ')
            ) AS testpivot ORDER BY nametypebeton, name_b, smena'
set @query8 = 'SELECT id, name_b, nametypebeton, id_mark,
                    smena, fio,' + @cols + ' from dbo.ViewSumBetonUchet
     
            pivot 
            (
                sum(sumsnytogodnsht) 
                for date_b in (' + @cols + ')
            ) AS testpivot ORDER BY nametypebeton, name_b, smena'
set @query9 = 'SELECT id, name_b, nametypebeton, id_mark,
                    smena, fio,' + @cols + ' from dbo.ViewSumBetonUchet
     
            pivot 
            (
                sum(sumsortgodnsht) 
                for date_b in (' + @cols + ')
            ) AS testpivot ORDER BY nametypebeton, name_b, smena'
 
/*print @query*/
execute(@query)
execute(@query2)
execute(@query3)
execute(@query4)
execute(@query5)
execute(@query6)
execute(@query7)
execute(@query8)
execute(@query9)

Добавлено через 1 минуту
Это запрос я планирую прописывать в ADOQuery Delphi, где в качестве диапазона дат вставлять выбранные пользователям значения периода

@PaulWist · 11.10.2022, 09:06

T-SQL
1
2
3
4
5
6
7
set @query =
@query
+ N'union all '
+ @query1
+ N'union all '
+ @query2
+....

@invm · 11.10.2022, 11:30

Сообщение от IlyyaNeustroev

Как связать эти 10 таблиц в одну у которых данные в столбцах с датами отличаются. Понимаю, что нужно задать псевдонимы для имен столбцов, однако не знаю как это сделать в рамках pivot, ведь они транспонируются по заданным именам столбцов.

Писал же: pivot - синтаксический сахар. Но мы не ищем легких путей...

Вот как получить желаемое без pivot

T-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
declare @t table (n varchar(10), d date, v1 int, v2 int);
insert into @t
values
 ('a', '2022-01-01', 1, 1), ('a', '2022-01-01', 2, 2), ('a', '2022-01-02', 10, 20)
 , ('b', '2022-01-01', -1, -1), ('b', '2022-01-01', -2, -2), ('b', '2022-01-03', 100, 200);
 
select
 n
 , sum(case when d = '2022-01-01' then v1 end) as [2022-01-01 v1]
 , sum(case when d = '2022-01-01' then v2 end) as [2022-01-01 v2]
 , sum(case when d = '2022-01-02' then v1 end) as [2022-01-02 v1]
 , sum(case when d = '2022-01-02' then v2 end) as [2022-01-02 v2]
 , sum(case when d = '2022-01-03' then v1 end) as [2022-01-03 v1]
 , sum(case when d = '2022-01-03' then v2 end) as [2022-01-03 v2]
from
 @t
where d in ('2022-01-01', '2022-01-02', '2022-01-03')
group by n;

Формируйте подобное динамически

Новые блоги и статьи Все статьи Все блоги /
http://iceja.net/ математические сервисы iceja 20.01.2026 Обновила свой сайт http:/ / iceja. net/ , приделала Fast Fourier Transform экстраполяцию сигналов. Однако предсказывает далеко не каждый сигнал (см ограничения http:/ / iceja. net/ fourier/ docs ). Также. . .	http://iceja.net/ сервер решения полиномов iceja 18.01.2026 Выкатила http:/ / iceja. net/ сервер решения полиномов (находит действительные корни полиномов методом Штурма). На сайте документация по API, но скажу прямо VPS слабенький и 200 000 полиномов. . .	Расчёт переходных процессов в цепи постоянного тока igorrr37 16.01.2026 / * Дана цепь постоянного тока с R, L, C, k(ключ), U, E, J. Программа составляет систему уравнений по 1 и 2 законам Кирхгофа, решает её и находит переходные токи и напряжения на элементах схемы. . . .	Восстановить юзерскрипты Greasemonkey из бэкапа браузера damix 15.01.2026 Если восстановить из бэкапа профиль Firefox после переустановки винды, то список юзерскриптов в Greasemonkey будет пустым. Но восстановить их можно так. Для этого понадобится консольная утилита. . .
Сукцессия микоризы: основная теория в виде двух уравнений. anaschu 11.01.2026 https:/ / rutube. ru/ video/ 7a537f578d808e67a3c6fd818a44a5c4/	WordPad для Windows 11 Jel 10.01.2026 WordPad для Windows 11 — это приложение, которое восстанавливает классический текстовый редактор WordPad в операционной системе Windows 11. После того как Microsoft исключила WordPad из. . .	Classic Notepad for Windows 11 Jel 10.01.2026 Old Classic Notepad for Windows 11 Приложение для Windows 11, позволяющее пользователям вернуть классическую версию текстового редактора «Блокнот» из Windows 10. Программа предоставляет более. . .	Почему дизайн решает? Neotwalker 09.01.2026 В современном мире, где конкуренция за внимание потребителя достигла пика, дизайн становится мощным инструментом для успеха бренда. Это не просто красивый внешний вид продукта или сайта — это. . .

@IlyyaNeustroev 2 / 2 / 0 Регистрация: 10.04.2016 Сообщений: 73
	05.10.2022, 08:02 [ТС]
	На втором рисунке ошибка, разумеется даты возрастают 01.09.2022, 02.09.2022, 03.09.2022 и т.д. 0

@IlyyaNeustroev 2 / 2 / 0 Регистрация: 10.04.2016 Сообщений: 73
	07.10.2022, 08:59 [ТС]
	русский 0

@qwertehok 5962 / 4538 / 1094 Регистрация: 29.08.2013 Сообщений: 28,148 Записей в блоге: 3
	07.10.2022, 09:06
	месяц с днем перепутаны поэтому ошибка и поэтому данные есть только за 08.08 1