我正在尝试使用XML路径方法获取逗号分隔字符串
表格:
CREATE TABLE [dbo].[tbl_BankList](
[ID] [int] IDENTITY(1,1) NOT NULL,
[INST_ID] [bigint] NULL,
[TITLE] [varchar](50) NULL,
[ADDRESS_LINE1] [varchar](50) NULL,
[CITY] [varchar](50) NULL,
[STATE_CODE] [varchar](50) NULL,
[ZIP] [varchar](50) NULL,
[LOCATION_ID] [varchar](50) NULL,
[ALOC] [varchar](50) NULL,
)
CREATE TABLE [dbo].[tbl_BankRTN](
[ID] [int] IDENTITY(1,1) NOT NULL,
[INST_ID] [bigint] NULL,
[RTN] [float] NULL,
)
查询:
SELECT bl.INST_ID,
STUFF((SELECT distinct ',' + BR.[RTN]
FROM tbl_BankRTN BR
WHERE bl.INST_ID = BR.INST_ID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'') RTN
FROM tbl_BankList bl;
错误-消息8114,级别16,状态5,行1转换数据类型时出错varchar浮动。
使用CAST
:
SELECT bl.INST_ID,
STUFF((SELECT distinct ',' + CAST(BR.[RTN] AS VARCHAR(1000))
FROM tbl_BankRTN BR
WHERE bl.INST_ID = BR.INST_ID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'') RTN
FROM tbl_BankList bl;
或者如果SQL Server 2012+
CONCAT
功能:
SELECT bl.INST_ID,
STUFF((SELECT distinct CONCAT(',', BR.[RTN])
FROM tbl_BankRTN BR
WHERE bl.INST_ID = BR.INST_ID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'') RTN
FROM tbl_BankList bl;
编辑:
为了避免科学格式的STR包装浮动:
返回从数字数据转换而来的字符数据。
STR(float_expression[,length[,decimal]])
SELECT bl.INST_ID,
STUFF((SELECT distinct ',' + STR(BR.[RTN], 10,3)
FROM tbl_BankRTN BR
WHERE bl.INST_ID = BR.INST_ID
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'') RTN
FROM tbl_BankList bl;