分拆列值

有表tb, 如下:
id value

SQL code

/* 1 */
declare @sql varchar(8000)
set @sql = ‘select ”总量” as [时间] ‘
select @sql = @sql + ‘ , sum(case 时间 when ‘ + rtrim(时间) + ‘ then 总量 end) [‘ + rtrim(时间) + ‘]’
from (
select DATEPART(hh, CreateTime) 时间,count(*) 总量 from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY  DATEPART(hh, CreateTime)

 1 –分拆列值 
 2 –原著:邹建 
 3 –改编:爱新觉罗.毓华(千克年风霜,守得冰山雪水芝开) 2006-12-16 安徽费城
 4 
 5 –有表tb, 如下: 
 6 –id value 
 7 ————- ———– 
 8 –1 aa,bb 
 9 –2 aaa,bbb,ccc 
10 –欲按id,分拆value列, 分拆后结果如下: 
11 –id value 
12 ————- ——– 
13 –1 aa 
14 –1 bb 
15 –2 aaa 
16 –2 bbb 
17 –2 ccc 
18 
19 –1. 旧的化解格局(sql server 3000) 
20 SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
21 
22 SELECT A.id, SUBSTRING(A.[values], B.id, CHARINDEX(‘,’, A.[values] + ‘,’, B.id) – B.id) 
23 FROM tb A, # B 
24 WHERE SUBSTRING(‘,’ + A.[values], B.id, 1) = ‘,’
25 
26 DROP TABLE #
27 
28 –2. 新的缓慢解决办法(sql server 2007) 
29 create table tb(id int,value varchar(30)) 
30 insert into tb values(1,’aa,bb’) 
31 insert into tb values(2,’aaa,bbb,ccc’) 
32 go 
33 SELECT A.id, B.value 
34 FROM( 
35 SELECT id, [value] = CONVERT(xml,’ <root> <v>’ + REPLACE([value], ‘,’, ‘ </v> <v>’) + ‘ </v> </root>’) FROM tb 
36 )A 
37 OUTER APPLY( 
38 SELECT value = N.v.value(‘.’, ‘varchar(100)’) FROM A.[value].nodes(‘/root/v’) N(v) 
39 )B
40 
41 DROP TABLE tb
42 
43 /* 
44 id value 
45 ———– —————————— 
46 1 aa 
分拆列值。47 1 bb 
48 2 aaa 
49 2 bbb 
50 2 ccc
51 
52 (5 行受影响)
53 */


统一列值 –******************************************************************************************* 表结构,数据如下: id value —–
—— 1 aa 1 bb 2 aaa 2 bbb 2 ccc 要求获得结果: id values
—— ———– 1 aa,bb 2 aaa,bbb,ccc 即:group
by id, 求 value 的谷青阳和(字符串相加)

)t ORDER BY 时间

1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value

  1. 旧的消除办法(在sql server
    三千中只好用函数消除。) –============================================================================= create
    table tb(id int, value
    varchar(10)) insert
    into tb values(1, ‘aa’)
    insert into tb
    values(1, ‘bb’)
    insert into tb
    values(2, ‘aaa’)
    insert into tb
    values(2, ‘bbb’)
    insert into tb
    values(2, ‘ccc’)
    go –1.
    成立管理函数 CREATE
    FUNCTION dbo.f_strUnite(@id
    int) RETURNS
    varchar(8000) AS
    BEGIN DECLARE
    @str varchar(8000) SET
    @str =
    ” SELECT
    @str =
    @str +
    ‘,’

