So I have this query that returns 2 lines of computed data: Line 1. Comma separated list of alive partners and their count Line 2. Comma separated list of non-alive partners and their count The data is also based on a "widgets to deliver to partners" table that links partners with widgets. (The same partner might be repeated in that table since it can receive more than one widget, hence the "distinct" below) SELECT LIST(f.PartnerID) as ListPartners, COUNT(f.PartnerID) as ListPartnersCount FROM (SELECT DISTINCT ps.PartnerID FROM Partners ps JOIN PartnersWidgets pw ON pw.PartnerID = ps.PartnerID WHERE (ps.ALIVE = 1 AND pw.DELIVERY_CHECK = 1)) f UNION SELECT LIST(p.PartnerID), COUNT(p.PartnerID) FROM (SELECT DISTINCT ps.PartnerId FROM Partners ps JOIN PartnersWidgets pw ON pw.PartnerId = ps.PartnerId WHERE (ps.ALIVE = 0 AND pw.DELIVERY_CHECK = 1)) p Is there a way to write this query so that the result is one single line? (I am looking actually for a better way of writing this query; having the result on a single line would be nice though) Thank you asked 27 Feb '14, 06:24 tzup |
Taking the technique from Crosstab, Pivot, Rotate and hard-coding the query instead of using EXECUTE IMMEDIATE because the number of input rows is fixed at 2 and number of output columns is fixed at 4, then extending the technique to use MAX instead of SUM on the string column... yes, it's ugly and weird, but generations of application developers have used the IF and SUM trick even on the client side (e.g., PowerBuilder): CREATE TABLE Partners ( PartnerID INTEGER, ALIVE INTEGER ); CREATE TABLE PartnersWidgets ( PartnerID INTEGER, DELIVERY_CHECK INTEGER ); INSERT Partners VALUES ( 1, 1 ); INSERT Partners VALUES ( 2, 1 ); INSERT Partners VALUES ( 3, 1 ); INSERT Partners VALUES ( 4, 1 ); INSERT Partners VALUES ( 5, 0 ); INSERT Partners VALUES ( 6, 0 ); INSERT Partners VALUES ( 7, 0 ); INSERT Partners VALUES ( 8, 0 ); INSERT PartnersWidgets VALUES ( 1, 1 ); INSERT PartnersWidgets VALUES ( 2, 1 ); INSERT PartnersWidgets VALUES ( 3, 0 ); INSERT PartnersWidgets VALUES ( 4, 0 ); INSERT PartnersWidgets VALUES ( 5, 1 ); INSERT PartnersWidgets VALUES ( 6, 1 ); INSERT PartnersWidgets VALUES ( 7, 0 ); INSERT PartnersWidgets VALUES ( 8, 0 ); COMMIT; WITH list_count AS ( SELECT set_id, LIST(f.PartnerID) as ListPartners, COUNT(f.PartnerID) as ListPartnersCount FROM (SELECT DISTINCT 1 AS set_id, ps.PartnerID FROM Partners ps JOIN PartnersWidgets pw ON pw.PartnerID = ps.PartnerID WHERE (ps.ALIVE = 1 AND pw.DELIVERY_CHECK = 1)) f GROUP BY set_id UNION SELECT set_id, LIST(p.PartnerID), COUNT(p.PartnerID) FROM (SELECT DISTINCT 2 AS set_id, ps.PartnerId FROM Partners ps JOIN PartnersWidgets pw ON pw.PartnerId = ps.PartnerId WHERE (ps.ALIVE = 0 AND pw.DELIVERY_CHECK = 1)) p GROUP BY set_id ) SELECT MAX ( IF set_id = 1 THEN ListPartners ELSE '' ENDIF ) AS ListPartners1, SUM ( IF set_id = 1 THEN ListPartnersCount ELSE 0 ENDIF ) AS ListPartnersCount1, MAX ( IF set_id = 2 THEN ListPartners ELSE '' ENDIF ) AS ListPartners2, SUM ( IF set_id = 2 THEN ListPartnersCount ELSE 0 ENDIF ) AS ListPartnersCount2 FROM list_count; ListPartners1 ListPartnersCount1 ListPartners2 ListPartnersCount2 1,2 2 5,6 2 answered 27 Feb '14, 08:17 Breck Carter Thank you Breck for taking the time to answer my question
(28 Feb '14, 01:14)
tzup
|
I guess there is an easier way here - and in general: Given you have a query that returns two result set rows A and B steeming from distinct parts of a UNION query, you can also construct the query as a cross join that simply joins the queries that build the UNION branches - a cross join between two one-row result sets will apparently consist of one row, too. Following Breck's table structure and contents, the following should do: SELECT f.ListPartners as ListPartners1, f.ListPartnersCount as ListPartnersCount1, p.ListPartners as ListPartners2, p.ListPartnersCount as ListPartnersCount2 FROM (SELECT LIST(ps.PartnerID) as ListPartners, COUNT(ps.PartnerID) as ListPartnersCount FROM Partners ps JOIN PartnersWidgets pw ON pw.PartnerID = ps.PartnerID WHERE ps.ALIVE = 1 AND pw.DELIVERY_CHECK = 1) f CROSS JOIN (SELECT LIST(ps.PartnerID) as ListPartners, COUNT(ps.PartnerID) as ListPartnersCount FROM Partners ps JOIN PartnersWidgets pw ON pw.PartnerID = ps.PartnerID WHERE ps.ALIVE = 0 AND pw.DELIVERY_CHECK = 1) p The result is the same as in Breck's query: ListPartners1 ListPartnersCount1 ListPartners2 ListPartnersCount2 1,2 2 5,6 2 IMHO, that pattern could generally be used to combine distinct single-row UNION branch queries "horizontally" (i.e. by concatenating/adding columns) instead of "vertically" (i.e. by adding rows). answered 01 Mar '14, 06:49 Volker Barth Well done! ( I feel a blog post coming on :)
(01 Mar '14, 08:25)
Breck Carter
"Cross join" huh? That would have never crossed my mind :) Thanks!
(03 Mar '14, 02:18)
tzup
Replies hidden
Well, a cross join is simply a join without further conditions, it's the generic "cross product" of joining each row of the first table with each row of the second table. It's useful here as there is no particular join condition... I'm stating that here although I'm sure you are aware of that:)
(03 Mar '14, 03:49)
Volker Barth
|
Do you want a single-row single-column result set that contains all the data in a single string? That one's easy because you can nest calls to STRING and LIST to build fantastically complex strings (e.g., entire web pages).
Or do you want a single-row four-column result set [list, count, list, count]? That one, not so easy... at first glance it looks like a "Crosstab, Pivot, Rotate" kinda problem.
I'd rather avoid having to parse the single-row, single-column on the client to "extract" the multiple pieces of data.