行转列输出字符串

[qian386620]

数据脚本如下
USE [BioErpDb]
GO
/****** Object: Table [dbo].[A] Script Date: 2017-12-14 9:26:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[XH] [nvarchar](50) NOT NULL,
[FeeName] [nvarchar](50) NULL,
[Amount] [int] NULL,
CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[A] ON

GO
INSERT [dbo].[A] ([ID], [XH], [FeeName], [Amount]) VALUES (1, N'SH160901055
', N'运费', 1920)
GO
INSERT [dbo].[A] ([ID], [XH], [FeeName], [Amount]) VALUES (2, N'SH160901055
', N'送货费', 200)
GO
INSERT [dbo].[A] ([ID], [XH], [FeeName], [Amount]) VALUES (3, N'SH160901055
', N'其它费', 500)
GO
SET IDENTITY_INSERT [dbo].[A] OFF
GO

[qian386620]

求个公用的函数。求完整的SQL代码。谢谢

[二月十六]

 SELECT  XH ,
            STUFF(( SELECT  ',' + FeeName+RTRIM(Amount)
                    FROM    A
                    WHERE   XH = a.XH
                  FOR
                    XML PATH('')
                  ), 1, 1, '') AS FeeName
    FROM    A a
    GROUP BY a.XH 

[吉普赛的歌]

测试数据：

USE tempdb
GO
IF OBJECT_ID('a') IS NOT NULL DROP TABLE a
go
CREATE TABLE [dbo].[A](
[ID] [int] IDENTITY(1,1) NOT NULL,
[XH] [nvarchar](50) NOT NULL,
[FeeName] [nvarchar](50) NULL,
[Amount] [int] NULL,
 CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[A] ON 
GO
INSERT [dbo].[A] ([ID], [XH], [FeeName], [Amount]) VALUES (1, N'SH160901055
', N'运费', 1920)
GO
INSERT [dbo].[A] ([ID], [XH], [FeeName], [Amount]) VALUES (2, N'SH160901055
', N'送货费', 200)
GO
INSERT [dbo].[A] ([ID], [XH], [FeeName], [Amount]) VALUES (3, N'SH160901055
', N'其它费', 500)
GO
INSERT [dbo].[A] ([ID], [XH], [FeeName], [Amount]) VALUES (4, N'SH160901056
', N'运费', 1920)
GO
INSERT [dbo].[A] ([ID], [XH], [FeeName], [Amount]) VALUES (5, N'SH160901056
', N'送货费', 200)
GO
INSERT [dbo].[A] ([ID], [XH], [FeeName], [Amount]) VALUES (6, N'SH160901056
', N'其它费', 500)

SET IDENTITY_INSERT [dbo].[A] OFF
GO

#2 版主的有点小问题， 修正了一下：

--#2 版主的
SELECT  XH ,
            STUFF(( SELECT  ',' + FeeName+RTRIM(Amount)
                    FROM    A
                    WHERE   XH = a.XH
                  FOR
                    XML PATH('')
                  ), 1, 1, '') AS FeeName
    FROM    A a
    GROUP BY a.XH 
/*
XH	         FeeName
SH160901055  运费1920,送货费200,其它费500,运费1920,送货费200,其它费500
SH160901056  运费1920,送货费200,其它费500,运费1920,送货费200,其它费500
*/

--修正后的：
SELECT  XH ,
        STUFF(( SELECT  ',' + b.FeeName+RTRIM(b.Amount)
                FROM    A AS b
                WHERE   b.XH = a.XH
                FOR
                XML PATH('')
                ), 1, 1, '') AS FeeName
FROM    A a
GROUP BY a.XH
/*
XH	            FeeName
SH160901055     运费1920,送货费200,其它费500
SH160901056     运费1920,送货费200,其它费500
*/

[二月十六]

引用 3 楼 yenange 的回复:

确实有点问题

[zjcxc]

2017 有聚合函数支持了

SELECT [XH], STRING_AGG(CONCAT([FeeName], [Amount]), ', ')
FROM a GROUP BY [XH]

[OwenZeng_DBA]

引用 5 楼 zjcxc 的回复:

2017 有聚合函数支持了

SELECT [XH], STRING_AGG(CONCAT([FeeName], [Amount]), ', ')
FROM a GROUP BY [XH]

你们都用2017了？

[Ginnnnnnnn]

引用 4 楼 sinat_28984567 的回复:

[quote=引用 3 楼 yenange 的回复:]

确实有点问题[/quote]

SELECT  XH ,
        STUFF(( SELECT  ',' + b.FeeName+RTRIM(b.Amount)
                FROM    A 
                WHERE  XH = c.XH
                FOR
                XML PATH('')
                ), 1, 1, '') AS FeeName
FROM    A c
GROUP BY a.XH

版主这种表名和别名都命同一个名的描述有点问题啊~