时间戳

首页 > 解决方案 > SQL - 一行中有多个记录

sql - SQL - 一行中有多个记录

问题描述

我正在尝试创建一个脚本，该脚本允许我在一行中显示多个记录，按特定列分组。下面是我处理的代码。我已经在 Power BI 中完成了它，但现在我需要它在 SQL 上有人可以帮助我如何解决这个问题，我很感激：

我的代码：

   B.WorkOrderCode,
   STUFF((SELECT '; ' + A.UserName 
          FROM [PanatrackerGP].[dbo].[User] AS A
          WHERE A.ProfileOid = B.ProfileOid
          FOR XML PATH('')), 1, 1, '') [USERS]
FROM [PanatrackerGP].[dbo].[TrxIssueInventory] AS B
WHERE B.WorkOrderCode = 'S12119' 
GROUP BY B.ProfileOid, B.WorkOrderCode
ORDER BY 1

------------------------------  OUTPUT  ----------------------------------------

WorkOrderCode  |      USERS

S12119         |   GM; FM; FO; GR; RG; TI
S12119         |   NC; BS; DNA; CS; JMAGGI; mj; fa; LR; lgm; MS; JPU
-----------------------------------------------------------------------

Only these users should be shown 

------------------------------TrxIssueInventory[TABLE]-----------------
WorkOrderCode |  CreateUserName

S12119        |      FO
S12119        |      lgm
-----------------------------------------------------------------------

-----------------------------GOAL OUTPUT---------------------------------
WorkOrderCode |  CreateUserName

S12119        |      FO ; lgm          
-----------------------------------------------------------------------

Thank You

标签： sqltsqlgroup-byconcatenation

解决方案

您必须在相关子查询中进行额外的连接：

SELECT  b.WorkOrderCode, 
        CreateUserName = STUFF((SELECT  CONCAT(';', u.UserName)
                                FROM    [dbo].[User] AS u
                                        INNER JOIN [dbo].[TrxIssueInventory] AS i
                                            ON i.ProfileOid = u.ProfileOid
                                WHERE   i.WorkOrderCode = b.WorkOrderCode
                                FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(1000)'), 1, 1, '')
FROM    [dbo].[TrxIssueInventory] AS b
GROUP BY b.WorkOrderCode;

我还稍微修改了你的连接方法，FOR XML PATH(), TYPE而不是仅仅用于 XML 路径，因为它可以正确处理特殊的 XML 字符。如果你有一个用户名，如果你不使用和'.value' ，LGM >这最终会是 。LGM >TYPE

工作演示

IF OBJECT_ID(N'tempdb..#TrxIssueInventory', 'U') IS NOT NULL
    DROP TABLE #TrxIssueInventory;

IF OBJECT_ID(N'tempdb..#User', 'U') IS NOT NULL
    DROP TABLE #User;

CREATE TABLE #TrxIssueInventory (WorkOrderCode INT, ProfileOid INT);
INSERT  #TrxIssueInventory VALUES (12119, 1), (12119, 2);

CREATE TABLE #User (ProfileOid INT, UserName VARCHAR(10));
INSERT #User VALUES (1, 'FO'), (2, 'LGM >');

SELECT  b.WorkOrderCode, 
        CreateUserName = STUFF((SELECT  CONCAT(';', u.UserName)
                                FROM    #User AS u
                                        INNER JOIN #TrxIssueInventory AS i
                                            ON i.ProfileOid = u.ProfileOid
                                WHERE   i.WorkOrderCode = b.WorkOrderCode
                                FOR XML PATH(''), TYPE
                            ).value('.', 'NVARCHAR(1000)'), 1, 1, '')
FROM    #TrxIssueInventory AS B
GROUP BY b.WorkOrderCode;

-- DON'T USE, JUST HERE TO DEMONSTRATE THE ISSUE OF NOT USING 'TYPE'
SELECT  b.WorkOrderCode, 
        CreateUserName = STUFF((SELECT  CONCAT(';', u.UserName)
                                FROM    #User AS u
                                        INNER JOIN #TrxIssueInventory AS i
                                            ON i.ProfileOid = u.ProfileOid
                                WHERE   i.WorkOrderCode = b.WorkOrderCode
                                FOR XML PATH('')
                            ), 1, 1, '')
FROM    #TrxIssueInventory AS B
GROUP BY b.WorkOrderCode

推荐阅读