这个SQL 语句如何写?

Question

table acontentA,B,C,DA1,B2,C2,D2
转为prptyA    prptyB   prptC   prptDA        B         C       D
A1       B1        C1      D1

Answer 1

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-03-24 17:11:54
-- Verstion:
--      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]([content] varchar(11))
insert [tb]
select 'A,B,C,D' union all
select 'A1,B2,C2,D2'
--------------开始查询--------------------------
SELECT COL  = PARSENAME(REPLACE(content,',','.'),4),
       COL1 = PARSENAME(REPLACE(content,',','.'),3),   ----替换一下 '.' 因为 parsename 只认 '.' 
       COL2 = PARSENAME(REPLACE(content,',','.'),2),
       COL3 = PARSENAME(REPLACE(content,',','.'),1)
 FROM tb
----------------结果----------------------------
/* COL                                                                                                                              COL1                                                                                                                             COL2                                                                                                                             COL3
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
A                                                                                                                                B                                                                                                                                C                                                                                                                                D
A1                                                                                                                               B2                                                                                                                               C2                                                                                                                               D2(2 行受影响)
*/

Answer 2

如果最多四个则可以用 PARSENAME

Answer 3

--------------------------------------------------------------------
IF OBJECT_ID('DBO.SPLIT_STR') IS NOT NULL
DROP FUNCTION DBO.SPLIT_STR
GO
CREATE FUNCTION DBO.SPLIT_STR(
@S varchar(8000),      --包含多个数据项的字符串
@INDEX int,             --要获取的数据项的位置
@SPLIT varchar(10)     --数据分隔符
)
RETURNS VARCHAR(100)
AS
BEGIN
IF @S IS NULL RETURN(NULL)
DECLARE @SPLITLEN int
SELECT @SPLITLEN=LEN(@SPLIT+'A')-2
WHILE @INDEX>1 AND CHARINDEX(@SPLIT,@S+@SPLIT)>0
SELECT @INDEX=@INDEX-1,@S=STUFF(@S,1,CHARINDEX(@SPLIT,@S+@SPLIT)+@SPLITLEN,'')
RETURN(ISNULL(LEFT(@S,CHARINDEX(@SPLIT,@S+@SPLIT)-1),''))
END
GO
PRINT DBO.SPLIT_STR('AA|BB|CC',2,'|')
--
GO

Answer 4

--轉老大
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_GetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_GetStr]
GO
--分段截取函数
CREATE FUNCTION dbo.f_GetStr(
@s varchar(8000),      --包含多个数据项的字符串
@pos int,             --要获取的数据项的位置
@split varchar(10)     --数据分隔符
)RETURNS varchar(100)
AS
BEGIN
    IF @s IS NULL RETURN(NULL)
    DECLARE @splitlen int
    SELECT @splitlen=LEN(@split+'a')-2
    WHILE @pos>1 AND CHARINDEX(@split,@s+@split)>0
        SELECT @pos=@pos-1,
            @s=STUFF(@s,1,CHARINDEX(@split,@s+@split)+@splitlen,'')
    RETURN(ISNULL(LEFT(@s,CHARINDEX(@split,@s+@split)-1),''))
