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

Откуда:
Сообщений: 11
Добрый день!
Есть 2 таблицы:
DECLARE @sers TABLE( [id] bigint, [name] varchar(100), [email] varchar(100) )
INSERT INTO @users VALUES
(1, 'Иванов Иван', 'ivanov@mail.ru'),
(2, 'Петров Петр', 'petrov@mail.ru')

DECLARE @events TABLE ([id] bigint, [name] varchar(100), data xml)
INSERT INTO @ events VALUES
(7, 'Лекция по философии',
'<users>
        <user>
                <user_id>1</user_id>
                <time>3</time>
        </user>
        <user>
                <user_id>2</user_id>
                <time>5</time>
        </user>
</users>')


Мне надо сделать выборку, чтобы получилось так:
7, 'Лекция по философии', 'Иванов Иван, Петров Петр ', 'ivanov@mail.ru, petrov@mail.ru '

Т.е. вывести информацию о событии и суммарную информацию о всех посетителях этого события.
Это кусок большого запроса, который я не смог осилить. :о(
Пожалуйста, не ругайте за структуру таблиц. Это давно работающий проект, в который мне нужно дописать код.
Спасибо.
14 июн 16, 22:21    [19293744]     Ответить | Цитировать Сообщить модератору
 Re: Сложение полей из XML выборки  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
DECLARE @users TABLE
(
    [id] BIGINT, [name] VARCHAR(100), [email] VARCHAR(100)
)
INSERT INTO @users VALUES
    (1, 'Иванов Иван', 'ivanov@mail.ru'),
    (2, 'Петров Петр', 'petrov@mail.ru')

DECLARE @events TABLE
(
    [id] BIGINT, [name] VARCHAR(100), data XML
)
INSERT INTO @events VALUES
    (7, 'Лекция по философии',
    '<users>
            <user>
                    <user_id>1</user_id>
                    <time>3</time>
            </user>
            <user>
                    <user_id>2</user_id>
                    <time>5</time>
            </user>
    </users>')

SELECT t.id
     , t.name
     , STUFF(CAST(x.query('a/text()') AS NVARCHAR(MAX)), 1, 2, '')
     , STUFF(CAST(x.query('b/text()') AS NVARCHAR(MAX)), 1, 2, '')
FROM @events t
OUTER APPLY (
    SELECT [a] = CASE WHEN t3.id = 'a' THEN ', ' + val END
         , [b] = CASE WHEN t3.id = 'b' THEN ', ' + val END
    FROM @users t2
    CROSS APPLY (
        VALUES ('a', t2.[name])
             , ('b', t2.[email])
    ) t3 (id, val)
    WHERE t.data.exist('users/user/user_id[text() = sql:column("t2.id")]') = 1
    FOR XML PATH(''), TYPE
) t2 (x)
14 июн 16, 22:37    [19293805]     Ответить | Цитировать Сообщить модератору
 Re: Сложение полей из XML выборки  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
select
 e.id, e.name,
 stuff(t.user_list, len(t.user_list), 1, ''),
 stuff(t.email_list, len(t.email_list), 1, '')
from
 @events e cross apply
 (
  select
   u.name + ',' as name, u.email + ',' as email
  from
   e.data.nodes('/users/user/user_id') eu(n) join
   @users u on u.id = eu.n.value('.', 'int')
  for xml path(''), type
 ) n(x) cross apply
 (select n.x.query('data(name)').value('.', 'varchar(max)'), n.x.query('data(email)').value('.', 'varchar(max)')) t(user_list, email_list);
14 июн 16, 22:51    [19293835]     Ответить | Цитировать Сообщить модератору
 Re: Сложение полей из XML выборки  [new]
sniffysko
Member

Откуда:
Сообщений: 11
Уважаемые AlanDenton и invm большое спасибо за ответы. То что нужно.
Пойду разбирать как они работают. :о)
15 июн 16, 09:09    [19294306]     Ответить | Цитировать Сообщить модератору
 Re: Сложение полей из XML выборки  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Малость вчера накрутил... Так будет проще:
SELECT t.id
     , t.name
     , STUFF(CAST(x.query('name/text()') AS NVARCHAR(MAX)), 1, 2, '')
     , STUFF(CAST(x.query('email/text()') AS NVARCHAR(MAX)), 1, 2, '')
FROM @events t
OUTER APPLY (
    SELECT [name] = N', ' + [name]
         , [email] = N', ' + [email]
    FROM @users t2
    WHERE t.data.exist('users/user/user_id[text() = sql:column("t2.id")]') = 1
    FOR XML PATH(''), TYPE
) t2 (x)

Рад был помочь
15 июн 16, 10:03    [19294500]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить