求助高手解答该SQL要如何实现!!!!!!!

Question

表的各字段内容 ，有一个字段内容会有3个|符号分割
 
字段1 字段2 字段3 字段4       字段5          字段6
a       b     c     d      aaa|bb|cc|d        e
a       b     c     d      aaa|bb|cc|d        e
a       b     c     d      aaa|bb|cc|d        e
a       b     c     d      aaa|bb|cc|d        e
a       b     c     d      aaa|bb|cc|d        e
查询后的效果将字段5的内容分别按|取出来显示在不同的字段字段1  字段2  字段3  字段4  字段5  字段6 字段7 字段8 字段9  
 a       b     c      d      aaa    bb    cc     d     e
 a       b     c      d      aaa    bb    cc     d     e
 a       b     c      d      aaa    bb    cc     d     e
 a       b     c      d      aaa    bb    cc     d     e
 a       b     c      d      aaa    bb    cc     d     e      

Answer 1

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-06-05 01:58:54
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([字段1] varchar(1),[字段2] varchar(1),[字段3] varchar(1),[字段4] varchar(1),[字段5] varchar(11),[字段6] varchar(1))
insert [tb]
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e'
--------------开始查询--------------------------
select
 字段1,
 字段2,
 字段3,
 字段4,
 字段5 = PARSENAME(REPLACE(字段5,'|','.'),4),   ----替换一下 '.' 因为 parsename 只认 '.' 
 字段6 = PARSENAME(REPLACE(字段5,'|','.'),3),
 字段7 = PARSENAME(REPLACE(字段5,'|','.'),2),
 字段8 = PARSENAME(REPLACE(字段5,'|','.'),2),
 字段6 as 字段9
from
  tb
----------------结果----------------------------
/* 字段1  字段2  字段3  字段4  字段5                                                                                                                              字段6                                                                                                                              字段7                                                                                                                              字段8                                                                                                                              字段9
---- ---- ---- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----
a    b    c    d    aaa                                                                                                                              bb                                                                                                                               cc                                                                                                                               cc                                                                                                                               e
a    b    c    d    aaa                                                                                                                              bb                                                                                                                               cc                                                                                                                               cc                                                                                                                               e
a    b    c    d    aaa                                                                                                                              bb                                                                                                                               cc                                                                                                                               cc                                                                                                                               e
a    b    c    d    aaa                                                                                                                              bb                                                                                                                               cc                                                                                                                               cc                                                                                                                               e
a    b    c    d    aaa                                                                                                                              bb                                                                                                                               cc                                                                                                                               cc                                                                                                                               e(5 行受影响)*/

Answer 2

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-06-05 01:58:54
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([字段1] varchar(1),[字段2] varchar(1),[字段3] varchar(1),[字段4] varchar(1),[字段5] varchar(11),[字段6] varchar(1))
insert [tb]
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e'
--------------开始查询--------------------------
select
 字段1,
 字段2,
 字段3,
 字段4,
 字段5 = PARSENAME(REPLACE(字段5,'|','.'),4),   ----替换一下 '.' 因为 parsename 只认 '.' 
 字段6 = PARSENAME(REPLACE(字段5,'|','.'),3),
 字段7 = PARSENAME(REPLACE(字段5,'|','.'),2),
 字段8 = PARSENAME(REPLACE(字段5,'|','.'),1),
 字段6 as 字段9
from
  tb
----------------结果----------------------------
/* 字段1  字段2  字段3  字段4  字段5                                                                                                                              字段6                                                                                                                              字段7                                                                                                                              字段8                                                                                                                              字段9
---- ---- ---- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----
a    b    c    d    aaa                                                                                                                              bb                                                                                                                               cc                                                                                                                               d                                                                                                                                e
a    b    c    d    aaa                                                                                                                              bb                                                                                                                               cc                                                                                                                               d                                                                                                                                e
a    b    c    d    aaa                                                                                                                              bb                                                                                                                               cc                                                                                                                               d                                                                                                                                e
a    b    c    d    aaa                                                                                                                              bb                                                                                                                               cc                                                                                                                               d                                                                                                                                e
a    b    c    d    aaa                                                                                                                              bb                                                                                                                               cc                                                                                                                               d                                                                                                                                e(5 行受影响)
*/

Answer 3

