Complex SQL Select Query

Question

Table schema:

CREATE TABLE TRANSACTIONDETAILS
(
    TransNo CHAR(15),
    Serial  INT,
    Project CHAR(3)
)

Dataset:

+-----------------+--------+---------+
|     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 |
+-----------------+--------+---------+

I want to identify transactions those have same project sets.

Expected output:

+-----------------+--------+---------+---------+
|     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   |
+-----------------+--------+---------+---------+

I am using SQL Server 2012 and later.

Thanks.

UPDATE 1: Partially my objective would be achieved if I make following from input dataset.

+-----------------+---------+---------+
|     TransNo     | Project1| Project2|
+-----------------+---------+---------+
| A00000000000001 |     100 |     101 |
| A00000000000002 |     100 |     101 |
| A00000000000003 |     100 |     200 |
| A00000000000004 |     200 |     100 |
| A00000000000005 |     101 |     100 |
+-----------------+---------+---------+

UPDATE 2:

Data set

+-----------------+--------+---------+
|     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 |
+-----------------+--------+---------+

Output:

+-----------------+--------+---------+---------+
|     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   |
+-----------------+--------+---------+---------+

Answer 1

CREATE TABLE #test_trans
    ([TransNo] varchar(15), [Serial] int, [Project] int)
;

INSERT INTO #test_trans
    ([TransNo], [Serial], [Project])
VALUES
    ('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)
;

[![;WITH cte
     AS (select \[TransNo\],(
Select cast(ST1.\[Project\] as varchar(max))  AS \[text()\]
                From #test_trans ST1
                where st1.TransNo=st2.TransNo
                 For XML PATH ('')) as rn,Project,st2.Serial from #test_trans st2)
SELECT TransNo,
       Serial,
       Project,
       Dense_rank()OVER(ORDER BY rn) AS flag
FROM   cte][1]][1] 

Answer 2

Try this

;WITH cte
     AS (SELECT *,
                Concat(Min(Project)OVER(partition BY TransNo), Max(Project)OVER(partition BY TransNo)) AS inter
         FROM   TRANSACTIONDETAILS)
SELECT TransNo,
       Serial,
       Project,
       Dense_rank()OVER(ORDER BY inter) AS flag
FROM   cte 

• SQL FIDDLE DEMO

Update : For partial result

SELECT TransNo,
       Max(CASE WHEN Serial = 1 THEN Project END) AS Project_1,
       Max(CASE WHEN Serial = 2 THEN Project END) AS Project_2
FROM   TRANSACTIONDETAILS
GROUP  BY TransNo