set @sql = @sql + ‘ from (select DATEPART(hh, CreateTime) 时间,count(*) 总量   from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
 group by DATEPART(hh, CreateTime) 
)  t ‘
exec(@sql) 


  • value FROM tb
    WHERE id=@id
    RETU奥迪Q5N STUFF(@str,
    1, 1, ”)
    END GO
    — 调用函数
    SELECt id, value =
    dbo.f_strUnite(id) FROM tb
    GROUP BY id
    drop table tb
    drop function
    dbo.f_strUnite go /* id
    value ———– ———– 1 aa,bb 2 aaa,bbb,ccc (所影响的行数为 2
    行) */ –=================================================================================== 2. 新绿业Computer高校的消除措施(在sql server
    二零零五中用OUTEPAJERO APPLY等化解。) create
    table tb(id int, value
    varchar(10)) insert
    into tb values(1, ‘aa’)
    insert into tb
    values(1, ‘bb’)
    insert into tb
    values(2, ‘aaa’)
    insert into tb
    values(2, ‘bbb’)
    insert into tb
    values(2, ‘ccc’)
    go — 查询处理SELECT
    * FROM(SELECT
    DISTINCT id FROM tb)A
    OUTER APPLY( SELECT
    [values]=
    STUFF(REPLACE(REPLACE( (
    SELECT value FROM tb N
    WHERE id = A.id
    FOR XML AUTO ), ‘ <N
    value=”‘, ‘,’),
    ‘”/>’,
    ”), 1, 1, ”) )N
    drop table tb
    /* id values ———– ———– 1 aa,bb 2
    aaa,bbb,ccc (2 行受影响) */
    –SQL200第55中学的方法2 create
    table tb(id int, value
    varchar(10)) insert
    into tb values(1, ‘aa’)
    insert into tb
    values(1, ‘bb’)
    insert into tb
    values(2, ‘aaa’)
    insert into tb
    values(2, ‘bbb’)
    insert into tb
    values(2, ‘ccc’)
    go select id,
    [values]=stuff((select
    ‘,’+[value]
    from tb t where
    id=tb.id for xml
    path(”)), 1, 1, ”)
    from tb group
    by id /* id
    values ———– ——————– 1 aa,bb 2 aaa,bbb,ccc (2 row(s)
    affected) */ drop
    table tb /*
    题目:分拆列值1
    笔者:爱新觉罗.毓华(十三年风霜,守得老马神话冰山雪金泽芝开)
    时间:二零一零-11-20 地方:湖南尼科西亚 描述 有表tb, 如下: id value ———–
    ———– 1 aa,bb 2 aaa,bbb,ccc 欲按id,分拆value列, 分拆后结果如下: id
    value ———– ——– 1 aa 1 bb 2 aaa 2 bbb 2 ccc */
    –1. 旧的化解形式(sql server 三千) SELECT
    TOP 九千 id =
    IDENTITY(int,
    1, 1) INTO #
    FROM syscolumns a, syscolumns b SELECT
    A.id, value = SUBSTRING(A.[value], B.id,
    CHARINDEX(‘,’,
    A.[value]
  • ‘,’, B.id)
  • B.id) FROM tb A,
    # B WHERE SUBSTRING(‘,’
  • A.[value], B.id,
    1) =
    ‘,’
    DROP TABLE #
    –2. 新永生的缓慢解决格局(sql server 二〇〇五)
    create
    table tb(id int,value
    varchar(30)) insert
    into tb values(1,’aa,bb’)
    insert into tb
    values(2,’aaa,bbb,ccc’)
    go SELECT
    A.id, B.value FROM( SELECT id,
    [value]
    = CONVERT(xml,'<root><v>’
  • REPLACE([value],
    ‘,’,
    ‘</v><v>’)
  • ‘</v></root>’)
    FROM tb )A OUTER
    APPLY( SELECT value =
    N.v.value(‘.’,
    ‘varchar(100)’)
    FROM A.[value].nodes(‘/root/v’) N(v) )B
    DROP TABLE tb
    /* id value ———–
    —————————— 1 aa 1 bb 2 aaa 2 bbb 2 ccc (5 行受影响)
    */

/* 2 */
select DATEPART(hh, CreateTime) 时间,count(*) 总量 into #tb  from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY  DATEPART(hh, CreateTime)

