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