title: Complex SQL Select Query
body: <p>Table schema:</p>
  
  <pre><code>CREATE TABLE TRANSACTIONDETAILS
  (
      TransNo CHAR(15),
      Serial  INT,
      Project CHAR(3)
  )
  </code></pre>
  
  <p>Dataset:</p>
  
  <pre><code>+-----------------+--------+---------+
  |     TransNo     | Serial | Project |
  +-----------------+--------+---------+
  | A00000000000001 |      1 |     100 |
  | A00000000000001 |      2 |     101 |
  | A00000000000002 |      1 |     100 |
  | A00000000000002 |      2 |     101 |
  | A00000000000003 |      1 |     100 |
  | A00000000000003 |      2 |     200 |
  | A00000000000004 |      1 |     200 |
  | A00000000000004 |      2 |     100 |
  | A00000000000005 |      1 |     101 |
  | A00000000000005 |      2 |     100 |
  +-----------------+--------+---------+
  </code></pre>
  
  <p>I want to identify transactions those have same project sets.</p>
  
  <p>Expected output:</p>
  
  <pre><code>+-----------------+--------+---------+---------+
  |     TransNo     | Serial | Project | Flag    |
  +-----------------+--------+---------+---------+
  | A00000000000001 |      1 |     100 |     1   |
  | A00000000000001 |      2 |     101 |     1   |
  | A00000000000002 |      1 |     100 |     1   |
  | A00000000000002 |      2 |     101 |     1   |
  | A00000000000005 |      1 |     101 |     1   |
  | A00000000000005 |      2 |     100 |     1   |
  | A00000000000003 |      1 |     100 |     2   |
  | A00000000000003 |      2 |     200 |     2   |
  | A00000000000004 |      1 |     200 |     2   |
  | A00000000000004 |      2 |     100 |     2   |
  +-----------------+--------+---------+---------+
  </code></pre>
  
  <p>I am using SQL Server 2012 and later.</p>
  
  <p>Thanks.</p>
  
  <p><strong>UPDATE 1:</strong> Partially my objective would be achieved if I make following from input dataset.</p>
  
  <pre><code>+-----------------+---------+---------+
  |     TransNo     | Project1| Project2|
  +-----------------+---------+---------+
  | A00000000000001 |     100 |     101 |
  | A00000000000002 |     100 |     101 |
  | A00000000000003 |     100 |     200 |
  | A00000000000004 |     200 |     100 |
  | A00000000000005 |     101 |     100 |
  +-----------------+---------+---------+
  </code></pre>
  
  <p><strong>UPDATE 2:</strong> </p>
  
  <p>Data set</p>
  
  <pre><code>+-----------------+--------+---------+
  |     TransNo     | Serial | Project |
  +-----------------+--------+---------+
  | A00000000000001 |      1 |     100 |
  | A00000000000001 |      2 |     101 |
  | A00000000000001 |      3 |     200 |
  | A00000000000002 |      1 |     100 |
  | A00000000000002 |      2 |     101 |
  | A00000000000003 |      1 |     100 |
  | A00000000000003 |      2 |     200 |
  | A00000000000004 |      1 |     200 |
  | A00000000000004 |      2 |     100 |
  | A00000000000005 |      1 |     101 |
  | A00000000000005 |      2 |     100 |
  +-----------------+--------+---------+
  </code></pre>
  
  <p>Output: </p>
  
  <pre><code>+-----------------+--------+---------+---------+
  |     TransNo     | Serial | Project | Flag    |
  +-----------------+--------+---------+---------+
  | A00000000000001 |      1 |     100 |     1   |
  | A00000000000001 |      2 |     101 |     1   |
  | A00000000000001 |      2 |     200 |     1   |
  | A00000000000002 |      1 |     100 |     2   |
  | A00000000000002 |      2 |     101 |     2   |
  | A00000000000005 |      1 |     101 |     2   |
  | A00000000000005 |      2 |     100 |     2   |
  | A00000000000003 |      1 |     100 |     3   |
  | A00000000000003 |      2 |     200 |     3   |
  | A00000000000004 |      1 |     200 |     3   |
  | A00000000000004 |      2 |     100 |     3   |
  +-----------------+--------+---------+---------+
  </code></pre>