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

本文转载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

执行结果如下图:

时间: 2024-11-01 21:27:10

SQL语法集锦三:合并列值与分拆列值的相关文章

CSS 语法由三部分构成:选择器、属性和值

CSS 语法 CSS 语法由三部分构成:选择器.属性和值: selector {property: value} 选择器 (selector) 通常是你希望定义的 HTML 元素或标签,属性 (property) 是你希望改变的属性,并且每个属性都有一个值.属性和值被冒号分开,并由花括号包围,这样就组成了一个完整的样式声明(declaration): body {color: blue} 上面这行代码的作用是将 body 元素内的文字颜色定义为蓝色.在上述例子中,body 是选择器,而包括在花括

PostgreSQL SQL语法(三):调用函数

本文档为PostgreSQL 9.6.0文档,本转载已得到原译者彭煜玮授权. PostgreSQL允许带有命名参数的函数被使用位置或命名记号法调用.命名记号法对于有大量参数的函数特别有用,因为它让参数和实际参数之间的关联更明显和可靠.在位置记号法中,书写一个函数调用时,其参数值要按照它们在函数声明中被定义的顺序书写.在命名记号法中,参数根据名称匹配函数参数,并且可以以任何顺序书写. 在任意一种记号法中,在函数声明中给出了默认值的参数根本不需要在调用中写出.但是这在命名记号法中特别有用,因为任何参

SQL语法集锦一:SQL语句实现表的横向聚合

本文转载:http://www.cnblogs.com/lxblog/archive/2012/09/29/2708128.html   问题描述:假如有一表结构和数据如下: C1 C2 C3 C4 C5 1 2 6 3 4 2 2 3 4 5 0 3 6 2 8 经过sql查询后输出的结果集为:(字段后面增加聚合[最大值] [最小值] [>=5的值个数]) C1 C2 C3 C4 C5 Max Min Count(>=5) 1 2 6 3 4 6 1 1 2 2 3 4 5 5 2 1 0

SQL语法集锦一:显示每个类别最新更新的数据

本文转载http://www.cnblogs.com/lxblog/archive/2012/09/28/2707504.html (1)显示每个类别最新更新的数据 在项目中经常遇到求每个类别最新显示的数据,比如显示某某某类别最新更新的5条数据.特写下这个sql记录于此: -- ============================================= -- 作者: <Rising_Sun> -- 创建日期: <2012-9-28> -- 描述: <显示每个类

【交流】SQL 2005溢用之:合并列值

问题描述: 无论是在sql 2000, 还是在 sql 2005 中,都没有提供字符串的聚合函数, 所以, 当我们在处理下列要求时,会比较麻烦:   有表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,

解析SQL Server 2005 溢用之:合并列值

很多人可能发现,无论是在sql 2000, 还是在 sql 2005 中,都没有提供字符串的聚合函数, 所以, 当我们在处理下列要求时,会比较麻烦, 但在 SQL Server 2005中, 这种情况得到了改善, 我们可以轻松地完成这项处理. 问题描述:无论是在sql 2000, 还是在 sql 2005 中,都没有提供字符串的聚合函数, 所以, 当我们在处理下列要求时,会比较麻烦:有表tb, 如下:id value----- ------1 aa1 bb2 aaa2 bbb2 ccc需要得到结

PostgreSQL SQL语法(二):值表达式

本文档为PostgreSQL 9.6.0文档,本转载已得到原译者彭煜玮授权. 值表达式被用于各种各样的环境中,例如在SELECT命令的目标列表中.作为INSERT或UPDATE中的新列值或者若干命令中的搜索条件.为了区别于一个表表达式(是一个表)的结果,一个值表达式的结果有时候被称为一个标量.值表达式因此也被称为标量表达式(或者甚至简称为表达式).表达式语法允许使用算数.逻辑.集合和其他操作从原始部分计算值. 一个值表达式是下列之一: 一个常量或文字值 一个列引用 在一个函数定义体或预备语句中的

sql合并列转行,在线等

问题描述 sql合并列转行,在线等 有表 wareid makeno qty busno 10001 11111 5 111 10001 11111 10 222 10002 22222 11 111 10002 33311 20 222 得到 wareid makeno qty busno 10001 11111 5@10@ 111@222@ 10002 22222 11@0@ 111@0@ 10002 33311 0@20@ 0@222@ 解决方案 可以这么写 select wareid ,

SQL语句集锦

--语 句                                功 能--数据操作SELECT      --从数据库表中检索数据行和列INSERT      --向数据库表添加新数据行DELETE      --从数据库表中删除数据行UPDATE      --更新数据库表中的数据--数据定义CREATE TABLE    --创建一个数据库表DROP TABLE     --从数据库中删除表ALTER TABLE     --修改数据库表结构CREATE VIEW     --创建