sql - SQL 行值到一列

Question

我有一个返回以下数据的 SQL 查询；

我需要将值写入一行依赖ID和Code列，如下所示；

标签： sqlsql-server

Answer 1

请检查以下尝试。您还可以使用其他选项。

 SELECT
    CAST(408 AS INT) AS ID,
    CAST(1 AS INT) AS CODE,
    CAST('A' AS VARCHAR(20)) AS VALUE
    INTO 
    #tmpgroupby
    
    INSERT INTO #tmpgroupby
    VALUES 
    (408,1,'B'),
    (408,1,'C'),
    (408,1,'D'),
    (408,1,'E'),
    (408,1,'F'),
    (408,1,'G'),
    (408,2,'H'),
    (408,2,'I'),
    (408,2,'J'),
    (408,2,'K')
    
    SELECT ID,CODE, STUFF(
             (SELECT ', ' + convert(varchar(10), t2.VALUE, 120)
              FROM #tmpgroupby t2
              where t1.ID = t2.ID
              AND t1.code = t2.CODE
              FOR XML PATH (''))
              , 1, 1, '') 
               FROM #tmpgroupby t1
    GROUP BY ID,CODE

--- below also give the same result
  
SELECT ID, Code, STRING_AGG(VALUE,',') AS Value
FROM dbo.#tmpgroupby
GROUP BY ID, Code;

    DROP TABLE #tmpgroupby