【SQL卓越语句】分拆列值

作者:数据库

  • value FROM tb WHERE id=@id RETUCR-VN 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 二零零六中用OUTESportage 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 行受影响) */ --SQL2007中的方法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 行受影响) */

 1 --分拆列值 
 2 --原著:邹建 
 3 --改编:爱新觉罗.毓华(公斤年风霜,守得冰山雪水芝开) 2005-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 */

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) 

1 aa
1 bb
2 aaa
2 bbb
2 ccc

  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 ','

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

--方法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

联合列值 --******************************************************************************************* 表结构,数据如下: 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 的谷青阳和(字符串相加)

李四 74   84   94


SQL code

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

有表tb, 如下:
id value

张三 物理 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

本文由ca88发布,转载请注明来源

关键词: ca88网址 Sql Server 大火兔 SEO 数据库--MSSQL sql 行列