开发者社区> 阿笨net> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL语法集锦三:合并列值与分拆列值

简介: 本文转载http://www.cnblogs.com/lxblog/archive/2012/09/29/2708724.html   在SQL中分拆列值和合并列值老生常谈了,从网上搜刮了一下并记录下来,以便不时之需 :) 什么叫分拆列值和合并列值呢?就只是这样的,比如有如下表A结构及数据...
+关注继续查看

本文转载http://www.cnblogs.com/lxblog/archive/2012/09/29/2708724.html

 

在SQL中分拆列值和合并列值老生常谈了,从网上搜刮了一下并记录下来,以便不时之需 :)

什么叫分拆列值和合并列值呢?就只是这样的,比如有如下表A结构及数据:

Id Data
1 aa,bb
2 aaa,bbb,ccc

将该表A的Data字段数据根据 “,” 进行分拆得到如下表B

Id Data
1 aa
1 bb
2 aaa
2 bbb
2 ccc

这就是表A-->表B 叫做分拆列值,表B-->表A 叫做合并列值。

一、分拆列值:

复制代码
CREATE TABLE t_Demo1
(
  Id INT,
  Data VARCHAR(30)
)
GO

INSERT INTO t_Demo1 VALUES(1,'aa,bb') 
INSERT INTO t_Demo1 VALUES(2,'aaa,bbb,ccc')
复制代码

分拆方法一:(古老方法,适合于SQL 2000)

复制代码
--方法一:适用于SQL 2000
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO #t FROM syscolumns a, syscolumns b

SELECT A.Id, SUBSTRING(A.Data, B.Id, CHARINDEX(',', A.Data + ',', B.Id) - B.Id) AS Data
FROM t_Demo1 as A, #t as B 
WHERE SUBSTRING(',' + A.Data, B.Id, 1) = ',' 

DROP TABLE #t
复制代码

分拆方法二:(适合于SQL2005及以上版本)

复制代码
--方法二:适用于SQL 2005及之后版本
SELECT A.Id, B.Data 
FROM( 
    SELECT Id, Data = CONVERT(xml,' <root> <v>' + REPLACE(Data, ',', ' </v> <v>') + ' </v> </root>') FROM t_Demo1 
)A 
OUTER APPLY( 
    SELECT Data = N.v.value('.', 'varchar(100)') FROM A.Data.nodes('/root/v') N(v) 
)B 
复制代码

执行结果如下图:

二、合并列值:

复制代码
REATE TABLE t_Demo2
(
  Id INT,
  Data VARCHAR(30)
)
GO
INSERT INTO t_Demo2 VALUES(1, 'aa') 
INSERT INTO t_Demo2 VALUES(1, 'bb') 
INSERT INTO t_Demo2 VALUES(2, 'aaa') 
INSERT INTO t_Demo2 VALUES(2, 'bbb') 
INSERT INTO t_Demo2 VALUES(2, 'ccc') 
复制代码

合并方法一:(适用于SQL2000 版本,只能用函数的方式来实现)

复制代码
--方法一(适用于SQL2000 版本只能用函数的方式来实现):
CREATE FUNCTION dbo.Fun_GetStr(@id INT) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
 DECLARE @r VARCHAR(8000) 
 SET @r = '' 
 SELECT @r = @r + ',' + CAST(Data AS VARCHAR) FROM t_Demo2 WHERE id=@id 
 SET @r=STUFF(@r, 1, 1, '')
 --或者
 --SET @r=RIGHT(@r , len(@r) - 1) 
 RETURN @r
END 
GO
--使用该函数
SELECT Id, Data = dbo.Fun_GetStr(Id) FROM t_Demo2 GROUP BY Id
--或者
SELECT DISTINCT Id ,dbo.Fun_GetStr(Id) AS Data FROM t_Demo2 
--删除该函数
DROP FUNCTION dbo.Fun_GetStr
GO
复制代码

合并方法二:(适用于SQL2005及其以后版本)

--方法二(适用于SQL2005及其以后版本)
SELECT Id, Data=STUFF((SELECT ','+ Data FROM t_Demo2 AS t WHERE Id=t_Demo2.Id FOR XML PATH('')), 1, 1, '') 
FROM t_Demo2 
GROUP BY Id 

执行结果如下图:

三、合并列值并使用聚合函数:

复制代码
CREATE TABLE t_Price
(
  Id INT,
  Salary FLOAT, 
  Name NVARCHAR(10)
)
GO

INSERT INTO t_Price VALUES(1,10,'小明')
INSERT INTO t_Price VALUES(1,20,'小红')
INSERT INTO t_Price VALUES(2,50,'小兰')
INSERT INTO t_Price VALUES(2,100,'小七')
GO

--方法一:(利用函数)
CREATE FUNCTION Fun_HbStr(@id int)
 RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @s NVARCHAR(100)
    SELECT @s=ISNULL(@s+',','')+ CAST(Name AS NVARCHAR) FROM t_Price where id=@id
    RETURN @s
END
GO
SELECT Id ,SUM(Salary) AS Salary,dbo.Fun_HbStr(Id) AS Data FROM t_Price GROUP BY Id
--删除函数
DROP FUNCTION Fun_HbStr
GO
--方法二:
SELECT Id, SUM(Salary) AS Salary,name=STUFF((SELECT ','+ Name FROM t_Price AS t WHERE Id=t_Price.Id FOR XML PATH('')), 1, 1, '') 
FROM t_Price 
GROUP BY Id 

--删除表 t_Price
DROP TABLE t_Price
GO
复制代码

执行结果如下图:

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)|学习笔记
快速学习3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(四)
0 0
3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(三)|学习笔记
快速学习3 PostgreSQL psql的使用,SQL语法,数据类型,递归SQL用法(三)
0 0
网站流量日志分析--统计分析--sql补充扩展--group by 语法限制解析|学习笔记
快速学习网站流量日志分析--统计分析--sql补充扩展--group by 语法限制解析
0 0
SQL 语法--表特定语句--分组、排序、过滤 | 学习笔记
快速学习 SQL 语法--表特定语句--分组、排序、过滤
0 0
SQL 语法--表特定语句--alter、drop、view | 学习笔记
快速学习 SQL 语法--表特定语句--alter、drop、view
0 0
SQL 语法--表数据导入方式 | 学习笔记
快速学习 SQL语法--表数据导入方式
0 0
SQL 语法--数据库特定语句 | 学习笔记
快速学习 SQL 语法--数据库特定语句
0 0
SQL 语法--表特定语句--create、insert、desc | 学习笔记
快速学习 SQL 语法--表特定语句--create、insert、desc
0 0
SQL语法过滤|学习笔记
快速学习SQL语法过滤
0 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
时序数据库TSDB新功能 - 如何用SQL进行时序查询
立即下载
PostgresChina2018_刘东明_PostgreSQL并行查询
立即下载
MaxCompute SQL计算成本调优以及优化方法
立即下载