CODEWITHSUNDEEP
sqlsql-server
DrGenius
DrGenius

Reputation: 967

Convert values into columns in SQL Server

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 |

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270883

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

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

or:

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

Upvotes: 1

Related Questions