title: SAP HANA SQL match string patterns and combinations
body: <p>I am using HANA and I am trying to extract the data from the table based on the multiple combinations of values in a column:<br/>
  Below is my customer table layout:<br/></p>
  
  <h2><strong>Table A:</strong><br/></h2>
  
  <h1>Customer &nbsp;Product</h1>
  
  <p>ABC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; P1<br/>
  ABC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; P2<br/>
  ABC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; P3<br/>
  ABC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; G2<br/>
  ABC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; G4<br/>
  ABC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; G6<br/>
  ABC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; G5<br/></p>
  
  <p>Below are the Sales Campaign rules:<br/></p>
  
  <h2><strong>Combinations:</strong><br/></h2>
  
  <h2>Campaign_Name&nbsp;&nbsp;Products_Purchased<br/></h2>
  
  <p>Campaign_1 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (P1 &amp; P2 &amp; P3)<br/>
  Campaign_2 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; (G2 or G4) &amp; G6<br/></p>
  
  <p>If a Customer purchased all of the products P1,P2 and P3 then it would qualify for the Campaign_1.<br/>
  If a Customer purchased any of the products from (G2 or G4 ) and G6 then it would qualify for the Campaign_2.<br/>
  In this example, since customer 'ABC' purchased the combinations of the products mentioned in the campaign, it would be qualified for both the campaigns.<br/></p>
  
  <h2><strong>Expected Result:</strong><br/></h2>
  
  <h2>Customer &nbsp;&nbsp;&nbsp;&nbsp; Sales_Campaign<br/></h2>
  
  <p>ABC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Campaign_1<br/>
  ABC &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Campaign_2<br/></p>
  
  <p>Below are the steps I have performed so far:<br/>
  <em>Step 1:</em> I extracted all the products purchased at a customer level.
  <em>Step2:</em> I aggregated all the products purchased by a customer in a single row separated by a comma.<br/>
  <em>Step 3:</em> Gave all the possible combinations manually in the case statement and setup a flag column to each of the campaigns to determine if the customer purchased any qualifying sales_campaigns.<br/>
  <em>Step 4</em>: If the Flag is 'Y' then I extracted the respective campaign name.<br/><br/>
  <strong>But if the number of products increases then the step 3 above would be impossible to derive.</strong><br/></p>
  
  <h2><strong>Step2 Result:</strong><br/></h2>
  
  <h2>Customer&nbsp;P_COMBO&nbsp;G_COMBO<br/></h2>
  
  <p>ABC &nbsp;&nbsp;&nbsp;&nbsp; #,1,P1,P2,P3&nbsp;&nbsp;&nbsp;&nbsp;#,1,G2,G4,G5,G6<br/><br/></p>
  
  <h2><strong>Step4 Result:</strong><br/></h2>
  
  <h2>Customer&nbsp;campaign1_flag&nbsp;Campaign2_flag<br/></h2>
  
  <p>ABC &nbsp;&nbsp;&nbsp;&nbsp; Y&nbsp;&nbsp;&nbsp;&nbsp;Y<br/><br/></p>
  
  <pre><code>SELECT DISTINCT
    B.CUSTOMER
    CASE WHEN P_COMBO ='#,1,P1,P2,P3' THEN 'Y' ELSE 'N' END) AS campaign1_flag,
    (CASE 
       WHEN G_COMBO  ='#,1,G2,G6'       THEN 'Y'
       WHEN G_COMBO  ='#,1,G4,G6'       THEN 'Y'
       WHEN G_COMBO  ='#,1,G2,G5,G6'    THEN 'Y'
       WHEN G_COMBO  ='#,1,G4,G5,G6'    THEN 'Y'
       WHEN G_COMBO  ='#,1,G2,G4,G5,G6' THEN 'Y'
       ELSE 'N'
    END) AS Campaign2_flag
  FROM (
    SELECT DISTINCT
      A.CUSTOMER ,
      (   MAX(CASE WHEN A.PRODUCT= '1'  THEN A.PRODUCT ELSE '#' END)
        || MAX(CASE WHEN A.PRODUCT= 'P1'  THEN ',' || A.PRODUCT ELSE '' END)
        || MAX(CASE WHEN A.PRODUCT= 'P2'  THEN ',' || A.PRODUCT ELSE '' END)
        || MAX(CASE WHEN A.PRODUCT= 'P3'  THEN ',' || A.PRODUCT ELSE '' END)
      ) AS P_COMBO  ,
      (   MAX(CASE WHEN A.PRODUCT= '1'  THEN A.PRODUCT ELSE '#' END)
        || MAX(CASE WHEN A.PRODUCT= 'G2'  THEN ',' || A.PRODUCT ELSE '' END)
        || MAX(CASE WHEN A.PRODUCT= 'G4'  THEN ',' || A.PRODUCT ELSE '' END)
        || MAX(CASE WHEN A.PRODUCT= 'G5'  THEN ',' || A.PRODUCT ELSE '' END)
        || MAX(CASE WHEN A.PRODUCT= 'G6'  THEN ',' || A.PRODUCT ELSE '' END)
      ) AS G_COMBO
      FROM
      (SELECT DISTINCT CUSTOMER,PRODUCT FROM customer) A
      GROUP BY CUSTOMER,2,3
   ) B
  </code></pre>
  
  <p>Please help me find a better solution. Any ideas are very much appreciated.</p>