时间戳

首页 > 解决方案 > 多行成一行多列

sql - 多行成一行多列

问题描述

我们需要将所有数字列为平面数据集，我们该怎么做？

表名称：电话

ID      TYPE      NUMBER
==================================
123      MN       042153939
123      HN       2242116
123      MN       1234567890
123      HN       12345678

Create Table Telephone
(
  ID Integer,
  Type char(3),
  Number Varchar(20)
);

insert into Telephone values 
(123, 'MN', '042153939'),
(123, 'HN', '2242116'),
(123, 'MN', '1234567890'),
(123, 'HN', '12345678');

我希望 SQL 以这种格式返回数据

ID    MN#1       Mn#2          HN#1     HN#2
================================================
123   042153939  1234567890   2242116   12345678

标签： sqlsql-servertsqldb2transpose

解决方案

动态方法

在里面

DROP TABLE IF EXISTS #Telephone;
CREATE TABLE #Telephone(ID INT,Type CHAR(3),Number VARCHAR(20));
INSERT INTO #Telephone (ID,Type,Number) VALUES 
(123, 'MN', '042153939'),
(123, 'HN', '2242116'),
(123, 'MN', '1234567890'),
(123, 'HN', '12345678');

编码

DECLARE @ColumnList NVARCHAR(MAX);
SELECT @ColumnList = STUFF((SELECT ',[' + RTRIM(t.[Type]) + '#' 
                + CONVERT(NVARCHAR(255),ROW_NUMBER()OVER(PARTITION BY t.[Type] ORDER BY t.ID)) + ']'
                FROM #Telephone t FOR XML PATH(''),TYPE).value('(./text())[1]','NVARCHAR(MAX)'),1,1,'')
;
DECLARE @sql NVARCHAR(MAX) = '';
SET @sql = N'
SELECT ID,' + @ColumnList + N'
FROM (
    SELECT t.ID,t.Number, RTRIM(t.[Type]) + ''#'' + CONVERT(NVARCHAR(255),ROW_NUMBER()OVER(PARTITION BY t.[Type] ORDER BY t.ID)) AS [Type]
    FROM #Telephone t
) a
PIVOT(MAX(a.Number) FOR a.Type IN (' + @ColumnList + N')) p
'
;
--PRINT @sql
IF @sql IS NOT NULL EXEC(@sql);

推荐阅读