Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
Добрый день,

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

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.

Спасибо.
16 окт 19, 07:55    [21995091]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
Я так понимаю, тут нужен PIVOT. Вот что у меня пока получилось (выдает синтаксическую ошибку):
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;
16 окт 19, 09:10    [21995122]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20189
PIVOT не нужен. Для конкатенации в группе используют STRING_AGG(), если версия сервера позволяет, либо трик через FOR XML.
16 окт 19, 09:17    [21995129]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
Akina
PIVOT не нужен. Для конкатенации в группе используют STRING_AGG(), если версия сервера позволяет, либо трик через FOR XML.


У меня все-таки получилось с PIVOT, но только для заранее известных значений кодов ошибок:
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?
16 окт 19, 09:26    [21995138]     Ответить | Цитировать Сообщить модератору
 Re: помогите написать запрос  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1090
Akina
PIVOT не нужен. Для конкатенации в группе используют STRING_AGG(), если версия сервера позволяет, либо трик через FOR XML.


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

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 
16 окт 19, 09:33    [21995150]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить