合并同一行内不同列的相同数据

来源：互联网发布：数据库入门教程编辑：程序博客网时间：2024/05/01 19:23

有一个表
a b c a b b null null
d b c y null c a a
y r v a null c null null
合并同一行里的重复数据,合并完后的数据为
a b c null null
d b c y a
y r v a c
去除重复数据,但是首次出现的顺序不变

----------------------------------

/*---------------------------------
-- Author : htl258(Tony)
-- Date   : 2009-09-11 10:36:58
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
    Mar 29 2009 10:27:29
    Copyright (c) 1988-2008 Microsoft Corporation
    Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)

---------------------------------*/
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([Col1] nvarchar(1),[Col2] nvarchar(1),[Col3] nvarchar(1),[Col4] nvarchar(1),[Col5] nvarchar(1),[Col6] nvarchar(1),[Col7] nvarchar(1),[Col8] nvarchar(1))
INSERT [tb]
SELECT N'a',N'b',N'c',N'a',N'b',N'b',null,null UNION ALL
SELECT N'd',N'b',N'c',N'y',null,N'c',N'a',N'a' UNION ALL
SELECT N'y',N'r',N'v',N'a',null,N'c',null,null
GO
--SELECT * FROM [tb]

-->SQL查询如下:
DECLARE @s VARCHAR(MAX),@s1 VARCHAR(MAX)
SELECT @s=ISNULL(@s+',','')+NAME FROM syscolumns WHERE OBJECT_ID('tb')=id
EXEC('
WITH T AS
(
SELECT *
FROM (SELECT RN=ROW_NUMBER()OVER(ORDER BY GETDATE()),* FROM TB) A
    UNPIVOT(A FOR B IN('+@S+'))B
)
SELECT A,RN,C=''COL''+LTRIM(ROW_NUMBER()OVER(PARTITION BY RN ORDER BY B))
INTO ##
FROM T A
WHERE NOT EXISTS(
    SELECT 1
    FROM T
    WHERE RN=A.RN AND A=A.A AND B<A.B)
')
SELECT @s1=ISNULL(@s1+',','')+C FROM ## GROUP BY C
EXEC('
SELECT '+@S1+'
FROM ##
    PIVOT(MAX(a) FOR C IN('+@S1+'))B;
DROP TABLE ##;')

/*
COL1 COL2 COL3 COL4 COL5
---- ---- ---- ---- ----
a    b    c    NULL NULL
d    b    c    y    a
y    r    v    a    c

(3 行受影响)
*/