SQL Select - Merge Rows with matching ID

Question

I have a table which contains details of transactions,

I have managed to get back to the raw form of data in terms of categorising Transactions

What I now need to do is return 1 row per transaction.

What I currently have is:

TransID | Type
1       | G
2       | G
2       | G
2       | G
3       | S
4       | G
4       | S
5       | X
5       | G
6       | G
6       | S
6       | X

What I need for each transaction is to return the following:

TransID | Type
1       | G
2       | G
3       | S
4       | M
5       | G
6       | M

So, what I need is, if a transaction is a single row, then return that Type.

If a transaction has multiple rows, evaluate as follows:

 All Types = G -> Return G
 All Types = S -> Return S
 Mix of Types G and S -> Return M
 Mix of Types G and X -> Return G
 Mix of Types S and X -> Return S
 Mix of Types S, G and X -> Return M

Answer 1

A simple way could be...

SELECT
  TransID,
  CASE
    WHEN G+S+X = 3 THEN 'M'
    WHEN S+X   = 2 THEN 'S'
    WHEN G+X   = 2 THEN 'G'
    WHEN G+S   = 2 THEN 'M'
    WHEN S     = 1 THEN 'S'
    WHEN G     = 1 THEN 'G'
                   ELSE '-'
  END as aggregate_type
FROM
(
  SELECT
    TansID,
    MAX(CASE WHEN Type = 'G' THEN 1 ELSE 0 END)   AS G,
    MAX(CASE WHEN Type = 'S' THEN 1 ELSE 0 END)   AS S,
    MAX(CASE WHEN Type = 'X' THEN 1 ELSE 0 END)   AS X
  FROM
    YourTable
  GROUP BY
    TransID
)
  AS data

Though you may be able to consolidate the CASE statement to be shorted, this should be very easy to change as/when necessary.