Query element query

Hi Jason

If you want to accumulate multiple row-values of something into single rows of an output set, there is a way to do that in SQL, if you can work out the right dataset to start from (not necessarily obvious, and heavily dependent on what you're actually trying to do).It's a fairly common piece of trickery, but not a single-step solution.

There's an example below of how you might do it. This one concatenates a comma-separated list of all of the perfs on an order (just their perf_no's, to keep it simple, which isn't what you're after, but might point you in the right direction - I've  simplified this from something that we use, to try to make it clearer.)

This code 

1. creates a local function which does the actual work of concatenating the perf_no's on the order
2. provides an example of how you might use the function in a select statement 

and that statement could be saved as a View, which you would then use as the basis for an output set element. ( you can't use functions directly, and anyway I tend to think it's much cleaner and more readable to just call a View in the TR_QUERY_ELEMENT.data_from field, and put your restrictions in the data_where field, than try to bung the whole Select statement in there.)

Ken

------------------------------------------------------------------------------------------------------------

CREATE  FUNCTION [dbo].[LF_perf_nos_on_order_string] ( @order_no INT )

RETURNS VARCHAR(600)

AS

/*     

Made by KenM 2016-12-29

This function returns a string containing a comma-separated list of the perf_no's of all of the perfs on an order

USAGE EXAMPLE

The usage example uses the function to return a table of customer_no's, order_no's, and the string of perf_no's on each order

select o.customer_no,  o.order_no, dbo.[LF_perf_nos_on_order_string](o.order_no) perf_no_list from T_order o

where o.order_dt between '2016-12-20' and getdate()

*/

BEGIN

DECLARE @perf_str VARCHAR(600) -- a variable to hold the final string

SET @perf_str = '' -- initialise it with a zero-length string

-- this is the tricky bit - it doesn't look sensible, but it will work.

SELECT @perf_str = @perf_str + ' , ' + CONVERT(VARCHAR, a.perf_no)

FROM T_SUB_LINEITEM a

WHERE a.order_no = @order_no

AND a.perf_no <> 0 -- we're not interested in package headers, just actual perfs

SET @perf_str = SUBSTRING(@perf_str, 4, 9999) -- this just chops off the  unneeded comma and spaces at the beginning

RETURN @perf_str

END

GO

GRANT execute ON dbo.LF_perfs_on_order_string TO ImpUsers

Go

AND the output looks like this:

--------------------------------------------------------------------------------------------

customer_no order_no    perf_no_list

----------- ----------- ----------------------------------------------

61320615    33155050    126128 , 126128

62419977    33146658    127888 , 127888 , 127888

61701050    33146659    136160 , 136160

62419974    33146661    125896 , 125896

62419978    33146663    127898 , 127898

62419976    33146664    125891 , 125891

62419963    33146667    137473 , 137473 , 137473 , 137473 , 137473 , 137473

--------------------------------------------------------------------------

Ken, Tom, and Jessica,

Thank you for your ideas. I will try all three of these. 

Jason

Wanted to throw in my two cents since this is SUCH a common request at my organization — wanting to aggregate a column of multiple rows into a comma-separated string for use in an output set.

The good news is that once Tessitura is compatible with SQL Server 2016, we FINALLY have a built-in aggregate function that will do just this, see STRING_AGG(): https://msdn.microsoft.com/en-us/library/mt790580.aspx

In the meantime, the cleanest solution I've found has been to use what's called the "XML Black Box" method. There's a great post on Simple-Talk about the various options and how they compare (Ken's suggestion is the "Scalar UDF With Recursion"): https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

What I like about the XML Black Box method is that you get to use it basically like you use an aggregate function; so you can put it directly into the view that you reference from TR_QUERY_ELEMENT. Here's a view I made recently that aggregates the selected interest descriptions in a designated TKW_CATEGORY for each constituent, for use in an output set:

ALTER VIEW [dbo].[LV_PROSPECT_ANNOTATION]
AS

WITH prosp_interests AS (
  SELECT
      ci.customer_no,
      ci.tkw,
      kw.description,
      ci.selected
    FROM dbo.TX_CUST_TKW ci
      JOIN dbo.TR_TKW kw
        ON ci.tkw = kw.id
          AND kw.category = dbo.FS_GET_DEFAULT_VALUE(null, null, 'LD_PROSPECTING_LIST_CATEGORY')
    WHERE ci.selected = 'Y'
),
customer_aggregate AS (
  SELECT
      i.customer_no,
      'Y' AS on_list,
      COUNT(*) AS quant,
      STUFF(REPLACE(
                    ( SELECT '#!' + description AS 'data()'
                        FROM prosp_interests WHERE customer_no = i.customer_no
                        ORDER BY tkw
                        FOR XML PATH ('') ),
                  ' #!', ', '), 1, 2, '') AS list_str
    FROM prosp_interests i
    GROUP BY customer_no
)
SELECT
    c.customer_no,
    ISNULL(ca.on_list, 'N') AS on_list,
    ISNULL(ca.quant, 0) AS quant,
    ISNULL(list_str, '') AS list_str
  FROM dbo.T_CUSTOMER c
    LEFT JOIN customer_aggregate ca
      ON c.customer_no = ca.customer_no;

GO