Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / помогите написать запрос / 5 сообщений из 5, страница 1 из 1
16.10.2019, 07:55
    #39876839
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите написать запрос
Добрый день,

Есть две таблицы:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE TABLE [dbo].[temp_student](
	[id] bigint NOT NULL,
	[dob] [datetime] NULL,
	[first_name] [varchar](512) NOT NULL,
	[last_name] [varchar](512) NOT NULL,
	[gender] [varchar](1) NULL,
	[class] [varchar](255) NULL,
	[status] [varchar](32) NULL,
	[validation_messages] [varchar](1024) NULL
) ON [PRIMARY]

CREATE TABLE  validation_results (
	id bigint,
	error_code int,
	message varchar(256)
)




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

Каждый студент может иметь ноль, одну или более одной ошибок.

Мне нужно написать запрос (желательно не прибегая к циклам), который запишет в таблицу temp_student в поле validation_messages все ошибки из таблицы validation_results (если они есть) или "ОК" если их нет. То есть если у студента в данных две ошибки, то эти строки нужно слепить вместе и засунуть в поле validation_messages. Две таблицы связаны по полю id.

Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2019, 09:10
    #39876858
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите написать запрос
Я так понимаю, тут нужен PIVOT. Вот что у меня пока получилось (выдает синтаксическую ошибку):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select 'messages' as message, 
[1], [2], [3], [4]
from 
(select error_code, [message] from tmp.validation_results) as source_table 
pivot 
(
[message]
FOR error_code IN ([1], [2], [3], [4])
) as PivotTable;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2019, 09:17
    #39876864
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите написать запрос
PIVOT не нужен. Для конкатенации в группе используют STRING_AGG(), если версия сервера позволяет, либо трик через FOR XML.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2019, 09:26
    #39876871
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите написать запрос
AkinaPIVOT не нужен. Для конкатенации в группе используют STRING_AGG(), если версия сервера позволяет, либо трик через FOR XML.

У меня все-таки получилось с PIVOT, но только для заранее известных значений кодов ошибок:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
update [tmp].[temp_student] set [validation_messages] = ISNULL(a.concat_messages, 'OK')
from [tmp].[temp_student] s left join (
select id, ISNULL([1], '') + '/' + ISNULL([2], '') + '/' +  ISNULL([3], '') + '/' +  ISNULL([4], '') concat_messages
from 
(select id, error_code, [message] from tmp.validation_results) as source_table 
pivot 
(
MIN([message])
FOR error_code IN ([1], [2], [3], [4])
) as PivotTable) a
on s.id = a.id



Вот результат на тестовых данных:
id dob first_name last_name gender class status validation_messages
1 01.01.2010 A W 2 2 enrolled duplicate student/age out of boundaries/wrong class/
2 01.01.2011 B X 2 2 enrolled duplicate student/age out of boundaries/wrong class/
3 01.01.2012 C Y 2 2 enrolled duplicate student/age out of boundaries//
4 01.01.2013 A Z 1 1 enrolled duplicate student///
5 01.01.2014 B W 2 1 enrolled duplicate student///
6 01.01.2015 C X 1 1 enrolled OK
7 01.01.2016 A Y 2 1 enrolled OK
8 01.01.2017 B Z 2 1 enrolled OK
9 01.01.2018 C W 1 1 enrolled OK
10 01.01.2019 A X 2 2 enrolled OK


Было бы неплохо сделать этот запрос более динамическим для любых наборов кодов ошибок включая которые появятся в будущем.

Пошел читать про STRING_AGG()

А как это можно реализовать через FOR XML?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.10.2019, 09:33
    #39876877
Roust_m
Roust_m
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
помогите написать запрос
AkinaPIVOT не нужен. Для конкатенации в группе используют STRING_AGG(), если версия сервера позволяет, либо трик через FOR XML.

Спасибо большое, STRING_AGG() просто волшебно работает и не зависит от числа ошибок:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
update [tmp].[temp_student] set [validation_messages] = ISNULL(a.concat_messages, 'OK')
from [tmp].[temp_student] s left join (
SELECT
    id, 
    STRING_AGG([message],';') [concat_messages]
FROM
    tmp.validation_results
GROUP BY
    id) a
on s.id = a.id
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / помогите написать запрос / 5 сообщений из 5, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]