Is it possible to display blanks instead of duplicated row cell information?

Question

I've got a report that I have grouped by a particular ID, which means some information is necessarily duplicated. In this case, each reference number has multiple patent records associated with it. So if there are 4 patents, it would correctly return 4 rows, but the reference ID, submission date, and title would be repeated on all 4 rows, while the other columns display unique information.

Is it possible to have this return blanks on rows 2-4 so that I only see the "common" information once?

This is what I currently see.

Reference-1    July 1, 2019    Title here    Acme, Inc.    ABCD-1234  Patent 1
Reference-1    July 1, 2019    Title here    Acme, Inc.    ABCD-1234  Patent 2
Reference-1    July 1, 2019    Title here    Acme, Inc.    ABCD-1234  Patent 3
Reference-1    July 1, 2019    Title here    Acme, Inc.    ABCD-1234  Patent 4

Is it possible to have it return this? I want the same rows returned, just without the duplicated values.

 Reference-1    July 1, 2019    Title here    Acme, Inc.    ABCD-1234  Patent 1
                                                                       Patent 2
                                                                       Patent 3
                                                                       Patent 4

Here's the current version of my code:

SELECT
i.reference_id AS "Reference Number",
i.submission_dt AS "Submission Date",
i.title AS "Title",
o.name AS "Sponsor",
o.grant_number AS "Grant Number",
p.patent_id AS "IP Reference"


FROM 
invention i
LEFT OUTER JOIN
organization2invention o2i ON o2i.invention_id = i.invention_id
LEFT OUTER JOIN
organization o ON o2i.organization_id = o.organization_id
LEFT OUTER JOIN
patent2invention pt2i ON pt2i.invention_id = i.invention_id
LEFT OUTER JOIN
patent p ON pt2i.patent_id = p.patent_id


WHERE
o.grant_number = "ABCD-1234"

group by p.patent_id
order by i.reference_id

ETA: In this example, the title would never change between Patent 3 and Patent 4. All of the columns except for the Patent column are linked and would remain the same.

However it IS possible for there to be another set of records with Reference-2, and I would want all of the fields to show the first time Reference-2 appears, but no other time.

Reference-1    July 1, 2019    Title here    Acme, Inc.    ABCD-1234  Patent 1
                                                                      Patent 2
                                                                      Patent 3
                                                                      Patent 4     

Reference-2    July 1, 2019    Title here    Acme, Inc.   ABCD-1234   Patent 5
                                                                      Patent 6
                                                                      Patent 7
                                                                      Patent 8

version 5.7.22

ETA2: I'm pulling data out of a web/cloud based database with a really shitty UI. They built in a page that has an input box for MySQL code and a button that says "Run Query" for instances their interface doesn't provide data the way we need it. I only have read-only access, and their display doesn't recognize certain things (like \n), and it has some built in formatting tags (similar to my_object_id AS "My Object|display_name:MyObject").

When I click "Run Query" the bottom portion of the web page returns the display.

This functionality is intended to be used as "Custom Reports", so display matters. They just didn't give me a method to do that other than in direct MySQL code.

How would you want it to act if say, Patent 3 was "title 2 here" ; how would you want the row for Patent 4 to display? — Martin, Jul 01 '19 at 16:31
In this example, the title would never change between Patent 3 and Patent 4. However it IS possible for there to be another set of records with Reference-2, and I would want all of the fields to show the first time Reference-2 appears, but no other time. — Mandi Kaye, Jul 01 '19 at 16:35
So to clarify: You want to display all data fields *ONLY* when the first occurance of a certain field value appears - so all data on the first occurance of Reference: "Reference-1" and all data shown on the first occurance of Reference: "Reference-2" etc. — Martin, Jul 01 '19 at 16:38
Yes, I think that's it exactly! Is that possible? This is for pure readability/export capability. — Mandi Kaye, Jul 01 '19 at 16:42
Possible- but consider handling issues of data display in application code instead — Strawberry, Jul 01 '19 at 16:43
That's not possible. I'm limited to a text input box for mysql code with read only access to the data. — Mandi Kaye, Jul 01 '19 at 16:45
@MandiKaye I created two query in demo check here https://www.db-fiddle.com/f/7zaA7BuUEQ4FziaxCcnzhR/0 — Mangesh Auti, Jul 01 '19 at 17:35
@MangeshAuti - that seems to display as I'm hoping. I'm just having trouble figuring out how to modify mine to match. — Mandi Kaye, Jul 01 '19 at 18:20