1 aa
1 bb
2 aaa
2 bbb
2 ccc

declare @sql varchar(8000)
set @sql = ‘select ”总量” as [时间] ‘
select @sql = @sql + ‘ , sum(case 时间 when ‘ + rtrim(时间) + ‘ then 总量 end) [‘ + rtrim(时间) + ‘]’
from #tb ORDER BY 时间

–方法1.使用xml完成
SELECT A.id, B.value FROM
(
SELECT id, [value] = CONVERT(xml,'<root><v>’ +
REPLACE([value], ‘,’, ‘</v><v>’) +
‘</v></root>’) FROM tb
) A OUTER APPLY
(
SELECT value = N.v.value(‘.’, ‘varchar(100)’) FROM
A.[value].nodes(‘/root/v’) N(v)
) B

set @sql = @sql + ‘ from #tb  t ‘
exec(@sql) 
DROP TABLE #tb

–方法2.使用CTE完成
;with tt as
(select id,[value]=cast(left([value],charindex(‘,’,[value]+’,’)-1)
as
nvarchar(100)),Split=cast(stuff([value]+’,’,1,charindex(‘,’,[value]+’,’),”)
as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(‘,’,Split)-1) as
nvarchar(100)),Split= cast(stuff(Split,1,charindex(‘,’,Split),”) as
nvarchar(100)) from tt where split>”
)
select id,[value] from tt order by id option (MAXRECURSION 0)

/* 3 */
declare @s nvarchar(4000)
select  @s=isnull(@s+’,’,”)+quotename([时间])+’=sum(case when [时间]=’+quotename([时间],””)+’ then [总量] else 0 end)’
from 
(select DATEPART(hh, CreateTime) 时间,count(*) 总量 from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY  DATEPART(hh, CreateTime))t
 group by [时间] ORDER BY 时间
exec(‘select ”总量” as [时间], ‘+@s+’ from (
select DATEPART(hh, CreateTime) 时间,count(*) 总量 from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY  DATEPART(hh, CreateTime)
)t ‘)

DROP TABLE tb

/* 4 */
declare @s nvarchar(4000)
select  @s=isnull(@s+’,’,”)+quotename([时间]) from (select DATEPART(hh, CreateTime) 时间,count(*) 总量 from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY  DATEPART(hh, CreateTime)) t group by[时间] ORDER BY 时间
exec(‘select * from (select DATEPART(hh, CreateTime) 时间,count(*) 总量 from Business_Login 
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY  DATEPART(hh, CreateTime)  
 )t pivot (max([总量]) for [时间] in(‘+@s+’))b’)

 
/*
时间    9    10    11    12    13    14    15    16    17    18
总量    187    369    340    56    374    381    307    410    516    73
*/

 –按0,1,2…
SELECT DATEPART(hh, CreateTime) [hour],
       COUNT(*) cnt
FROM   Business_Login
WHERE  CreateTime > ( select CONVERT(varchar, getdate(), 111 ))
GROUP BY
       DATEPART(hh, CreateTime)
ORDER BY [hour] 

/*
hour    cnt
9    187
10    369
11    340
12    56
13    374
14    381
15    307
16    410
17    516
18    73
*/

/*
标题:普通行列调换(version 2.0)
作者:爱新觉罗.毓华(十两年风霜,守得冰山雪水华开)
时间:2008-03-09
地点:台湾费城
表明:普通行列转变(version 1.0)仅针对sql server
两千提供静态和动态写法,version 2.0充实sql server 二〇〇五的有关写法。

主题素材:假使有张学生战表表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想成为(获得如下结果):
姓名 语文 数学 物理


李四 74   84   94

张三 74   83   93

*/

