有点难的mssql的问题，关于将行转化为列的问题，希望大家帮帮我
有一个表
TBL01,字段如下
ID PayInfo
========================
1 学费:100|杂费:20
2 学费:100|书本费:10
3 学费:100|杂费:20|保险费:10
其中学费,杂费，书本费，保险费是由另一张表得到的
PayItems
ID MoneyName
===================
1 学费
2 杂费
3 书本费
4 保险费
因为历史原因结构已经不能动了，现在我想将PayInfo扩展为一个视图
View01,实现的效果如下
ID 学费 杂费 书本费 保险费
===================================================
1 100 20 0 0
2 100 0 10 0
3 100 20 0 10
请为这个视图应该如何创建？
[解决办法]
第二个表貌似没必要用到哦
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-04-15 22:05:52
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
--Jun 17 2011 00:57:23
--Copyright (c) Microsoft Corporation
--Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------
--> 测试数据：[TBL01]
if object_id('[TBL01]') is not null drop table [TBL01]
go
create table [TBL01]([ID] int,[PayInfo] varchar(26))
insert [TBL01]
select 1,'学费:100
[解决办法]
杂费:20' union all
select 2,'学费:100
[解决办法]
书本费:10' union all
select 3,'学费:100
[解决办法]
杂费:20
[解决办法]
保险费:10'
--> 测试数据：[PayItems]
if object_id('[PayItems]') is not null drop table [PayItems]
go
create table [PayItems]([ID] int,[MoneyName] varchar(6))
insert [PayItems]
select 1,'学费' union all
select 2,'杂费' union all
select 3,'书本费' union all
select 4,'保险费'
--------------开始查询--------------------------
SELECT id,[学费]=SUM(CASE WHEN payInfo='学费' THEN CONVERT(INT,[money]) ELSE 0 END ),
[杂费]=SUM(CASE WHEN payInfo='杂费' THEN CONVERT(INT,[money]) ELSE 0 END ),
[书本费]=SUM(CASE WHEN payInfo='书本费' THEN CONVERT(INT,[money]) ELSE 0 END ),
[保险费]=SUM(CASE WHEN payInfo='保险费' THEN CONVERT(INT,[money]) ELSE 0 END )
FROM (
SELECT id ,
SUBSTRING(SUBSTRING(PayInfo, number,
CHARINDEX('
[解决办法]
', PayInfo + '
[解决办法]
', number) - number), 1,
PATINDEX('%:%',
SUBSTRING(PayInfo, number,
CHARINDEX('
[解决办法]
', PayInfo + '
[解决办法]
', number)
- number)) - 1) PayInfo ,
SUBSTRING(SUBSTRING(PayInfo, number,
CHARINDEX('
[解决办法]
', PayInfo + '
[解决办法]
', number) - number),
PATINDEX('%:%',
SUBSTRING(PayInfo, number,
CHARINDEX('
[解决办法]
', PayInfo + '
[解决办法]
', number)
- number)) + 1,
LEN(SUBSTRING(PayInfo, number,
CHARINDEX('
------解决方案--------------------
', PayInfo + '
[解决办法]
', number) - number))) [Money]
FROM [TBL01] a ,
master..spt_values
WHERE number >= 1
AND number < LEN(PayInfo)
AND type = 'p'
AND SUBSTRING('
[解决办法]
' + PayInfo, number, 1) = '
[解决办法]
')a
GROUP BY ID
----------------结果----------------------------
/*
id 学费 杂费 书本费 保险费
----------- ----------- ----------- ----------- -----------
1 100 20 0 0
2 100 0 10 0
3 100 20 0 10
*/
create table TBL01
(ID int, PayInfo varchar(100))
insert into TBL01
select 1, '学费:100
[解决办法]
杂费:20' union all
select 2, '学费:100
[解决办法]
书本费:10' union all
select 3, '学费:100
[解决办法]
杂费:20
[解决办法]
保险费:10'
create table PayItems
(ID int, MoneyName varchar(10))
insert into PayItems
select 1, '学费' union all
select 2, '杂费' union all
select 3, '书本费' union all
select 4, '保险费'
-- 创建存储过程
create proc sp_View01
as
begin
declare @tsql varchar(6000), @pllist as varchar(100)
select @pllist=stuff(
(select ','+'['+MoneyName+']' from PayItems for xml path('')),1,1,'')
select @tsql='
with t as
(select a.ID,
substring(a.PayInfo,b.number,charindex(''
[解决办法]
'',a.PayInfo+''
[解决办法]
'',b.number)-b.number) ''p''
from TBL01 a
inner join master.dbo.spt_values b
on b.[type]=''P'' and b.number between 1 and len(a.PayInfo)
and substring(''
[解决办法]
''+a.PayInfo,b.number,1) = ''
[解决办法]
''
),
g as(
select e.ID,e.MoneyName,isnull(f.pv,0) ''pv''
from
(select c.ID,d.MoneyName
from (select distinct ID from TBL01) c
cross join (select MoneyName from PayItems) d) e
left join (select ID,
substring(p,1,charindex('':'',p)-1) ''pn'',
substring(p,charindex('':'',p)+1,100) ''pv''
from t) f on e.ID=f.ID and e.MoneyName=f.pn
)
select ID,'+@pllist+
' from g
pivot(max(pv) for MoneyName in('+@pllist+')) h
'
exec(@tsql)
end
-- 执行存储过程
exec sp_View01
/*
ID 学费 杂费 书本费 保险费
----------- --------- --------- --------- ---------
1 100 20 0 0
2 100 0 10 0
3 100 20 0 10
*/