END
GOselect dbo.f_GetStr('2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00 ',7,'∮')
/*
----------------------------------------------------------------------------------------------------
786(1 個資料列受到影響)------------更新
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_SetStr]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_SetStr]
GO
--分段更新函数
CREATE FUNCTION dbo.f_SetStr(
@s varchar(8000),      --包含数据项的字符串
@pos int,             --要更新的数据项的段
@value varchar(100),   --更新后的值
@split varchar(10)     --数据分隔符
)RETURNS varchar(8000)
AS
BEGIN
    DECLARE @splitlen int,@p1 int,@p2 int
    SELECT @splitlen=LEN(@split+'a')-2,
        @p1=1,
        @p2=CHARINDEX(@split,@s+@split)
    WHILE @pos>1 AND @p1<=@p2
        SELECT @pos=@pos-1,
            @p1=@p2+@splitlen+1,
            @p2=CHARINDEX(@split,@s+@split,@p1)
    RETURN(CASE
        WHEN @p1<@p2 THEN STUFF(@s,@p1,@p2-@p1,@value)
        WHEN @p2>LEN(@s) THEN @s+@value
        WHEN @p2=@p1 THEN STUFF(@s,@p1,0,@value) 
        ELSE @s END)
END
GO
select dbo.f_setstr('0,0,0,0,0,0,0,0',3,1,',')
/*
--------------------------
0,0,1,0,0,0,0,0(1 個資料列受到影響)
*/declare @tb table([ID] int,[STR] varchar(45))
insert @tb
select 1,'2∮11∮10∮09∮10∮13∮786∮91.93∮69∮100.00'
SELECT [STR]
FROM (
        SELECT a.id,
              [STR]=SUBSTRING(a.[STR],number,CHARINDEX('∮',a.[STR]+'∮',number)-b.number),ROW_NUMBER() OVER (ORDER BY GETDATE()) AS RID
        FROM @tb a
        JOIN master..spt_values b
        ON b.type='p' AND CHARINDEX('∮','∮'+a.[STR],number)=number
     ) A
WHERE A.RID=7/*
STR
---------------------------------------------
786(1 row(s) affected)create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date    :2003-10-14
as 
begin
    declare @i int
    set @SourceSql=rtrim(ltrim(@SourceSql))
    set @i=charindex(@StrSeprate,@SourceSql)
    while @i>=1
    begin
        insert @temp values(left(@SourceSql,@i-1))
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        set @i=charindex(@StrSeprate,@SourceSql)
    end
    if @SourceSql<>'' 
       insert @temp values(@SourceSql)
    return 
end
select * from dbo.f_split('1,2,3,4',',')a                                                                                                    
-------------------- 
1
2
3

Answer 5

引用的一个函数，如果你需要多列的话
CREATE FUNCTION [dbo].[split]
 (@str nvarchar(4000),@code varchar(10),@no int )  
RETURNS varchar(200)
AS  
BEGIN declare @intLen int
declare @count int
declare @indexb  int
declare @indexe  int
set @intLen=len(@code)
set @count=0
set @indexb=1if @no=0
  if charindex(@code,@str,@indexb)<>0
     return left(@str,charindex(@code,@str,@indexb)-1) 
  else
     return @str
while charindex(@code,@str,@indexb)<>0
  begin
       set @count=@count+1
       if @count=@no
         break
       set @indexb=@intLen+charindex(@code,@str,@indexb)
  end if @count=@no
  begin
      set @indexe=@intLen+charindex(@code,@str,@indexb)
          if charindex(@code,@str,@indexe)<>0
             return substring(@str,charindex(@code,@str,@indexb)+len(@code),charindex(@code,@str,@indexe)-charindex(@code,@str,@indexb)-len(@code))
          else 
             return right(@str,len(@str)-charindex(@code,@str,@indexb)-len(@code)+1)
  endreturn ''
ENDselect 
dbo.split(content,',',0),
dbo.split(content,',',1),
dbo.split(content,',',2),
dbo.split(content,',',3),
dbo.split(content,',',4),
dbo.split(content,',',5)
from 
(
select 'a,b,c,d,e,f'  content union all
select 'a1,b1,c1,d1,e1,f1' content
) tb-----------------------------------------
a b c d e f
a1 b1 c1 d1 e1 f1

Answer 6

-- 步骤比较繁琐（SQL Server 2005）
if object_id('tempdb..#') is not null
 drop table #;
go
create table # (id int identity,contents varchar(100));
go
insert into #
 select 'A,B,C,D' union all select 'A1,B2,C2,D2';
go;with t as(
select id,cast('<item>'+replace(contents,',','</item><item>')+'</item>' as xml) c from # 
),
t1 as(
select t.id,p.[prpty],row_number() over (partition by id order by prpty) rid
from t outer apply 
(select g.c.value('.','varchar(10)') [prpty] from t.c.nodes('//item') g(c)) p
)
select [1],[2],[3],[4] from t1 pivot
(max(prpty) for rid in ([1],[2],[3],[4])) p
/*
A B C D
A1 B2 C2 D2
*/