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's gravatar image

tzup
36091324
accept rate: 0%

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.

(27 Feb '14, 07:20) Breck Carter

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

(27 Feb '14, 07:40) 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
permanent link

answered 27 Feb '14, 08:17

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

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

permanent link

answered 01 Mar '14, 06:49

Volker%20Barth's gravatar image

Volker Barth
30.6k304455662
accept rate: 32%

edited 01 Mar '14, 06:56

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×412

question asked: 27 Feb '14, 06:24

question was seen: 616 times

last updated: 03 Mar '14, 03:50