在SQL Server中将值转换为列

Question

我有这个专栏:

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

我想把它变成这样:

DEP
------
first
second
third
fourth

我正在尝试这个:

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

但是结果返回DEP列4次:

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

Answer 1

只使用replace()或stuff()如何?

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

或:

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