SQL Server：将数据从多行移动到一行，再移动到新列并分配列名

我的表如下所示，行是基于date & dep中的值动态变化的
enter image description here

ID  Date       Dep_Name  Event_ID
---------------------------------
A1  2022-01-01  DepX    1
A1  2022-02-02  DepY    2
B1  2022-01-01  DepA    3
B1  2022-03-03  DepX    4
B1  2022-03-04  DepY    5

我希望上表中的数据显示如下
enter image description here

ID  Date1       Dep1    Date2       Dep2    Date3       Dep3
------------------------------------------------------------
A1  2022-01-01  DepX    2022-02-02  DepY    NULL        NULL
B1  2022-01-01  DepA    2022-03-03  DepX    2022-03-04  DepY

我试过这个代码-但我需要进一步的帮助。

BEGIN TRY 
    DROP TABLE ##dep 
END TRY 
BEGIN CATCH 
END CATCH;

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SET @cols =  STUFF((SELECT DISTINCT ',' + QUOTENAME('Dep_Name' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY EVENT_ID) AS varchar(5))) 
                    FROM #firstpool c
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = 'SELECT ID, ' + @cols + ' INTO ##dep 
              FROM
                  (SELECT 
                       ID, Dep_Name,
                       ''Dep'' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY EVENT_ID) AS varchar(5)) col
                   FROM #firstpool) x
              PIVOT
                  (MAX(Dep_Name)
                       FOR col IN (' + @cols + ')
                  ) p '

EXECUTE(@query)

SELECT * FROM ##dep

这里有一个方法可以完全动态地完成，就像您尝试做的那样。我不确定这是否表现为PIVOT类型的问题。使用PIVOT意味着在透视表之后的列名在您的数据中;我不认为您可以像以前那样分配任意的列名，这个解决方案仍然使用动态SQL，但是它创建了一个多连接SELECT语句来完成同样的事情。

CREATE TABLE data (
  ID nvarchar(10) not null
  , [Date] datetime not null
  , Dep_name nvarchar(50) not null
  , Event_ID int not null
);

INSERT INTO data (ID, [Date], Dep_name, Event_ID)
VALUES 
  ('A1','2022-01-01','DepX',1)
  , ('A1','2022-02-02','DepY',2)
  , ('B1','2022-01-01','DepA',3)
  , ('B1','2022-03-03','DepX',4)
  , ('B1','2022-03-04','DepY',5)
;

--Get the maximum number of events so we know how many columns to create.
DECLARE @maxEvents int;
SET @maxEvents = (
  SELECT MAX(d.row_num)
  FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Event_ID) as row_num
    FROM data
  ) as d );

--Create a variable to hold the dynamic query.
DECLARE @sqlText nvarchar(max);

--Create the first part of the SELECT statment with place holders
--where we can insert columns ({{0}}) and JOINS ({{1}})
SET @sqlText = '
  WITH prelim as (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Event_ID) as row_num
    FROM data
  )
  SELECT DISTINCT d.ID
    {{0}}
  FROM prelim as d
    {{1}}
  ';

--Setup loop variables.
DECLARE @loopCount int = 1;
DECLARE @loopCText nvarchar(50);

WHILE (@loopCount <= @maxEvents)
BEGIN
  --Get a string for the loop count... pad it with zeros if multi-digits.
  SET @loopCText = RIGHT(
    '0000000000' + CAST(@loopCount as nvarchar(10))
    , LEN(CAST(@maxEvents as nvarchar(50)))
  );

  --Add a SELECT entry for this loop instance.
  SET @sqlText = REPLACE(@sqlText, '{{0}}', '
    , d' + @loopCText + '.Dep_Name as Dep_Name' + @loopCText + '
    , d' + @loopCText + '.[Date] as Date' + @loopCText + '
    {{0}}
  ');

  --Add a JOIN entry for this loop instance.
  SET @sqlText = REPLACE(@sqlText, '{{1}}', '
    LEFT OUTER JOIN prelim as d' + @loopCText + '
      ON d' + @loopCText + '.ID = d.ID
      AND d' + @loopCText + '.row_num = ' + CAST(@loopCount as nvarchar(10)) + '
    {{1}}
  ');

  --SELECT @sqlText;  --For debugging
  SET @loopCount = @loopCount + 1;
END

--Post Loop Cleanup... remove template place holders.
SET @sqlText = REPLACE(@sqlText, '{{0}}', '');
SET @sqlText = REPLACE(@sqlText, '{{1}}', '');

EXEC(@sqlText);

| 识别号|部门名称1|日期1|部门名称2|日期2|部门名称3|日期3|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|
| A1|深度X| 2022年1月1日00时00分00秒|部门|2022年2月2日00时00分00秒| * 无效 | 无效 *|
| 地下一层|部门|2022年1月1日00时00分00秒|深度X| 2022年3月3日00时00分00秒|部门|2022年3月4日00时00分00秒|
fiddle

SQL Server：将数据从多行移动到一行，再移动到新列并分配列名

1条答案

相关问题

热门标签

最新问答