createtable
tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insertinto tb
values(‘张三’ ,
‘语文’ ,
74)
insertinto tb
values(‘张三’ ,
‘数学’ ,
83)
insertinto tb
values(‘张三’ ,
‘物理’ ,
93)
insertinto tb
values(‘李四’ ,
‘语文’ ,
74)
insertinto tb
values(‘李四’ ,
‘数学’ ,
84)
insertinto tb
values(‘李四’ ,
‘物理’ ,
94)
go

–SQL SEEnclaveVEXC60 3000静态SQL,指科目唯有语文、数学、物理那三门科目。(以下同)
select 姓名 as 姓名 ,
  max(case 课程
when’语文’then 分数
else0end)
语文,
  max(case 课程
when’数学’then 分数
else0end)
数学,
  max(case 课程
when’物理’then 分数
else0end) 物理
from tb
groupby 姓名

–SQL SE奥德赛VE普拉多 三千动态SQL,指科目不仅仅语文、数学、物理这三门学科。(以下同)
declare@sqlvarchar(8000)
set@sql=’select
姓名 ‘
select@sql=@sql+’ ,
max(case 课程 when ”’+ 课程
+”’ then
分数 else 0 end) [‘+ 课程
+’]’
from (selectdistinct
课程 from tb) as a
set@sql=@sql+’ from tb
group by 姓名’
exec(@sql)

–SQL SERVER 2005 静态SQL。
select*from
(select*from tb) a
pivot (max(分数) for 课程
in (语文,数学,物理)) b

–SQL SERVER 2005 动态SQL。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+’],[‘ ,
”) + 课程
from tb groupby 课程
set@sql='[‘+@sql+’]’
exec (‘select *
from (select * from tb) a pivot (max(分数) for 课程 in (‘+@sql+’))
b’)


/*
主题素材:在上述结果的功底上加平均分,总分,获得如下结果:
姓名 语文 数学 物理 平均分 总分


李四 74   84   94   84.00  252
张三 74   83   93   83.33  250
*/

–SQL SERVER 2000 静态SQL。
select 姓名 姓名,
  max(case 课程
when’语文’then 分数
else0end)
语文,
  max(case 课程
when’数学’then 分数
else0end)
数学,
  max(case 课程
when’物理’then 分数
else0end)
物理,
  cast(avg(分数*1.0) asdecimal(18,2)) 平均分,
  sum(分数) 总分
from tb
groupby 姓名

–SQL SERVER 2000 动态SQL。
declare@sqlvarchar(8000)
set@sql=’select
姓名 ‘
select@sql=@sql+’ ,
max(case 课程 when ”’+ 课程
+”’ then
分数 else 0 end) [‘+ 课程
+’]’
from (selectdistinct
课程 from tb) as a
set@sql=@sql+’ ,
cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb
group by 姓名’
exec(@sql)

–SQL SERVER 2005 静态SQL。
select m.* ,
n.平均分 , n.总分 from
(select*from
(select*from tb) a
pivot (max(分数) for 课程
in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) asdecimal(18,2)) 平均分 , sum(分数)
总分 from tb groupby 姓名)
n
where m.姓名 = n.姓名

–SQL SERVER 2005 动态SQL。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+’,’ ,
”) +'[‘+ltrim(课程名称)+’]’from tb
groupby 课程
exec (‘select
m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in
(‘+@sql+’)) b) m
,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数)
总分 from tb group by 姓名) n
where m.姓名 = n.姓名’)

droptable
tb  

ALTERPROCEDURE[dbo].[pro_score]

AS
declare@xqvarchar(20)
declare@zyvarchar(20)
declare@sqlvarchar(1000)

select@sql=isnull(@sql+’,’ ,
”) +'[‘+ltrim(课程名称)+’]’from
score_view groupby
课程名称

exec (‘select
m.*, n.平均分,n.总分 from (select * from (select
学号,姓名,学期,职业,课程名称,成绩 from score_view   ) a
pivot (max(战绩) for 课程名称 in (‘+@sql+’) )  b ) m
,
(select 学号, 学期,专业,cast(avg(成绩*1.0) as
decimal(18,2))平均分,sum(成绩)总分 from score_view  group by
学号,学期,专门的工作 having 学期=”二〇一〇1” and
职业=”软件本事”) n
where m.学号 = n.学号 ‘)

