`
volunteer521
  • 浏览: 205705 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

Sql合并表、行列转换经典案例

    博客分类:
  • SQL
阅读更多
案例:表A:STUDENT
STUDENTID,STUDENTNAME
1,        'BOY A'
2,        'BOY B'

表B:SUBJECT
STUDENTID,SUBJECT
1,      '语文'
1,      '数学'
1,      '英语'
1,      '化学
1,      '物理'
1,      '政治'
1,      '体育'
2,      '语文'

STUDENTNAME  SUBJECTS
'BOY A'        '语文,数学,英语...'
'BOY B'        '语文'
 

SQL code问题描述:
无论是在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, 求 value 的和(字符串相加)

1. 旧的解决方法

-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
   
DECLARE @r varchar(8000)
   
SET @r = ''
   
SELECT @r = @r + ',' + value
   
FROM tb
   
WHERE id=@id
   
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数

SELECt id, values=dbo.f_str(id)
FROM tb
GROUP BY id

-- 2. 新的解决方法
--
示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'

-- 查询处理
SELECT *
FROM(
   
SELECT DISTINCT
        id
   
FROM @t
)A
OUTER APPLY(
   
SELECT
       
[values]= STUFF(REPLACE(REPLACE(
            (
               
SELECT value FROM @t N
               
WHERE id = A.id
               
FOR XML AUTO
            ),
'<N value="', ','), '"/>', ''), 1, 1, '')
)N

/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--
*/

--各种字符串分函数

--3.3.1 使用游标法进行字符串合并处理的示例。
--
处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

--合并处理
--
定义结果集表变量
DECLARE @t TABLE(col1 varchar(10),col2 varchar(100))

--定义游标并进行合并处理
DECLARE tb CURSOR LOCAL
FOR
SELECT col1,col2 FROM tb ORDER BY  col1,col2
DECLARE @col1_old varchar(10),@col1 varchar(10),@col2 int,@s varchar(100)
OPEN tb
FETCH tb INTO @col1,@col2
SELECT @col1_old=@col1,@s=''
WHILE @@FETCH_STATUS=0
BEGIN
   
IF @col1=@col1_old
       
SELECT @s=@s+','+CAST(@col2 as varchar)
   
ELSE
   
BEGIN
       
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
       
SELECT @s=','+CAST(@col2 as varchar),@col1_old=@col1
   
END
   
FETCH tb INTO @col1,@col2
END
INSERT @t VALUES(@col1_old,STUFF(@s,1,1,''))
CLOSE tb
DEALLOCATE tb
--显示结果并删除测试数据
SELECT * FROM @t
DROP TABLE tb
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--
*/
GO


/*==============================================*/


--3.3.2 使用用户定义函数,配合SELECT处理完成字符串合并处理的示例
--
处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
GO

--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
   
DECLARE @re varchar(100)
   
SET @re=''
   
SELECT @re=@re+','+CAST(col2 as varchar)
   
FROM tb
   
WHERE col1=@col1
   
RETURN(STUFF(@re,1,1,''))
END
GO

--调用函数
SELECT col1,col2=dbo.f_str(col1) FROM tb GROUP BY col1
--删除测试
DROP TABLE tb
DROP FUNCTION f_str
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--
*/
GO

/*==============================================*/


--3.3.3 使用临时表实现字符串合并处理的示例
--
处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3

--合并处理
SELECT col1,col2=CAST(col2 as varchar(100))
INTO #t FROM tb
ORDER BY col1,col2
DECLARE @col1 varchar(10),@col2 varchar(100)
UPDATE #t SET
   
@col2=CASE WHEN @col1=col1 THEN @col2+','+col2 ELSE col2 END,
   
@col1=col1,
    col2
=@col2
SELECT * FROM #t
/*--更新处理后的临时表
col1       col2
---------- -------------
a          1
a          1,2
b          1
b          1,2
b          1,2,3
--
*/
--得到最终结果
SELECT col1,col2=MAX(col2) FROM #t GROUP BY col1
/*--结果
col1       col2
---------- -----------
a          1,2
b          1,2,3
--
*/
--删除测试
DROP TABLE tb,#t
GO


/*==============================================*/

--3.3.4.1 每组 <=2 条记录的合并
--
处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'c',3

--合并处理
SELECT col1,
    col2
=CAST(MIN(col2) as varchar)
       
+CASE
           
WHEN COUNT(*)=1 THEN ''
           
ELSE ','+CAST(MAX(col2) as varchar)
       
END
FROM tb
GROUP BY col1
DROP TABLE tb
/*--结果
col1       col2     
---------- ----------
a          1,2
b          1,2
c          3
--
*/

--3.3.4.2 每组 <=3 条记录的合并
--
处理的数据
CREATE TABLE tb(col1 varchar(10),col2 int)
INSERT tb SELECT 'a',1
UNION ALL SELECT 'a',2
UNION ALL SELECT 'b',1
UNION ALL SELECT 'b',2
UNION ALL SELECT 'b',3
UNION ALL SELECT 'c',3

--合并处理
SELECT col1,
    col2
=CAST(MIN(col2) as varchar)
       
+CASE
           
WHEN COUNT(*)=3 THEN ','
               
+CAST((SELECT col2 FROM tb WHERE col1=a.col1 AND col2 NOT IN(MAX(a.col2),MIN(a.col2))) as varchar)
           
ELSE ''
       
END
       
+CASE
           
WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2) as varchar)
           
ELSE ''
       
END
FROM tb a
GROUP BY col1
DROP TABLE tb
/*--结果
col1       col2
---------- ------------
a          1,2
b          1,2,3
c          3
--
*/
GO
if not object_id('A') is null
   
drop table A
Go
Create table A([id] int,[cname] nvarchar(2))
Insert A
select 1,N'张三' union all
select 2,N'李四' union all
select 3,N'王五' union all
select 4,N'蔡六'
Go
--> -->

if not object_id('B') is null
   
drop table B
Go
Create table B([id] int,[cname] nvarchar(5))
Insert B
select 1,N'1,2,3' union all
select 2,N'3,4'
Go
create function F_str(@cname nvarchar(100))
returns nvarchar(100)
as
begin
select @cname=replace(@cname,ID,[cname]) from A where patindex('%,'+rtrim(ID)+',%',','+@cname+',')>0
return @cname
end
go
select [id],dbo.F_str([cname])[cname] from B

id          cname
----------- ----------------------------------------------------------------------------------------------------
1           张三,李四,王五
2           王五,
分享到:
评论

相关推荐

    sql server 行列转换

    本文将通过一个具体的SQL Server示例来深入探讨如何实现行列转换。 #### 一、创建视图(Step 1) 首先,我们需要创建一个视图,该视图将两个表`TableA`和`TableB`的数据合并在一起。这一步骤是后续行列转换的基础...

    通过SQL语句实现行列转换的几种方法

    ### 通过SQL语句实现行列转换的几种方法 在日常工作中,我们经常需要处理的数据结构并不总是按照我们期望的方式组织的。特别是在制作自定义报表或进行产品开发时,经常会遇到需要将数据从一种布局转换到另一种布局...

    SQL语句行列转换(附带数据库、表、视图操作)

    SQL 语句行列转换(附带数据库、表、视图操作) SQL 语句行列转换是数据库管理系统中的一种常见操作,它可以将数据从行转换为列,或者从列转换为行。在这个过程中,需要使用数据库管理语言(Database Management ...

    SQL 2008行列转换的pivot

    ### SQL 2008 行列转换 (Pivot) 的动态实现 #### 知识点一:行列转换(Pivot)的概念与应用场景 在数据库查询中,有时我们需要将数据表中的行转换为列,或者将列转换为行,这种操作被称为行列转换。行列转换在报表...

    Sql语句实现表的行列转换,行转列,列转行

    ### SQL语句实现表的行列转换,行转列,列转行 在处理数据库时,我们经常需要对数据进行各种变换以适应不同的分析需求。其中,“行列转换”就是一种非常实用的功能,它可以帮助我们将表中的行数据转换为列数据,...

    SQL SERVER 2005中文版经典案例设计与实现_浩瀚图书\

    《SQL SERVER 2005中文版经典案例设计与实现》是针对数据库管理和开发人员的一本实用指南,它集合了SQL Server 2005的众多实战案例,旨在帮助读者深入理解和熟练应用这一强大的数据库系统。这本书以浩瀚图书的名义...

    行列转换.sql(包括行列转换实例)

    行列转换实例 表ttt有三个字段 seq --序列 jcxm --检查项目 zhi --值 数据分别如下: seq jcxm zhi ------- -------- -------- 11 1 0.50 11 2 0.21 11 3 0.25 12 1 0.24 12 2...

    SQL 数据行列转换

    ### SQL 数据行列转换 #### 知识点概览 1. **SQL Server中的行列转换**:介绍如何在SQL Server环境中利用内置函数实现数据从行到列的转换。 2. **FOR XML PATH() 函数的应用**:详细解释FOR XML PATH()函数在行列...

    行列转换SQL存储过程代码

    ### 行列转换SQL存储过程代码解析 #### 核心知识点概述 本篇文章将深入探讨一个SQL存储过程的实现方式,该存储过程主要用于完成“行转列”(即行列转换)的操作。通过这种方式,可以有效地将数据库表中的行数据...

    SQL语句普通行列转换.docx

    ### SQL语句普通行列转换详解 #### 一、背景与目的 本文档旨在介绍如何使用SQL Server中的行列转换功能来解决常见的数据展示需求。通过具体的示例,将原始的宽表(每一行代表一个学生的所有科目成绩)转换为长表...

    SQL2000 和 SQL2005 下 行列转换 示例 - freeliver54 - 博客园.pdf

    ### SQL2000 和 SQL2005 下行转列转换示例解析 #### 一、背景介绍 本文档提供了在SQL Server 2000和SQL Server 2005两个版本中进行行转列操作的具体示例。行转列是一种常见的数据处理方式,用于将表格中的行数据...

    SQL行列转换

    在实际的数据分析和报表制作过程中,我们经常需要将数据的列与行进行互换,这就是所谓的“SQL行列转换”。这种操作在处理汇总数据、创建透视表或者进行特定统计分析时尤其常见。以下我们将详细探讨SQL中的行列转换...

    [数据库] SQL查询语句表行列转换及一行数据转换成两列1

    在SQL查询中,表的行列转换以及一行数据转换成两列是常见的操作,尤其是在数据分析和报表展示时。本文将详细讲解这些概念,并提供相应的SQL语句示例。 首先,我们从创建数据库表及插入数据开始。在例子中,创建了一...

    SQLServer数据库开发经典案例完整版课件.ppt

    SQLServer数据库开发经典案例完整版课件.ppt

    ms sql 行列转换

    这个案例主要涉及了如何将数据从行格式转换为列格式,针对的是一个学生成绩表,其中包含姓名、科目和成绩三个字段。我们将分别介绍静态SQL和动态SQL两种方法来实现这个转换。 首先,静态SQL是针对已知列名的情况。...

    SQL行列转换 Pivot UnPivot

    SQL 行列转换 Pivot UnPivot SQL 行列转换是指将表格中的行数据转换为列数据,或者将列数据转换为行数据。Pivot 和 UnPivot 是 SQL Server 2005 中引入的两个语法,用于实现行列转换。 Pivot Pivot 语法的主要...

    Oracle中SQL语句行列之间相互转换

    ### Oracle中SQL语句行列之间的相互转换 在Oracle数据库中,有时我们需要将表格中的行转换为列或将列转换为行,这种操作被称为行列转换。这种转换对于数据分析、报表制作等场景非常有用。本文将详细介绍Oracle中...

    SQL Server数据库开发经典案例解析.part1

    SQL Server数据库开发经典案例解析.part1

    SQL脚本文件合并工具

    通过“SQL脚本文件合并工具”,我们可以将分散的SQL脚本整合到一起,形成一个大的SQL脚本文件,这样在SQL*Plus中只需要运行一次,就能完成所有脚本的执行,避免了反复打开、执行单个文件的繁琐步骤。 合并过程可能...

Global site tag (gtag.js) - Google Analytics