SQL server – How to split one column into more columns?

I have this query:

select plantnaam, plantmaat, stapel, hoeveelheid
from kist
where rij = '11'
order by stapel ASC, datum DESC

.

What I want to achieve is to give each number of the column ‘Stapel’ a seperate column, filled with the information of the columns ‘plantnaam + plantmaat + hoeveelheid’. Like this:

Is it possible to create a query like this? Any help would be much appreciated.

Answer

If I followed you correctly, you could do this with row_number()and conditional aggregation:

select
    max(case when stapel = 1 then concat_ws(' ', plantnaam, plantmaat, hoeveelheid) end) col1,
    max(case when stapel = 2 then concat_ws(' ', plantnaam, plantmaat, hoeveelheid) end) col2,
    max(case when stapel = 3 then concat_ws(' ', plantnaam, plantmaat, hoeveelheid) end) col3,
    max(case when stapel = 4 then concat_ws(' ', plantnaam, plantmaat, hoeveelheid) end) col4,
    max(case when stapel = 5 then concat_ws(' ', plantnaam, plantmaat, hoeveelheid) end) col5
from (
    select 
        t.*, 
        row_number() over(partition by stapel order by plantnaam, plantmaat, hoeveelheid) rn
    from mytable t
) t
group by rn

The order by clause of row_number() controls in which order rows are displayed in the resultset; you might want to adapt it to your exact requirement.