74242

SQL add processed ids to a single cell seperated with a comma

Question:

i have the following sql query to get an idea of what it does please read the description below

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

and the following sql tables

oitems table

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

Orders table

+-----------------+------------+------------+ | 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 +-----------------+------------+------------+

the reusltant datatable

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

idea is to sum the numitems column for products that have the same catalogid depinding on the data in the oitems table with the following conditions

<ol><li>if ocardtype is empty then ignore the numitems and consider it as 0 in the sum and sum the ignored items to the ignoreditems column</li> <li>if ocardtype for some order is MasterCard or Visa and the odate is empty then ignore the numitems and consider it as 0 and sum the ignored items to the ignoreditems column</li> <li>if ocardtype is Paypal or Sofort, then just do the numitems sum without checking the odate because those types require no odate</li> <li>in another table called booked i have a column called ignoredoid, this columns contains orderids from the above table that i want to ignore even if the 3 conditions above are satsfied</li> </ol>

To this point the query is working perfectly thanks to @Richard aka cyberkiwi to his answer in this <a href="https://stackoverflow.com/questions/12668630/sql-select-case" rel="nofollow">question</a>

<hr />

The Question is, i need the result datatable to look like the following

+-----------+----------+--------------+-------------------+ | 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 +-----------+----------+--------------+-------------------+

as you can see the only change is the addition of orderidcollection column, it have to add the orderid to the new column seperated by a comma only if that order is not ignored in the code, i've been googling for couple hours with no luck! is this even possible with SQL?

Answer1:

My Linq-to-Sql answer to your previous question extended to this one (LINQpad query):

<pre class="lang-vb prettyprint-override">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

Note this solution issues multiple SQL queries to determine the orderids for each catalogid where check is True to accumulate each orderidcollection. There may be a more efficient solution (either using some, probably convoluted, Linq-to-Sql that causes the generated SQL to do the equivalent of String.Join, or get the catalogid, numitems, check, orderid using Linq-to-Sql and then use Linq-to-objects to do the final accumulations).

Note also this query includes your Booked table (with LINQpad's pluralisation).

Recommend

  • Problems to select the correct row from a second table
  • INVALID_REQUEST: Field [order.avsDetails.billToFirstname] was not in charset [ISO-8859-1]
  • Validating credit card format using regular expressions?
  • WooCommerce Credit Card Fees [duplicate]
  • PayPal RestApiSDK .NET http 503 Server Unavailable
  • Sending SCPI/GPIB commands over USB from C#
  • The request was aborted: Could not create SSL/TLS secure channel.
  • The specified message queue synchronization barrier token has not been posted
  • Migrating row from one table to another with conditions [MYSQL]
  • C++ std::set comparator
  • use images instead of text in webgrid asp mvc
  • Conditional render in tagfile depending on whether the attribute is specified or not
  • SQLiteOpenHelper: onCreate() method not called on physical device
  • Can't get LogCat (Alcatel OneTouch Evolve)
  • Prolog Query - Trying to understand how this result happens
  • How to read data from a text file if the file location is not known in c#?
  • Powerpoint 2010 VSTO AddIn taskpane on multiple Windows
  • QTP UFT Unable to find row count from table
  • how to sort an arraylist that contains string arrays?
  • how to pass value from asp.net server control using jQuery?
  • How to fail Phing without triggering backtrace
  • How to clear specified format data from clipboard?
  • How can I include the Ivy dependency and none of its dependencies?
  • Gforce min not supported for character in data.table
  • Does the failbit effect the call ignore on cin?
  • Swift string variables localization
  • PHP Copy function not working
  • Cythonized function unexpectedly slow
  • Setting src to Base64-encoded image with Javascript is failing
  • ASPNetCore MVC Routing Let Server Handle Specific Route
  • Javascript focus remove text highlight
  • Adding Parent and Child Nodes in TreeView from Sql Server 2008
  • get iframe content as string
  • How solve “Qt: Untested Windows version 10.0 detected!”
  • xtable package: Skipping some rows in the output
  • Jackson Parser: ignore deserializing for type mismatch
  • How to use remove-erase idiom for removing empty vectors in a vector?
  • Repeat a vertical line on every page in Report Builder / SSRS
  • Why is an OPTIONS request sent to the server?
  • Retrieving value from sql ExecuteScalar()