多行数据合并为单行的SQL技巧与案例

-- 示例 1: 多行转单行及部分列转行
IF OBJECT_ID(N'[one]', N'U') IS NOT NULL DROP TABLE [one];
CREATE TABLE [one] (
    [month] INT,
    [day] INT,
    [money] INT
);
INSERT INTO [one]
VALUES (10, 1, 200), (10, 2, 300), (10, 3, 400),
       (11, 1, 500), (11, 2, 600), (11, 3, 700);

-- 使用动态SQL实现多行转单行
DECLARE @sql NVARCHAR(MAX) = N'SELECT [month]';
SELECT @sql += N', [' + CAST([day] AS NVARCHAR(10)) + N'] = SUM(CASE WHEN [day] = ' + CAST([day] AS NVARCHAR(10)) + N' THEN [money] ELSE 0 END)'
FROM (SELECT DISTINCT [day] FROM [one]) AS D;
SET @sql += N' FROM [one] GROUP BY [month];';
EXEC sp_executesql @sql;

-- 示例 2: 合并特定列的数据
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[two]') AND type IN (N'U'))
    DROP TABLE [two];
CREATE TABLE [two] (
    [id] INT IDENTITY(1, 1) NOT NULL,
    [UserName] VARCHAR(50),
    [Code] VARCHAR(50)
);

SET IDENTITY_INSERT [two] ON;
INSERT INTO [two] ([id], [UserName], [Code]) VALUES
(1, 'User1', 'A'), (1, 'User1', 'B'), (1, 'User2', 'C'),
(1, 'User2', 'D'), (1, 'User2', 'E'), (1, 'User3', 'F'),
(1, 'User3', 'G'), (1, 'User3', 'H'), (1, 'User3', 'I');
SET IDENTITY_INSERT [two] OFF;

-- 合并用户名对应的代码列表
SELECT B.UserName, LEFT(B.UserList, LEN(B.UserList) - 1) AS [List]
FROM (
    SELECT [UserName],
          (SELECT [Code] + ',' FROM [two] WHERE [UserName] = A.[UserName] ORDER BY [id] FOR XML PATH('')) AS UserList
    FROM [two] A
    GROUP BY [UserName]
) B;

-- 示例 3: 使用函数和CTE实现多行转单行
IF OBJECT_ID(N'[tb]', N'U') IS NOT NULL DROP TABLE [tb];
CREATE TABLE [tb] ([id] INT, [value] NVARCHAR(1));
INSERT INTO [tb] VALUES (1, N'a'), (1, N'b'), (1, N'c'), (2, N'd'), (2, N'e'), (3, N'f');

CREATE FUNCTION dbo.f_str(@id INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @result NVARCHAR(MAX) = '';
    SELECT @result += ',' + [value]
    FROM [tb]
    WHERE [id] = @id;
    RETURN STUFF(@result, 1, 1, '');
END;
GO

SELECT DISTINCT [id], [value] = dbo.f_str([id]) FROM [tb];

-- 使用CTE实现相同功能
WITH Roy AS (
    SELECT [id], [value], [row] = ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [id])
    FROM [tb]
), Roy2 AS (
    SELECT [id], CAST([value] AS NVARCHAR(100)) AS [value], [row]
    FROM Roy
    WHERE [row] = 1
    UNION ALL
    SELECT a.[id], CAST(b.[value] + ',' + a.[value] AS NVARCHAR(100)), a.[row]
    FROM Roy a
    JOIN Roy2 b ON a.[id] = b.[id] AND a.[row] = b.[row] + 1
)
SELECT [id], [value]
FROM Roy2 a
WHERE [row] = (SELECT MAX([row]) FROM Roy WHERE [id] = a.[id])
ORDER BY [id]
OPTION (MAXRECURSION 0);

-- 示例 4: 合并特定类型的数据
IF OBJECT_ID(N'[tb]', N'U') IS NOT NULL DROP TABLE [tb];
CREATE TABLE [tb] (
    [id] INT,
    [type] VARCHAR(12)
);
INSERT INTO [tb]
VALUES (1, 'aa'), (2, 'bb'), (3, 'aa'), (4, 'aa'), (5, 'ab'), (6, 'bb'), (7, 'c'), (8, 'ac');

CREATE FUNCTION dbo.Fn_GetType_STR(@typeid VARCHAR(32))
RETURNS VARCHAR(1000)
AS
BEGIN
    DECLARE @s VARCHAR(1000) = '';
    SELECT @s = ISNULL(@s + ',', '') + CAST([id] AS VARCHAR)
    FROM [tb]
    WHERE [type] = @typeid;
    RETURN @s;
END;

SELECT DISTINCT [type], [str] = dbo.Fn_GetType_STR([type]) FROM [tb];

-- 示例 5: 最通用的多行转单行方法
DECLARE @tb TABLE (
    [id] INT,
    [value] VARCHAR(10)
);
INSERT INTO @tb
VALUES (1, 'aa'), (1, 'bb'), (2, 'aaa'), (2, 'bbb'), (2, 'ccc');

SELECT [id], [value] = STUFF((SELECT ',' + [value] FROM @tb t WHERE t.[id] = tv.[id] FOR XML PATH('')), 1, 1, '')
FROM @tb AS tv
GROUP BY [id];

-- 示例 6: 使用动态SQL实现多行转单行
IF OBJECT_ID(N'[tb]', N'U') IS NOT NULL DROP TABLE [tb];
CREATE TABLE [tb] ([col] VARCHAR(20));
INSERT INTO [tb] VALUES ('a'), ('b'), ('c'), ('d'), ('e');

-- 方法一
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += [col] + ','
FROM (SELECT [col] FROM [tb]) AS t;
SET @sql = 'SELECT [result] = ''' + LEFT(@sql, LEN(@sql) - 1) + '''';
EXEC sp_executesql @sql;

-- 方法二
DECLARE @output NVARCHAR(MAX) = '';
SELECT @output = COALESCE(@output + ',', '') + [col]
FROM [tb];
PRINT @output;

-- 方法三
SELECT (SELECT LTRIM([col]) + ',' FROM [tb] FOR XML PATH('')) AS [a];

-- 处理包含重复值的情况
IF OBJECT_ID(N'[a]', N'U') IS NOT NULL DROP TABLE [a];
CREATE TABLE [a] ([name] VARCHAR(50), [num] INT);
INSERT INTO [a] VALUES ('aa', 1), ('bb', 2), ('cc', 4), ('dd', 4), ('ee', 2);

DECLARE @STR NVARCHAR(MAX) = '';
SELECT @STR = ISNULL(@STR + ',', '') + [name]
FROM (SELECT DISTINCT [name] FROM [a]) AS T;
SELECT @STR;