Convert values into columns in SQL Server

Question

I have this column:

floor
---------
dep:first
dep:second
dep:third
dep:fourth

And I want to make it like this :

DEP
------
first
second
third
fourth

I am trying this :

select
    max(case when floor = 'dep:first' then 'first' end) DEP,
    max(case when floor = 'dep:second' then 'second' end) DEP,
    max(case when floor = 'dep:third' then 'third' end) DEP,
    max(case when floor = 'dep:fourth' then 'fourth' end) DEP,
from 
    db.table

But the result returns the DEP column 4 times:

DEP    |DEP    |DEP     |DEP    |
-------|-------|--------|-------|
first  |second |third   |fourth |

Gordon Linoff · Accepted Answer · 2021-02-11 15:37:56Z

1

How about just using replace() or stuff()?

select replace(floor, 'dep:', '')

or:

select stuff(floor, 1, 4, '')

answered Feb 11, 2021 at 15:37

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

DrGenius Over a year ago

Thank you for your quick response. I thought too the first option but I wanted to see the one that I asked. Can you please explain me the second option you have provided?

Thom A Over a year ago

The documentation explains how the function works, @Thkas . STUFF (Transact-SQL)

Collectives™ on Stack Overflow

Convert values into columns in SQL Server

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related