CODEWITHSUNDEEP
sqloracle-databasepivot
Syns
Syns

Reputation: 127

Pivot table: Join same record if one of parameter is empty

I have this Country Label table:

Country_Code  |  Product_Code  | Label_Name  | Placement
IT               PR_I01          T-Label       Top Left
IT               PR_I01          Instruction   Bottom Center
HK               PR_H34          Tag           Bottom Left
HK                               HK Label      Bottom Right
US               PR_U12          Sticker       Bottom Center
GB               PR_G16          QR Code       Bottom Right

I managed to pivot the table to something like this:

Country_Code  |  Product_Code  | Label_Name1  | Placement1      | Label_Name2   | Placement 2 |
IT               PR_I01          T-Label       Top Left           Instruction     Bottom Center
HK               PR_H34          Tag           Bottom Left
HK                               HK Label      Bottom Right
US               PR_U12          Sticker       Bottom Center
GB               PR_G16          QR Code       Bottom Right

I want to make the record with an Empty product_code join other record with the same country_code (every product_code for certain country will always use that one same label). So it will be like this

Country_Code  |  Product_Code  | Label_Name1  | Placement1      | Label_Name2   | Placement 2 |
HK               PR_H34          Tag            Bottom Left       HK Label        Bottom Right

Here my current query:

SELECT CODE_COUNTRY,
       PRODUCT_CODE,
       "1_LN" Label_Name1,
       "1_LP" Placement1,
       "2_LN" Label_Name2,
       "2_LP" Placement2
FROM   (WITH CNTRYRWS
               AS (SELECT   CODE_COUNTRY,
                            PRODUCT_CODE,
                            LABEL_NAME,
                            PLACEMENT,                                
                            ROW_NUMBER ()
                               OVER (
                                  PARTITION BY CODE_COUNTRY, product_code
                                  ORDER BY LABEL_NAME, PLACEMENT
                               )
                               RN
                     FROM   PL_COUNTRY_LABEL_V)
        SELECT   *
          FROM   CNTRYRWS PIVOT ( MIN (LABEL_NAME) LN , MIN (
                          PLACEMENT) LP FOR RN IN ( 1 AS "1" , 2
                          AS "2") ))

How to achieve such a result? Thank you.

Upvotes: 0

Views: 46

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You can aggregate through use of MAX() for all columns except the Country_Code column which should be in the GROUP BY in the main query after removing Product_Code from PARTITION BY list such as

SELECT Country_Code, 
       MAX(product_code) AS Product_Code,
       MAX("1_LN") AS Label_Name1,
       MAX("1_LP") AS Placement1,
       MAX("2_LN") AS Label_Name2,
       MAX("2_LP") AS Placement2
  FROM (WITH CNTRYRWS AS 
       (
        SELECT Country_Code,
               PRODUCT_CODE,
               LABEL_NAME,
               PLACEMENT,
               ROW_NUMBER() 
               OVER(PARTITION BY Country_Code--, Product_Code 
                        ORDER BY LABEL_NAME, PLACEMENT) RN
          FROM PL_COUNTRY_LABEL_V
         )
        SELECT *
          FROM CNTRYRWS 
         PIVOT 
         ( MIN (LABEL_NAME) LN , 
           MIN (PLACEMENT) LP FOR RN IN ( 1 AS "1" , 2 AS "2") ))
GROUP BY Country_Code

Demo

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Hmmm . . . Conditional aggregation seems like a simple approach:

select country_code, max(product_code) as product_code,
       max(case when seqnum = 1 then label_name end) as label_name_1,
       max(case when seqnum = 1 then placement end) as placement_1,
       max(case when seqnum = 2 then label_name end) as label_name_2,
       max(case when seqnum = 2 then placement end) as placement_2
from (select c.*,
             row_number() over (partition by country_code
                                order by product_code nulls last, label_name
                               ) as seqnum
      from PL_COUNTRY_LABEL_V c
     ) c
group by country_code

Upvotes: 1

Related Questions