[高分悬赏] varchar类型的纵表变横表

[sartner]

原表 case_table
fname ftype fvalue
-------------------
小乔 早餐 10块
小乔 中餐 20块
小乔 晚餐 5块
大乔 早餐 15块
大乔 中餐 5块
大乔 晚餐 10块

变成
fname 早餐 中餐 晚餐
---------------------------
小乔 10快 20块 5块
大乔 15快 5块 10快

select Fname, sum(case Ftype when '早餐'
then Fvalue else 0 end) as '早餐',
sum(case Ftype when '中餐'
then Fvalue else 0 end) as '中餐',
sum(case Ftype when '晚餐' then Fvalue else 0 end)
as '晚餐'from case_table group by Fname


由于varchar类型不能用sum聚合函数. 所以以上SQL不可用
会报: 在将 varchar 值 '10块' 转换成数据类型 int 时失败。
该怎么解决???
自己写VAR类型聚合函数的话....对于每个表都要写一次 太麻烦, 有其他方法么?

[开着拖拉机泡妞]

cast(left(fvalue,len(fvalue)-1) as int)

[开着拖拉机泡妞]

稍等，给你写一个

[开着拖拉机泡妞]

/*
原表 case_table
fname ftype fvalue
-------------------
小乔 早餐 10块
小乔 中餐 20块
小乔 晚餐 5块
大乔 早餐 15块
大乔 中餐 5块
大乔 晚餐 10块

变成
fname 早餐 中餐 晚餐
---------------------------
小乔 10快 20块 5块
大乔 15快 5块 10快
*/

go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
fname varchar(10),
ftype varchar(10),
fvalue varchar(10)
)
go
insert tbl
select '小乔','早餐','10块' union all
select '小乔','中餐','20块' union all
select '小乔','晚餐','5块' union all
select '大乔','早餐','15块' union all
select '大乔','中餐','5块' union all
select '大乔','晚餐','10块'

select fname as 姓名,
max(case when ftype='早餐' then cast(cast(LEFT(fvalue,LEN(fvalue)-1) AS int)AS varchar)+'块' else ''end)
as 早餐,
max(case when ftype='中餐' then cast(cast(LEFT(fvalue,LEN(fvalue)-1) AS int) as varchar)+'块'else ''end)
as 中餐,
max(case when ftype='晚餐' then cast(cast(LEFT(fvalue,LEN(fvalue)-1) AS int)as varchar)+'块'else ''end)
as 晚餐
from tbl group by fname

/*
结果：
姓名 早餐 中餐 晚餐
大乔 15块 5块 10块
小乔 10块 20块 5块
*/

[Rotel-刘志东]

---语法没有问题，数据类型转化一下就可以了。
---把每个cast(Fvalue as int)不就可以吗
select Fname, 
sum(case Ftype when '早餐'then cast(Fvalue as int) else 0 end) as '早餐',
sum(case Ftype when '中餐'  then cast(Fvalue as int) else 0 end) as '中餐',
sum(case Ftype when '晚餐' then cast(Fvalue as int) else 0 end)  
as '晚餐'from case_table 
group by Fname

[Rotel-刘志东]

---语法没有问题，数据类型转化一下就可以了。
---把每个cast(Fvalue as int)不就可以吗
select Fname, 
max(case Ftype when '早餐'then cast(Fvalue as int) else 0 end) as '早餐',
max(case Ftype when '中餐'  then cast(Fvalue as int) else 0 end) as '中餐',
max(case Ftype when '晚餐' then cast(Fvalue as int) else 0 end)  
as '晚餐'from case_table 
group by Fname

[sartner]

回lzd_83:
在将 varchar 值 '10块' 转换成数据类型 int 时失败。

回TravyLee:
这个表是我临时创建的demo
那个Fvalue的值不一定是固定的格式.
也可能是中文, 比如: 十块, 二十块, 1美元, 一百日元..等等
so..不能截取字符串

[开着拖拉机泡妞]

汗，你加分等大牛来给你解决吧！

[开着拖拉机泡妞]

你可以试试动态行列转换

[Vidor]

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(fname varchar(8), ftype varchar(8), fvalue varchar(8))
insert into #
select '小乔', '早餐', '10块' union all
select '小乔', '中餐', '20块' union all
select '小乔', '晚餐', '5块' union all
select '大乔', '早餐', '15块' union all
select '大乔', '中餐', '5块' union all
select '大乔', '晚餐', '10块'

select fname,
	早餐=max(case ftype when '早餐' then fvalue end),
	中餐=max(case ftype when '中餐' then fvalue end),
	晚餐=max(case ftype when '晚餐' then fvalue end)
from # group by fname
/*
fname    早餐       中餐       晚餐
-------- -------- -------- --------
大乔       15块      5块       10块
小乔       10块      20块      5块
*/

[开着拖拉机泡妞]

[Quote=引用 6 楼 sartner 的回复:]

回lzd_83:
在将 varchar 值 '10块' 转换成数据类型 int 时失败。

回TravyLee:
这个表是我临时创建的demo
那个Fvalue的值不一定是固定的格式.
也可能是中文, 比如: 十块, 二十块, 1美元, 一百日元..等等
so..不能截取字符串
[/Quote]

我请教别人给你写了，但是没结果，都快气毁了，变态的设计

[fanzhouqi]

