Question: I have two transaction tables like this:

```
TableA

 X  R  S
 71 1 10 
 71 2 20
 71 3 30 
 72 1 40 
 72 2 50
 72 3 60

TableB

 X  P Q
 71 1 110 
 71 2 120
 71 3 130 
 73 1 140 
 73 2 150
 73 3 160

```

I want to flatten the table and execute a query which gives me a result like this:

```
  X  S1  S2  S3   Q1  Q2  Q3

  71 10  20  30  110 120 130
  72 40  50  60   0    0   0
  73  0   0   0  140 150 160

```

where the subscripts in S and Q denote the value of the third column when the second column equals the subscript. For example, S1 denotes the `S` column value when second column is equal to 1, or Q2 denotes the value of `Q` when the second column is 2.

I can't figure out what is the best way to about it. Maybe I can use a case statement or may be a subquery. But honestly I have no concrete idea of going about it.

Answer: 
You would want to do this with a join and two aggregations:

```
select coalesce(a.x, b.x) as x, a.s1, a.s2, a.s3, b.q1, b.q2, b.q3
from (select x,
             max(case when r = 1 then s end) as s1,
             max(case when r = 2 then s end) as s2,
             max(case when r = 3 then s end) as s3
      from tableA a
      group by x
     ) a full outer join
     (select x,
             max(case when p = 1 then q end) as q1,
             max(case when p = 2 then q end) as q2,
             max(case when p = 3 then q end) as q3
      from tableb b
      group by x
     ) b
     on a.x = b.x;

```

EDIT:

If you want to avoid the nested subqueries, you could try:

```
select coalesce(a.x, b.x) as x,
       max(case when r = 1 then s end) as s1,
       max(case when r = 2 then s end) as s2,
       max(case when r = 3 then s end) as s3
       max(case when p = 1 then q end) as q1,
       max(case when p = 2 then q end) as q2,
       max(case when p = 3 then q end) as q3
from TableA a full outer join
     TableB b
     on a.x = b.x and a.r = b.p
group by coalesce(a.x, b.x);

```

It might even be more efficient.
```
SELECT 
ISNULL(A.x, B.x) AS x, SUM(CASE WHEN R = 1 THEN S ELSE 0 END) AS S1,
SUM(CASE WHEN R = 2 THEN S ELSE 0 END) AS S2,
SUM(CASE WHEN R = 3 THEN S ELSE 0 END) AS S3,
SUM(CASE WHEN P = 1 THEN Q ELSE 0 END) AS Q1,
SUM(CASE WHEN P = 2 THEN Q ELSE 0 END) AS Q2,
SUM(CASE WHEN P = 3 THEN Q ELSE 0 END) AS Q3 
FROM tableA A FULL JOIN tableB B ON A.X = B.X
GROUP BY ISNULL(A.x, B.x)

```

T-SQL version