Query element query

Jason Buehrer $organization over 9 years ago

We are at the end of our fiscal year and have started an data mining project for grants and foundations. Right now I am working through a 27k excel sheet. Through clever data sorting and conditional formatting I have gotten that number down to 17k. We are trying to squeeze out our unique attendance from this list. The bulk of the work is combining multiple line items into one. For example, we have a patron who has purchased 4 tickets to Westside Story. One of the party is a youth, one is a Young professional, and the rest are adults. When I out put the data through execute and output set, I end up with three rows of data that I have to combine manually.

Is it possible to build a query element that combines all these rows into one? Has anyone had any success building one? Am I using the right tool for this kind of analysis? I have some experience with building query elements with mixed success. I also have some basic SQL skills.

Any help would be appreciated.

Thank you,

Jason Buehrer

Parents

Nick Reilingh $organization over 9 years ago

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

Reply

Nick Reilingh $organization over 9 years ago

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.

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

Children

No Data