分拆列值

日期:2019-09-10编辑作者:数据大全

有表tb, 如下:
id value

SQL code

 1 --分拆列值 
 2 --原著:邹建 
 3 --改编:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开) 2007-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 2000) 
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 2005) 
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 */

/* 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)


合并列值 --******************************************************************************************* 表结构,数据如下: 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 2000中只能用函数解决。) --============================================================================= 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 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 2005中用OUTER 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 行受影响) */ --SQL2005中的方法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 作者:爱新觉罗.毓华(十八年风雨,守得战将传奇冰山雪莲花开) 时间:2008-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 2000) SELECT TOP 8000 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 2005) 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 2000提供静态和动态写法,version 2.0增加sql server 2005的有关写法。

问题:假设有张学生成绩表(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 SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
  max(case 课程 when'语文'then 分数 else0end) 语文,
  max(case 课程 when'数学'then 分数 else0end) 数学,
  max(case 课程 when'物理'then 分数 else0end) 物理
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+' 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 学期=''20081'' and 专业=''软件技术'') n
where m.学号 = n.学号 ')

--20081   -- @xq



/*
问题:如果上述两表互相换一下:即表结构和数据为:
姓名 语文 数学 物理
张三 74  83  93
李四 74  84  94
想变成(得到如下结果):
姓名 课程 分数


李四 语文 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

    2000中只能用函数解决。) 

    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 2005中用OUTER 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 行受影响) 
*/ 

--SQL2005中的方法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 2000)  
    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 2005) 
    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 行受影响) 
*/

本文由澳门金莎娱乐网站发布于数据大全,转载请注明出处:分拆列值

关键词:

VBA学习笔记,怎样往mysql中导数据执行效率高

这个警告在常规场景中没什么影响,但如果是用excel跑SQL,它会因为该警告阻止你的后续操作~事实上excel执行sql限制多...

详细>>

返回第一个不是null的值

澳门金莎娱乐手机版,Coalsece 返回第一个不是null的值    select top 5 [objname] ,[objjc] ,[objorder] ,[objid] ,[parentid] ,[grade] ,...

详细>>