title: Using SQL - How to aggregate Payment Type
body: <p>How can I aggregate the PaymentType field so that if a customer has used all payment types previously, the payment type screen displays 'All' instead of 'Cash', 'Check', 'Credit/Debit Card', or 'Corporate Account'? Below is the script necessary to demonstrate the issue.</p>
  
  <p><a href="https://i.stack.imgur.com/pWWHk.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pWWHk.png" alt="enter image description here"></a></p>
  
  <pre><code>CREATE TABLE #Customer (
      CustomerId int,
      CustomerName nvarchar(100),
      Address nvarchar(100),
  )
  INSERT INTO #Customer
  VALUES (1, 'Bill', '123 1st St'),
         (2, 'Fred', '111 Market St'),
         (3, 'Lisa', '01 Boulevard')
  
  CREATE TABLE #Product (
      ProductId int,
      ProductName nvarchar(100)
  )
  INSERT INTO #Product
  VALUES (1, 'Corn Flakes'),
         (2, 'Cheerios'),
         (3, 'Granoloa')
  
  CREATE TABLE #PaymentType (
      PaymentId int,
      PaymentTypeName nvarchar(100)
  )
  
  INSERT INTO #PaymentType
  VALUES (1, 'Cash'),
         (2, 'Check'),
         (3, 'Credit/Debit Card'),
         (4, 'Corporate Account')
  
  CREATE TABLE #TransactionLog (
      LogId int,
      ProductId int,
      CustomerId int,
      PaymentId int,
      Amount float
  )
  INSERT INTO #TransactionLog
  VALUES (1, 1, 1, 1, 2.00),
         (2, 2, 1, 2, 2.40),
         (3, 3, 1, 3, 1.80),
         (4, 1, 1, 4, 2.00),
         (5, 1, 2, 4, 2.00),
         (6, 2, 3, 2, 2.40)
  
  SELECT * from #Customer
  SELECT * from #Product
  SELECT * from #PaymentType
  SELECT * from #TransactionLog
  
  SELECT
      c.CustomerName,
      p.ProductName,
      pt.PaymentTypeName as PaymentType
  FROM #TransactionLog t
  join #Product p on t.ProductId = p.ProductId
  join #Customer c on t.CustomerId = c.CustomerId
  join #PaymentType pt on t.PaymentId = pt.PaymentId
  
  DROP TABLE #Customer
  DROP TABLE #Product
  DROP TABLE #PaymentType
  DROP TABLE #TransactionLog
  </code></pre>