有点难的mssql的问题，关于将行转化为列的问题，希望大家帮帮我

Question

有一个表
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请为这个视图应该如何创建？

Answer 1

第二个表貌似没必要用到哦----------------------------------------------------------------
-- 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
*/

Answer 2

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 view View01
as
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,[学费],[杂费],[书本费],[保险费]
 from g  
 pivot(max(pv) for MoneyName in([学费],[杂费],[书本费],[保险费])) h
select * from View01/*
ID          学费       杂费       书本费      保险费
----------- --------- --------- --------- ---------
 1       100  20      0         0
 2       100  0      10         0
 3       100  20      0         10
*/

Answer 3

建议改用存储过程实现..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
*/