–20081   — @xq



/*
主题素材:假设上述两表相互换一下:即表结商谈多少为:
姓名 语文 数学 物理
张三 74  83  93
李四 74  84  94
997755.com澳门葡京,想产生(获得如下结果):
姓名 课程 分数


李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83

张三 物理 93

*/

createtable
tb(姓名 varchar(10) , 语文 int , 数学
int , 物理 int)
insertinto tb
values(‘张三’,74,83,93)
insertinto tb
values(‘李四’,74,84,94)
go

–SQL SERVER 2000 静态SQL。
select*from
(
select 姓名 , 课程 =’语文’ , 分数
= 语文 from tb
unionall
select 姓名 , 课程 =’数学’ , 分数
= 数学 from tb
unionall
select 姓名 , 课程 =’物理’ , 分数
= 物理 from tb
) t
orderby 姓名 ,
case 课程 when’语文’then1when’数学’then2when’物理’then3end

–SQL SERVER 2000 动态SQL。
–调用系统表动态生态。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+’ union all
‘ , ” )
+’ select
姓名 , [课程] = ‘+quotename(Name , ””)
+’ ,
[分数] = ‘+quotename(Name) +’ from
tb’
from syscolumns
where name! =
N’姓名’and ID
=object_id(‘tb’)
–表名tb,不含有列名字为现名的别样列
orderby colid
asc
exec(@sql+’ order by
姓名 ‘)

–SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb
unpivot (分数 for 课程 in([语文] ,
[数学] ,
[物理])) t

–SQL SERVER 2005 动态SQL,同SQL SERVER 2000
动态SQL。


/*
问题:在上述的结果上加个平均分,总分,得到如下结果:
姓名 课程   分数


李四 语文   74.00
李四 数学   84.00
李四 物理   94.00
李四 平均分 84.00
李四 总分   252.00
张三 语文   74.00
张三 数学   83.00
张三 物理   93.00
张三 平均分 83.33

张三 总分   250.00

*/

select*from
(
select 姓名 as 姓名 ,
课程 =’语文’ , 分数
= 语文 from tb
unionall
select 姓名 as 姓名 ,
课程 =’数学’ , 分数
= 数学 from tb
unionall
select 姓名 as 姓名 ,
课程 =’物理’ , 分数
= 物理 from tb
unionall
select 姓名 as 姓名 ,
课程 =’平均分’ , 分数
=cast((语文

  • 数学 +
    物理)*1.0/3asdecimal(18,2)) from tb
    unionall
    select 姓名 as 姓名 ,
    课程 =’总分’ , 分数
    = 语文 + 数学
  • 物理 from tb
    ) t
    orderby 姓名 ,
    case 课程 when’语文’then1when’数学’then2when’物理’then3when’平均分’then4when’总分’then5end

droptable tb

 

 

 

