How do I group SQL query results into multiple columns depending on the number of results?

Question

This is a tricky one to try and explain but basically I'm after the following.

These are my current results :

Item  | Colours            | Sizes       | Prices
--------------------------------------------------
123   | Black,Blue,Green   | 32,34,36    |  9.99
123   | Black,Blue,Green   | 38,40,42    | 12.99
123   | Black,Blue,Green   | 44,46,48    | 15.99

I need it to be displayed like this:

Item | Colours          | Sizes    | Sizes1   | Size2    | Prices | Prices1 | Prices2
-----------------------------------------------------------------------------------
123  | Black,Blue,Green | 32,34,36 | 38,40,42 | 44,46,48 |  9.99  | 12.99   | 15.99

So I need the columns to be dynamically created depending on the number of rows.

Hope that makes sense.

Answer 1

This is a type of pivot query. I prefer to use conditional aggregation:

select item, colours,
       max(case when seqnum = 1 then sizes end) as sizes_1,
       max(case when seqnum = 2 then sizes end) as sizes_2,
       max(case when seqnum = 3 then sizes end) as sizes_3,
       max(case when seqnum = 1 then prices end) as prices_1,
       max(case when seqnum = 2 then prices end) as prices_2,
       max(case when seqnum = 3 then prices end) as prices_3
from (select t.*,
             row_number() over (partition by item, colours order by prices) as seqnum
      from t
     ) t
group by item, colours;