根据单位改变数据类型

[owl1986]

请问能不能在选数据的时候根据比如单位的不同来改变后面数据的数据类型呢.
比如
吨 11000 22000
m3 2500 3500
null 3600 4600
我现在想在select的时候判断吨和m3,如果是吨 11000就改为11000.0000,22000改为22000.0 如果是m3就改为2500.00, 3500改为3500.0

有朋友回复我
select t1,(case t1 when '吨' then cast(t2 as decimal(14,4)) when 'm3' then cast(t2 as decimal(12,2)) else t2 end)t2,
(case t1 when '吨' then cast(t3 as decimal(12,1)) when 'm3' then cast(t3 as decimal(12,1)) else t3 end)t3
from tb
可是这样写只能把t2都变成4位小数的,并不能根据单位来改变

[Mr_Nice]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([col1] varchar(2),[col2] int,[col3] int)
insert [TB]
select '吨',11000,22000 union all
select 'm3',2500,3500 union all
select null,3600,4600

select * from [TB]


SELECT col1,
col2 = CASE col1 WHEN 'm3' THEN CONVERT(VARCHAR,CONVERT(DECIMAL(9,2),col2))
						WHEN '吨' THEN CONVERT(VARCHAR,CONVERT(DECIMAL(9,4),col2))
						ELSE CONVERT(VARCHAR,col2) END ,
						
	    col3 = CASE col1 WHEN 'm3' THEN CONVERT(VARCHAR,CONVERT(DECIMAL(9,2),col3))
						WHEN '吨' THEN CONVERT(VARCHAR,CONVERT(DECIMAL(9,4),col3))
						ELSE CONVERT(VARCHAR,col3) END 				
FROM TB


/*
col1	col2		col3
吨		11000.0000	22000.0000
m3		2500.00		3500.00
NULL	3600		4600*/

[owl1986]

[Quote=引用 1 楼 orchidcat 的回复:]

SQL code
--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([col1] varchar(2),[col2] int,[col3] int)
insert [TB]
select '吨',11000,22000 union all
select 'm3',2500,3500 ……
[/Quote]
请问必须是varchar型的么,我想要还是decimal