How to rewrite a query to return a single line

[former_SQLA_member1694900]

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

[Breck_Carter]

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.

[former_SQLA_member1694900]

I'd rather avoid having to parse the single-row, single-column on the client to "extract" the multiple pieces of data.

[Breck_Carter · Accepted solution]

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

[former_SQLA_member1694900 · Accepted solution]

Thank you Breck for taking the time to answer my question

[VolkerBarth]

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

[Breck_Carter]

Well done! ( I feel a blog post coming on 🙂

[former_SQLA_member1694900]

"Cross join" huh? That would have never crossed my mind 🙂 Thanks!

[VolkerBarth]

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