Martin · Answer 1 · 2019-07-01T17:32:22.513

This answer will not output perfect layout because layout is not something SQL is built to deal with. As stated by Strawberry - "consider handling issues of data display in application code instead"

...

OP states:

I'm limited to a text input box for mysql code with read only access to the data.

So MySQL Views are out the question, and possible tidying of results by the display output processor (PHP, etc.) is an unknown variable.

OP also states:

All of the columns except for the Patent column are linked and would remain the same

So;

Try turning the problem around; you want one full result with lots of iterations of patent data. You can do this using GROUP_CONCAT on the changing column, the patent_id column. You must also select DISTINCT values of the reference column, but the SQL SELECT edits to get your desired resuls are fairly minimal:

SELECT
DISTINCT i.reference_id AS "Reference Number",
i.submission_dt AS "Submission Date",
i.title AS "Title",
o.name AS "Sponsor",
o.grant_number AS "Grant Number",
GROUP_CONCAT(p.patent_id SEPARATOR '\\n') AS "IP Reference"

FROM 
invention i
LEFT OUTER JOIN
organization2invention o2i ON o2i.invention_id = i.invention_id
LEFT OUTER JOIN
organization o ON o2i.organization_id = o.organization_id
LEFT OUTER JOIN
patent2invention pt2i ON pt2i.invention_id = i.invention_id
LEFT OUTER JOIN
patent p ON pt2i.patent_id = p.patent_id

WHERE
o.grant_number = "ABCD-1234"

GROUP BY i.reference_id,
ORDER BY i.reference_id

This will result in a set of one row with "Reference 1", for example, with a GROUP CONCAT of all the different p.patent_id values associated with that Reference, seperated by Line breaks, in this case.

The \n is a standard Line Break character (escaped in MySQL as \\n). You can tweak this SEPERATOR as you require.

You have given us no clues as to your display mechanism but potentially you can insert whatever you like, even HTML into the SEPERATOR clause; if you are outputting into an HTML table.

  GROUP_CONCAT(p.patent_id SEPARATOR 
      '</td></tr><tr><td colspan="5">&nbsp;</td><td>') AS "IP Reference"

You can also remove duplicates frrom the patent list by using the DISTINCTclause.

Reference

Output:

Reference-1  July 1, 2019  Title here  Acme, Inc.  ABCD-1234  Patent 1
                                                              Patent 2
                                                              Patent 3
                                                              Patent 4     

Reference-2  July 1, 2019  Title here  Acme, Inc.  ABCD-1234   Patent 5
                                                               Patent 6
                                                               Patent 7
                                                               Patent 8

Thank you. Unfortunately, this won't work. I oversimplified the issue for my question here - there are other columns of data associated with the patent information that requires separate lines instead of using concat. I didn't think to include them here. So this leads me to believe that in the tools I have access to, this will not be possible, other than manually removing them from an export of the data. — Mandi Kaye, Jul 01 '19 at 17:38
@MandiKaye We can only work with the information we are told. MySQL is not designed for output formatting. You should follow Strawberry's advice and tell us more about what sort of display mechanism you have in place,or even if you can run this output result from multiple queries? — Martin, Jul 01 '19 at 17:39
I added as much information as I can to the original post. I don't have much to work with. :( — Mandi Kaye, Jul 01 '19 at 18:06

Is it possible to display blanks instead of duplicated row cell information?

1 Answers1

Output: