SQL добавляет обработанные идентификаторы в одну ячейку, разделенную запятой

У меня есть следующий sql-запрос, чтобы получить представление о том, что он делает, прочитав ниже описание

select catalogid, numitems, allitems - numitems ignoreditems
    from (
      select i.catalogid,
        sum(case when (ocardtype in ('PayPal','Sofort') OR
                       ocardtype in ('mastercard','visa') and
                       odate is not null) AND NOT EXISTS (
                         select * from booked b
                         where b.ignoredoid = o.orderid
                       ) then numitems
                       else 0 end) numitems,
        sum(numitems) allitems
      from orders o
      join oitems i on i.orderid=o.orderid
      group by i.catalogid
    ) X

и следующие таблицы sql

таблица oitems

+---------+-----------+----------+
| orderid | catalogid | numitems |
+---------+-----------+----------+
| O737    |       353 |        1 |
| O738    |       364 |        4 |
| O739    |       353 |        3 |
| O740    |       364 |        6 |
| O741    |       882 |        2 |
| O742    |       224 |        5 |
| O743    |       224 |        2 |
+---------+-----------+----------+

Таблица заказов

 +-----------------+------------+------------+
    |         orderid | ocardtype  |   odate    |
    +-----------------+------------+------------+
    |     O737        | Paypal     |            | 'OK
    |     O738        | MasterCard | 01.02.2012 | 'OK
    |     O739        | MasterCard | 02.02.2012 | 'OK
    |     O740        | Visa       | 03.02.2012 | 'OK
    |     O741        | Sofort     |            | 'OK
    |     O742        |            |            | 'ignore because ocardtype is empty
    |     O743        | MasterCard |            | 'ignore because Mastercard no odate
    +-----------------+------------+------------+

reusltant datatable

+-----------+----------+--------------+
| catalogid | numitems | ignoreditems |
+-----------+----------+--------------+
|       353 |        4 |            0 |
|       364 |       10 |            0 |
|       882 |        2 |            0 |
|       224 |        0 |            7 |
+-----------+----------+--------------+

идея состоит в том, чтобы суммировать столбец numitems для продуктов, которые имеют одинаковую каталогизу, отображающую данные в таблице oitems со следующими условиями

  • если ocardtype пуст, то игнорируйте numitems и считайте его 0 в сумме и сумме игнорируемых элементов в столбец ignoreditems
  • если ocardtype для какого-либо заказа - MasterCard или Visa, а odate - затем проигнорируйте numitems и рассмотрите его как 0 и суммируйте игнорируются элементы в столбце ignoreditems
  • Если ocardtype - Paypal или Sofort, тогда просто выполните сумму numitems без проверки odate, потому что для этих типов не требуется odate
  • в другой таблице, названной забронированной, у меня есть столбец с именем ignoredoid, эти столбцы содержат orderids из приведенной выше таблицы, что я хочу игнорировать, даже если указанные выше условия выше satsfied

К этому моменту запрос работает отлично благодаря @Richard aka cyberkiwi к его ответу в этом question

Вопрос: мне нужен результат, который будет выглядеть следующим образом

+-----------+----------+--------------+-------------------+
| catalogid | numitems | ignoreditems | orderidcollection |
+-----------+----------+--------------+-------------------+
|       353 |        4 |            0 | O737,O739         |
|       364 |       10 |            0 | O738,O740         |
|       882 |        2 |            0 | O741              |
|       224 |        0 |            7 |                   |'O742 & O743 are ignored      
+-----------+----------+--------------+-------------------+

как вы можете видеть, единственным изменением является добавление столбца orderidcollection, он должен добавить orderid в новый столбец, разделенный запятой, только если этот порядок не игнорируется в коде, я был googling для пары часов без везения! это даже возможно с SQL?

Ответы

Ответ 1

Мой ответ Linq-to-Sql на ваш предыдущий вопрос распространяется на этот вопрос (запрос LINQpad):

Dim odateRequired = {"MasterCard", "Visa"}
Dim odateNotRequired = {"Paypal", "Sofort"}

Dim result = From o In Orders Join i In Oitems On o.orderid Equals i.orderid _
             Let check = Not (From b In Bookeds Where b.ignoredoid=i.orderid).Any _
                     AndAlso o.ocardtype IsNot Nothing _
                     AndAlso ((odateRequired.Contains(o.ocardtype) AndAlso o.odate IsNot Nothing) _
                        OrElse odateNotRequired.Contains(o.ocardtype)) _
         Group By i.catalogid Into _
         numitems = Sum(If(check, i.numitems, 0)), _
         ignoreditems = Sum(If(check, 0, i.numitems)), _
         group
         Select catalogid, numitems, ignoreditems, _
             orderidcollection = String.Join(",", (From g in group Where g.check Select g.i.orderid))
result.Dump

Обратите внимание, что это решение выдает несколько SQL-запросов для определения orderids для каждого catalogid, где check есть True для накопления каждого orderidcollection. Может быть более эффективное решение (либо с использованием некоторых, возможно, запутанных, Linq-to-Sql, которые заставляют сгенерированный SQL выполнять эквивалент String.Join, либо получить catalogid, numitems, check, orderid с использованием Linq-to-Sql, а затем использовать Linq -to-objects для окончательного накопления).

Обратите внимание, что этот запрос включает вашу таблицу Booked (с плюрализацией LINQpad).