代码改变世界

SQL Server 中多行多列连接成为单行单列

2007-07-11 03:10  晓风残月  阅读(...)  评论(...编辑  收藏

原始结构:

Column1     Column2                                                                                              
----------- ----------
1           A
1           B
2           C
2           D
2           E
3           F

查询效果:

 

Column1     Column2                                                                 
----------- ------------------
1           A,B
2           C,D,E
3           F

即将 Column1 相同的行的 Column2 连成一列。
不知如何描述此种用法,是否具有像交叉表相关的 Cross-Table 和 Pivot  之类的约定成熟的专业称谓?
是否也可以称为另一种 Cross-Table ?
此需求应该是常见的,网上也有许多DEMO,只是 CSDN 中频繁有新手提问,现简单实现一个DEMO,以便参考。

-- 多行多列连接成为单行单列示例:需要一个自定义函数
--
 http://community.csdn.net/Expert/TopicView3.asp?id=5603231

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VertToHorzSample]'and OBJECTPROPERTY(id, N'IsUserTable'= 1)
drop table [dbo].[VertToHorzSample]
GO

-- 建立测试数据
CREATE TABLE VertToHorzSample(
    Column1 
int,
    Column2 
varchar(100)
)

GO

INSERT INTO VertToHorzSample(Column1, Column2)
SELECT 1'A'
UNION ALL
SELECT 1'B'
UNION ALL
SELECT 2'C'
UNION ALL
SELECT 2'D'
UNION ALL
SELECT 2'E'
UNION ALL
SELECT 3'F'

GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ConvertVertToHorz]'and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[ConvertVertToHorz]
GO

-- 建立辅助函数
CREATE FUNCTION ConvertVertToHorz(@Col1Val int)
RETURNS VARCHAR(8000)
AS
    
BEGIN 
        
-- 实际项目中,应该考虑 @RetVal 是否会超过 8000 个字符
        DECLARE @RetVal varchar(8000)
        
SET @RetVal = ''
        
-- 通过递归 SELECT 连接指定列存储到临时变量中
        SELECT @RetVal = Column2 + ',' + @RetVal FROM VertToHorzSample WHERE Column1 = @Col1Val
        
-- 连接多列
        -- SELECT @RetVal = Column2 + ',' + Column3 + ',' + Column4 + ',' + @RetVal FROM VertToHorzSample WHERE Column1 = @Col1Val
        
        
-- 去掉尾巴的 , (逗号)
        IF LEN(@RetVal> 0
          
SET @RetVal = LEFT(@RetValLEN(@RetVal- 1)    
        
--PRINT @RetVal
        
        
RETURN @RetVal
    
END

GO

-- 测试
SELECT Column1, dbo.ConvertVertToHorz(Column1) Column2 FROM (SELECT DISTINCT Column1 FROM VertToHorzSample) t
/*
Column1     Column2                                                                 
----------- ------------------
1           A,B
2           C,D,E
3           F
*/


GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VertToHorzView]'and OBJECTPROPERTY(id, N'IsView'= 1)
drop view [dbo].[VertToHorzView]
GO

-- 可以建立一个视图
CREATE VIEW dbo.VertToHorzView
AS
SELECT Column1, dbo.ConvertVertToHorz(Column1) Column2
FROM (SELECT DISTINCT Column1 FROM dbo.VertToHorzSample) t

GO

-- 测试视图
SELECT * FROM VertToHorzView
/*
Column1     Column2s                                                                                                                                                                                                                                                         
----------- -----------------
1           A,B
2           C,D,E
3           F
*/

下载