(3)合并列值与分拆列值

在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

 执行结果如下图:

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏C# 编程

3 - SQL Server 2008 之 使用SQL语句删除约束条件

基本语法为: ALTER TABLE 表名 DROP CONSTRAINT 约束名1,约束名2…… 如果删除一个约束,不需要逗号后的约束名,如果删除两个及两个以...

21600
来自专栏PHP在线

MySQL DELETE语句和TRUNCATE TABLE语句的区别

MySQL DELETE语句和TRUNCATE TABLE语句功能相似,但是二者究竟有何区别呢?下文就将为您分析MySQL DELETE语句和TRUNCATE ...

401140
来自专栏Python攻城狮

PostgreSQL语法、连接

博客地址:https://ask.hellobi.com/blog/zhiji 欢迎大家来交流学习。

14010
来自专栏Netkiller

数据库安全·用户/角色认证

以下节选择《Netkiller Architect 手札》 地址 http://www.netkiller.cn/architect/ 接下来几周的话题是数据库...

36250
来自专栏沃趣科技

innodb存储引擎锁的实现

通常,我们在95%以上的MySQL使用场景中,从一定程度上来讲,就是在使用InnoDB存储引擎,很多时候我们选择使用InnoDB存储引擎的原因,就是因为它支持高...

15850
来自专栏java工会

十个mysql语句的优化方法

21260
来自专栏散尽浮华

mysql操作命令梳理(2)-alter(update、insert)

在mysql运维操作中会经常使用到alter这个修改表的命令,alter tables允许修改一个现有表的结构,比如增加或删除列、创造或消去索引、改变现有列的类...

22560
来自专栏数据库

开发篇-MySQL分区(一)

MySQL从5.1版本开始支持分区的功能。分区是指根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言,逻辑上只有一个表或一...

25570
来自专栏Java呓语

第13.1.1章 语法解释ALTER DATABASE

ALTER DATABASE 允许修改数据库的特征,所有关于数据库的特征都存储在db.opt文件中。你必须先具备该数据库的ALTER特权,才能完成对数据库的AL...

8610
来自专栏乐沙弥的世界

SQL基础--> 约束(CONSTRAINT)

约束是表、列级的强制规定、是防止那些无效或有问题的数据输入到表中。当对该表进行DML

9920

扫码关注云+社区

领取腾讯云代金券