how to transpose in sql

Question

Suppose I have

select *
from A a
     left outer join B b on b.ID in (1,2,3/*and possibly any numbers*/)

and so I get (Ax - A's xth row, Bx - B's xth row):

A1 B1
A1 B2
A1 B3
A2 B1
...

And what I want is this:

A1 B1 B2 B3
A2 B1 B2 B3

So that there is dynamic number of columns. What is the best way of achieving this?

Answer 1

It is possible using Pivots. The below link might help you. It contains 4-5 different solutions.

Tranpose in SQL Server