/*表结构,数据如下: 
id    value 
—– —— 
1    aa 
1    bb 
2    aaa 
2    bbb 
2    ccc 

内需得到结果: 
id    values 
—— ———– 
1      aa,bb 
2      aaa,bbb,ccc 
即:group by id, 求 value 的和(字符串相加) */

  1. 旧的消除方法(在sql server

    三千中只好用函数消除。) 

    create table tb(id
    int, value varchar(10)) 
    insert into tb
    values(1, ‘aa’) 
    insert into tb
    values(1, ‘bb’) 
    insert into tb
    values(2, ‘aaa’) 
    insert into tb
    values(2, ‘bbb’) 
    insert into tb
    values(2, ‘ccc’) 
    go 
    –1. 成立管理函数
    CREATE FUNCTION
    dbo.f_strUnite(@id int) 
    RETURNS varchar(8000) 
    AS 
    BEGIN 
        DECLARE @str varchar(8000) 
        SET @str = ” 
        SELECT @str = @str + ‘,’ + value
    FROM tb WHERE
    id=@id 
        RETURN STUFF(@str,
    1, 1, ”) 
    END 
    GO 
    — 调用函数
    SELECt id, value =
    dbo.f_strUnite(id) FROM tb
    GROUP BY id 
    drop table tb 
    drop function
    dbo.f_strUnite 
    go
    /* 
    id          value      
    ———– ———– 
    1          aa,bb 
    2          aaa,bbb,ccc 
    (所影响的行数为 2 行) 

    */ 

  2. 新的减轻措施(在sql server 二〇〇五中用OUTEWrangler应用程式LY等消除。) 
    create table tb(id
    int, value varchar(10)) 
    insert into tb
    values(1, ‘aa’) 
    insert into tb
    values(1, ‘bb’) 
    insert into tb
    values(2, ‘aaa’) 
    insert into tb
    values(2, ‘bbb’) 
    insert into tb
    values(2, ‘ccc’) 
    go 
    — 查询管理
    SELECT * FROM(SELECT DISTINCT id
    FROM tb)A OUTER
    APPLY( 
            SELECT [values]= STUFF(REPLACE(REPLACE( 
                ( 
                    SELECT value FROM tb

                    WHERE id = A.id 
                    FOR XML AUTO 
                ), ‘ <N value=”‘,
    ‘,’),
    ‘”/>’,
    ”), 1, 1, ”) 
    )N 
    drop table tb 

/* 
id          values 
———– ———– 
1          aa,bb 
2          aaa,bbb,ccc 

(2 行受影响) 
*/ 

–SQL200第55中学的方法2
create table tb(id
int, value varchar(10)) 
insert into tb
values(1, ‘aa’) 
insert into tb
values(1, ‘bb’) 
insert into tb
values(2, ‘aaa’) 
insert into tb
values(2, ‘bbb’) 
insert into tb
values(2, ‘ccc’) 
go 
select id, [values]=stuff((select ‘,’+[value] from tb t
where id=tb.id 
for xml path(”)),
1, 1, ”) 
from tb 
group by id 
/* 
id          values 
———– ——————– 
1          aa,bb 
2          aaa,bbb,ccc  
*/ 

drop table tb 

 

 

 

 

/*有表tb,
如下: 
id          value 
———– ———– 
1          aa,bb 
2          aaa,bbb,ccc 
欲按id,分拆value列, 分拆后结果如下: 
id          value 
———– ——– 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc */

  1. 旧的解决措施(sql server 两千)  
    select a.id, substring(a.[value],
    b.number, charindex(‘,’,
    a.[value] + ‘,’,
    b.number) –
    b.number) 
    FROM tb a, master..spt_values  b 
    WHERE b.type=’p’ and substring(‘,’ +
    a.[value],b.number,
    1) = ‘,’ 

  2. 新的解决方法(sql server 二零零五) 
    create table tb(id
    int,value varchar(30)) 
    insert into tb
    values(1,’aa,bb’) 
    insert into tb
    values(2,’aaa,bbb,ccc’) 
    go 
    SELECT a.id, b.value 
    FROM( 
        SELECT id, [value] = CONVERT(xml,’
    <root> <v>’ + REPLaCE([value],
    ‘,’,
    ‘ </v> <v>’)
    + ‘
    </v> </root>’)
    FROM tb 
    )a 
    OUTER aPPLY( 
        SELECT value =
    N.v.value(‘.’,
    ‘varchar(100)’)
    FROM a.[value].nodes(‘/root/v’) N(v) 
    )b 

DROP TabLE tb 

/* 
id          value 
———– —————————— 
1          aa 
1          bb 
2          aaa 
2          bbb 
2          ccc 

(5 行受影响) 
*/

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website