,我写的奔泪了。
nnd，吐血，绝对的吐血

[开着拖拉机泡妞]

[Quote=引用 11 楼 fanzhouqi 的回复:]

,我写的奔泪了。
nnd，吐血，绝对的吐血
[/Quote]

把你写的分享给他了：

go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
fname varchar(10),
ftype varchar(10),
fvalue varchar(10)
)
go
insert tbl
select '小乔','早餐','一百十一块' union all
select '小乔','中餐','21美元' union all
select '小乔','晚餐','5块' union all
select '大乔','早餐','15块' union all
select '大乔','中餐','5块' union all
select '大乔','晚餐','10块'


go
if OBJECT_ID('t')is not null
drop table t
go
create TABLE t (VALUE VARCHAR(20), ivalue INT,hex BINARY(2) )
INSERT INTO t
SELECT '十',10,CAST('十' AS BINARY)
UNION ALL SELECT '百',100,CAST('百' AS BINARY)
UNION ALL SELECT '千',1000,CAST('千' AS BINARY)
UNION ALL SELECT '万',10000,CAST('万' AS BINARY)
UNION ALL SELECT '一',1,CAST('一' AS BINARY)
UNION ALL SELECT '二',2,CAST('二' AS BINARY)
UNION ALL SELECT '三',3,CAST('三' AS BINARY)
UNION ALL SELECT '四',4,CAST('四' AS BINARY)
UNION ALL SELECT '五',5,CAST('五' AS BINARY)
UNION ALL SELECT '六',6,CAST('六' AS BINARY)
UNION ALL SELECT '七',7,CAST('七' AS BINARY)
UNION ALL SELECT '八',8,CAST('八' AS BINARY)
UNION ALL SELECT '九',9,CAST('九' AS BINARY)
UNION ALL SELECT '零',0,CAST('零' AS BINARY)
go
alter FUNCTION dbo.moneylen( @value varchar(2000))
RETURNs int
AS
begin
DECLARE @i INT
SET @i = 0
IF (CAST(LEFT(@value,1) AS BINARY(2)) BETWEEN 0x30 AND 0x39)
SELECT @i = COUNT(1) FROM master..spt_values a
WHERE (CAST( SUBSTRING(@value,number+1,1) as BINARY(2)) < 0x30 OR CAST( SUBSTRING(@value,number+1,1) as BINARY(2)) > 0x39 )
AND type= 'p' AND number < LEN(@value)
else
SELECT @i = COUNT(1) FROM t a
right JOIN (SELECT hex = CAST( SUBSTRING(@value,number+1,1) as BINARY(2)) FROM master..spt_values
WHERE type= 'p' AND number < LEN(@value) ) b ON a.hex = b.hex
WHERE a.hex IS null


RETURN (@i);
end
go

go
if OBJECT_ID('tmpmiddle')is not null
drop table tmpmiddle
go
SELECT fname,ftype,fvalue
,n = CASE WHEN CAST(SUBSTRING(a.fvalue,b.number+1,1) AS BINARY(2)) BETWEEN 0x30 AND 0x39
THEN
CAST(SUBSTRING(a.fvalue,b.number+1,1) AS int) * POWER(10, LEN(a.fvalue)-1-b.number)
/ POWER(10, dbo.moneylen(a.fvalue))
ELSE
(SELECT TOP 1 ivalue FROM t WHERE hex = CAST(SUBSTRING(a.fvalue,b.number+1,1) AS BINARY(2)))
end
,b = REVERSE(left(REVERSE (a.fvalue),dbo.moneylen(a.fvalue)))
,m = 0
INTO tmpmiddle
FROM tbl a
INNER JOIN master..spt_values b ON LEN(fvalue) > b.number AND b.type = 'p'

--WITH detail
--AS(
--)

--DECLARE @c INT
--DECLARE @p INT
--SELECT @c = 0, @p = 0
--UPDATE tmpmiddle SET @p = CASE WHEN @c BETWEEN 0 AND 9 THEN 0 ELSE @p * @c END,@c = n ,
-- m = ISNULL(CASE WHEN @c BETWEEN 0 AND 9 THEN 0 ELSE @p * @c END,0)
--WHERE NOT CAST(LEFT(fvalue,1) AS BINARY(2)) BETWEEN 0x30 AND 0x39

SELECT *
FROM tmpmiddle
WHERE NOT CAST(LEFT(fvalue,1) AS BINARY(2)) BETWEEN 0x30 AND 0x39
AND n IS not null
--GROUP BY fname,ftype ,fvalue
--GROUP BY fname,ftype,fvalue
--WHERE
/*
select fname as 姓名,
max(case when ftype='早餐' then fvalue else ''end)
as 早餐,
max(case when ftype='中餐' then fvalue else ''end)
as 中餐,
max(case when ftype='晚餐' then fvalue else ''end)
as 晚餐
from tbl group by fname

SELECT CAST('一' AS BINARY(2))
SELECT CAST('二' AS BINARY(2))*/

楼主慢慢看吧

[Vidor]

楼上太壮观了，还有汇率问题要解决，开个玩笑不要见怪。

这种问题不是无解，而是无聊，属于前期数据处理范畴，根本不能进入数据库处理。

PS：中文数值转数字不用那么复杂的，逐个扫描汉字，乘加乘加就出来了。正则也可以。