SQL 把 行 转变成 列 示例

需求：

当 House_Explist_Loc 表中 ExpGuid 列相同时，把 LocCode 列合并成一列

1.表结构 如下图：

SQL文片段：

CREATE TABLE [dbo].[House_Explist_Loc](
	[ExpGuid] [uniqueidentifier] NOT NULL,
	[ExpQty] [int] NULL,
	[LocCode] [varchar](50) NULL,
	[Guid] [uniqueidentifier] NOT NULL,
	[UpdateTime] [datetime] NULL,
	[UpdateUser] [int] NULL,
	[CreateTime] [datetime] NOT NULL,
	[CreateUser] [int] NOT NULL,
	[Usd3] [varchar](100) NULL,
	[Usd1] [varchar](100) NULL,
	[Usd2] [varchar](100) NULL,
	[DataFrom] [nvarchar](50) NULL
)

INSERT INTO House_Explist_Loc ([Guid],ExpGuid,LocCode,CreateTime,CreateUser) VALUES(NEWID(),'147A5C5B-1F74-40F5-BCBA-F36250677A04','A1',GETDATE(),1)
INSERT INTO House_Explist_Loc ([Guid],ExpGuid,LocCode,CreateTime,CreateUser) VALUES(NEWID(),'147A5C5B-1F74-40F5-BCBA-F36250677A04','A2',GETDATE(),1)

2.表中数据示例：

SQL文片段：

SELECT ExpGuid,LocCode FROM House_Explist_Loc WHERE ExpGuid ='147A5C5B-1F74-40F5-BCBA-F36250677A04'

3. 行转换成列结果示例：

SQL文片段：

SELECT A.ExpGuid,
(
SELECT LocCode+'|' FROM House_Explist_Loc hl WHERE hl.ExpGuid=A.ExpGuid FOR XML PATH('')
) AS LocCode
FROM House_Explist_Loc A  WHERE ExpGuid = '147A5C5B-1F74-40F5-BCBA-F36250677A04'
GROUP BY A.ExpGuid