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

Question

I have this query:

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

This is what the query gives me. .

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.

This doesn't look like your looking to turn a column into multiple columns, this looks like your trying concatenate many columns into one, and to move rows to columns. The Title and images tell completely different stories,. What is *actually* correct? — Thom A, May 04 '20 at 12:15
@larnu Hi, I am sorry for not being clear at all.. The last image is what I am trying to achieve here. — Lucdabomb, May 04 '20 at 12:29

GMB · Accepted Answer · 2020-05-04T14:05:10.093

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.

score 1 · Answer 2 · answered May 04 '20 at 12:35

1

This can be solved by using PIVOT in SQL Server.

To understand about Pivot, Please use the already answered link below

Understanding PIVOT function in T-SQL

answered May 04 '20 at 12:35

Hari

64
7

score 0 · Answer 3 · answered May 04 '20 at 12:49

You can use good old style of max/sum(case when staple = 1 then plantnaam+' '+plantmaat+' '+hoeveelheid end) as 1 and group by on staple and use repeat operator to print row multiple times. Or as suggested above you can use PIVOT operator.

Assumption here plantmaat column is of string type other you will have to use convert function for converting number to string.

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

3 Answers3