SQL - Suppressing duplicate columns (not rows) for a query

Question

I have a query that returns something like this...

Dataset 1:

EFFECTIVE_DATE END_DATE DESC                            SUBPART

4/10/2011                  Dairy Products Processing           L
4/10/2011                  360 CMR 10.000   
4/1/2011       4/9/2011    Dairy Products Processing            A
4/1/2011       4/9/2011    Ferroalloy Manufacturing             A

I'm looking to get a query that returns that dataset like this...

Dataset 2:

   EFFECTIVE_DATE END_DATE  DESC                            SUBPART

    4/10/2011                 Dairy Products Processing           L
                             360 CMR 10.000 
    4/1/2011        4/9/2011   Dairy Products Processing           A
                               Ferroalloy Manufacturing         A

Notice that the duplicate effective date( 4/10/2011 - {null} and 4/1/2011 - 4/9/2011) are suppressed when duplicated.

Edit 1: In response to @Justin Cave's answer,

Below is my query merged with Justin Cave's template. It's close, but its a little off. The dates and descriptions seem a little mixed up from what they're supposed to be (the data in the dataset should be like the one in Dataset 2:. I think it may have something to do with my ordering but I'm not sure.

SELECT (CASE WHEN effective_date = prior_effective_date 
             THEN null
             ELSE effective_date
         END) effective_date,
       (CASE WHEN end_date = prior_end_date 
             THEN null
             ELSE end_date
         END) end_date,
       cfr_part_desc ,
       cfr_subpart
  FROM
    (SELECT c.effective_date,
            lag(c.effective_date) over (order by c.effective_date desc, cpl.cfr_part_desc asc) prior_effective_date,
            c.end_date,
            lag(c.end_date) over  (order by c.effective_date desc, cpl.cfr_part_desc asc) prior_end_date,
            cpl.CFR_PART_DESC as cfr_part_desc,
            cd.CFR_SUBPART as cfr_subpart
     from table1 c
      inner join table2 cd ON c.IND_ID = cd.IND_ID
                              AND cd.EFFECTIVE_DATE = c.EFFECTIVE_DATE
      inner join table3 cpl on cd.CFR_PART_L_S = cpl.CFR_PART_L_S 
      inner join table4 f on c.ind_id = f.ind_id
      inner join table5 p on f.ind_id = p.ind_id
    where p.PERMIT_S = '4988'
    order by c.effective_date desc, cpl.CFR_PART_DESC asc
    );

Edit 2: The incorrect data was due to ambiguously defining the column effective date. The query above now works correctly.

Why do you need that at all? IMHO it's up to your client to display the data according to your needs. Don't do it with SQL. — Falcon
– Falcon, Commented Apr 19, 2011 at 20:50
unfortunately the way the system is setup this must be done. (Its a legacy system). Pretty much, I construct my sql statement, use aspose and XML and display the output of the query in a MS word document. — contactmatt
– contactmatt, Commented Apr 19, 2011 at 20:59

Justin Cave · Accepted Answer · 2011-04-20 20:54:41Z

5

I think you're looking for something like this using analytic functions. It's not clear to me how the result set is ordered, so you'll have to fill that in. You'll also probably be able to combine the analytic function into your query rather than adding a third level of nesting.

SELECT (CASE WHEN effective_date = prior_effective_date 
             THEN null
             ELSE effective_date
         END) effective_date,
       (CASE WHEN end_date = prior_end_date 
             THEN null
             ELSE end_date
         END) end_date,
       description ,
       subpart
  FROM
    (SELECT effective_date,
            lag(effective_date) over 
                (order by effective_date desc, description asc) prior_effective_date,
            end_date,
            lag(end_date) over 
                (order by effective_date desc, description asc) prior_end_date,
            desc,
            subpart
       FROM <<your query>>)

edited Apr 20, 2011 at 20:54

answered Apr 19, 2011 at 21:21

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

contactmatt Over a year ago

I have it so close to working. I'm sorting by effective_date descending. Could you update your answer with my sort? Maybe I'm missing something

Justin Cave Over a year ago

@contactmatt - How do you know that "Dairy Products Processing" comes before "360 CMR 10.000" if both effective_date values are the same? Or is there a difference with the time component?

contactmatt Over a year ago

Nothing was specified by the client we're working with, but I think it'd make sense to sort by the description ascending. (There's no time component)

Justin Cave Over a year ago

@contactmatt - OK. Updated the ORDER BY in the analytic functions

contactmatt Over a year ago

@Justin Cave, thank you! I updated my question to include my query merged with your template and a screenshot of the returned dataset. It's just a little off and I don't know what it is. I think it may be something with the sorting.

|

user761758 · Accepted Answer · 2012-04-12 08:12:15Z

1

If you're using SQL*Plus to generate the output, you can use BREAK:

BREAK ON EFFECTIVE_DATE ON END_DATE
SELECT ...

edited Apr 12, 2012 at 8:12

user761758

5852 gold badges6 silver badges19 bronze badges

answered Apr 20, 2011 at 2:50

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Collectives™ on Stack Overflow

SQL - Suppressing duplicate columns (not rows) for a query

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related