如何在sql中对两行进行marge并生成单行

本文关键字：marge 两行 单行 sql | 更新日期: 2023-09-27 18:24:31

我正在尝试为两行加边距，并制作单列。同一行的数据必须加边距，不同的数据必须附加到同一行，

示例：我把三张这样的表连在一起得到了结果

uid     name    diseaseid     intensity
1       xxxx    1              high
1       xxxx    2              low

现在我需要像这样的最终结果

uid    name      diseaseid1   intensity       diseaseid2        intensity
1      xxxx       1             high           2                  low

在Sql中有这样的选项吗？请帮我。

提前谢谢。

如何在sql中对两行进行marge并生成单行

试试这个

CREATE TABLE #Table1
    ([uid] int, [name] varchar(4), [diseaseid] int, [intensity] varchar(4))
;
INSERT INTO #Table1
    ([uid], [name], [diseaseid], [intensity])
VALUES
    (1, 'xxxx', 1, 'high'),
    (1, 'xxxx', 2, 'low')
;
SELECT MAX([uid]) AS [uid]
       ,MAX([name]) AS [name]
       ,MAX([diseaseid1]) AS [diseaseid1]
       ,MAX([intensity1]) AS [intensity1]
       ,MAX([diseaseid2]) AS [diseaseid2]
       ,MAX([intensity2]) [intensity2]
FROM 
(
    SELECT [uid], [name]
    , CASE WHEN rn=2 THEN NULL ELSE [diseaseid] END AS [diseaseid1]
    , CASE WHEN rn=2 THEN NULL ELSE [intensity] END AS [intensity1]
    , CASE WHEN rn=1 THEN NULL ELSE [diseaseid] END AS [diseaseid2]
    , CASE WHEN rn=1 THEN NULL ELSE [intensity] END AS [intensity2]
    FROM
    (
        SELECT [uid], [name], [diseaseid], [intensity], 
        ROW_NUMBER() OVER(PARTITION BY [uid] ORDER BY Name) AS rn
        FROM #Table1
    ) T
) T
GROUP BY [uid], [name]
DROP TABLE #Table1

EIDT

请对动态列尝试此操作

CREATE TABLE #Table1
    ([uid] int, [name] varchar(10), [diseaseid] int, [intensity] varchar(10))

INSERT INTO #Table1
    ([uid], [name], [diseaseid], [intensity])
VALUES
    (1, 'xxxx', 1, 'high'),
    (1, 'xxxx', 2, 'low'),
    (1, 'xxxx', 3, 'medium')

DECLARE @MaxRows INT
DECLARE @CurrentRow INT
DECLARE @Query VARCHAR(MAX)
SET @CurrentRow = 1
SELECT @MaxRows = MAX(cnt) FROM (
SELECT COUNT(name) cnt FROM #Table1 GROUP BY [uid]
) AS T

SET @Query = '
SELECT  MAX([uid]) AS [uid]
       ,MAX([name]) AS [name]'
WHILE @CurrentRow <= @MaxRows
BEGIN
     SET @Query= @Query +  '
                ,MAX(diseaseid'+CONVERT(VARCHAR(10),@CurrentRow)+') AS diseaseid'+CONVERT(VARCHAR(10),@CurrentRow)+''
     SET @Query= @Query +  '
                ,MAX(intensity'+CONVERT(VARCHAR(10),@CurrentRow)+') AS intensity'+CONVERT(VARCHAR(10),@CurrentRow)+''
    SET @CurrentRow = @CurrentRow + 1
END
SET @Query= @Query + ' FROM 
  (
    SELECT [uid], [name]'
SET @CurrentRow = 1
WHILE @CurrentRow <= @MaxRows
BEGIN
     SET @Query= @Query +  '
                , CASE WHEN rn='+CONVERT(VARCHAR(10),@CurrentRow)+' THEN [diseaseid] ELSE NULL END AS diseaseid'+CONVERT(VARCHAR(10),@CurrentRow)+''
     SET @Query= @Query +  '
                , CASE WHEN rn='+CONVERT(VARCHAR(10),@CurrentRow)+' THEN [intensity] ELSE  NULL END AS intensity'+CONVERT(VARCHAR(10),@CurrentRow)+''
     SET @CurrentRow = @CurrentRow + 1
END
SET @Query= @Query + ' FROM 
    (
        SELECT [uid], [name], [diseaseid], [intensity], 
        ROW_NUMBER() OVER(PARTITION BY [uid] ORDER BY Name) AS rn
        FROM #Table1
    ) T2
) T3
GROUP BY [uid], [name]'
PRINT (@Query)
EXEC (@Query)
DROP TABLE #Table1

您可以使用类似以下的查询

select *,(select col1,col2 from table2 where uid=t.uid) from table1 t

我认为你的联接做得不对。。。看起来你在做一个联合，你只按UID和名称分组。在不知道联接的情况下，我能给你的最好建议是尝试使用左右外联接。

我认为这应该是你想要的。

试着加入你从疾病中得到的表两次，一次用join，第二次用右外join，这样你就可以毫无问题地得到疾病。

相关文章：