如果|号超过四个 用以下函数
--各种字符串分函数if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s   varchar(8000),   --待分拆的字符串
@split varchar(10)     --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
    DECLARE @splitlen int
    SET @splitlen=LEN(@split+'a')-2
    WHILE CHARINDEX(@split,@s)>0
    BEGIN
        INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
        SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
    END
    INSERT @re VALUES(@s)
    RETURN
END
GO

Answer 4

'aaa|bb|cc|d'字符串里的长度是不确定的如
'aaabb|bbcc|ccdd|dee'
'abb|bcc|cdd|deed'
'ccccc|aaa|bbbbbb|ssss'

Answer 5

create table [tb]([字段1] varchar(1),[字段2] varchar(1),[字段3] varchar(1),[字段4] varchar(1),[字段5] varchar(11),[字段6] varchar(1))
insert [tb]
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e'
select [字段1],[字段2],[字段3],[字段4]
    ,[字段5]=convert(xml,'<root><row>'+replace([字段5],'|','</row><row>')+'</row></root>').value('(root/row)[1]','nvarchar(10)')
    ,[字段6]=convert(xml,'<root><row>'+replace([字段5],'|','</row><row>')+'</row></root>').value('(root/row)[2]','nvarchar(10)')
    ,[字段7]=convert(xml,'<root><row>'+replace([字段5],'|','</row><row>')+'</row></root>').value('(root/row)[3]','nvarchar(10)')
    ,[字段8]=convert(xml,'<root><row>'+replace([字段5],'|','</row><row>')+'</row></root>').value('(root/row)[4]','nvarchar(10)')
    ,[字段6]
    from [tb]字段1  字段2  字段3  字段4  字段5        字段6        字段7        字段8        字段6
---- ---- ---- ---- ---------- ---------- ---------- ---------- ----
a    b    c    d    aaa        bb         cc         d          e
a    b    c    d    aaa        bb         cc         d          e
a    b    c    d    aaa        bb         cc         d          e
a    b    c    d    aaa        bb         cc         d          e
a    b    c    d    aaa        bb         cc         d          e(5 行受影响)

Answer 6

如果 没用sqlserver2005的话，就看 这个
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([字段1] varchar(1),[字段2] varchar(1),[字段3] varchar(1),[字段4] varchar(1),[字段5] varchar(11),[字段6] varchar(1))
insert [tb]
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e' union all
select 'a','b','c','d','aaa|bb|cc|d','e'
--1 加个序号，避免重复
select id=identity(int,1,1),* into #t from tb
--2 分解字段5
Select
    a.id,a.字段1,a.字段2,a.字段3,a.字段4,
    字段5=substring(a.字段5,b.number,charindex('|',a.字段5+'|',b.number)-b.number) ,a.字段6,
    b.number
into #t1
from 
    #t a join master..spt_values  b 
    ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.字段5)
where
     substring('|'+a.字段5,b.number,1)='|'--3 加小组内的序号，避免重复
select *,id1=(select count(1) from #t1 where id=t.id and number<=t.number)
into #t2
from #t1 t--4 合并显示
declare @sql varchar(8000)
set @sql = 'select 字段1,字段2,字段3,字段4 '
select @sql = @sql + ' , max(case id1 when ' + ltrim(id1) + ' then 字段5 else null end) [字段' +ltrim(id1+4) + ']'
from (select distinct id1 from #t2) as a
set @sql = @sql + ',字段6 as 字段'+(select ltrim(count(distinct id1)+5) from #t2)+' from #t2 group by id,字段1,字段2,字段3,字段4,字段6'
exec(@sql)--结果
/*
字段1  字段2  字段3  字段4  字段5         字段6         字段7         字段8         字段9  
---- ---- ---- ---- ----------- ----------- ----------- ----------- ---- 
a    b    c    d    aaa         bb          cc          d           e
a    b    c    d    aaa         bb          cc          d           e
a    b    c    d    aaa         bb          cc          d           e
a    b    c    d    aaa         bb          cc          d           e
a    b    c    d    aaa         bb          cc          d           e（所影响的行数为 5 行）警告: 聚合或其他 SET 操作消除了空值。
*/

Answer 7

如果只有每个字段都只有三个|,还是parsename方便,否则就用自定义函数或XML解决.