请帮助SQL问题 - 单引号数据然后逗号 [英] PLEASE HELP SQL Problems - Data with Single Quote then Comma
问题描述
请帮我解决我的SQL问题
我有一个数据:
否Str
1 A
1 A
2 B
2 B
我想像这样输出它(因为我将获得No 1的数据):
'A','A'
我希望它将输出存储到变量中。我的问题是我不知道如何将每个数据放在单引号中然后用逗号表示。
这是我的进步:
Please help me with my SQL Problem
I had a data:
No Str
1 A
1 A
2 B
2 B
I want to output it like this (Since I'll be getting the data with No 1):
'A','A'
I want it to store the output into a variable. My problem is I don't know how to
put every data in single quote then comma.
This is my progress:
DECLARE @TMP VARCHAR(100)
SET @TMP = (SELECT DISTINCT ''''+
STUFF((SELECT '''' + ''+ [Str] + ''''
FROM TableN
WHERE [No] = 1
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM TableN)
PRINT @TMP
以上查询的输出:
'A''A'
(虽然我能用单引号输出数据,但我对它不太确定。)
请帮我说说在上面的输出上加一个逗号。
我想要的输出是'A','A'
请帮助。
非常感谢你。
The output of the above query:
'A''A'
(Though I was able to output data with single quote but I'm not so sure with it.)
Please help me on how to put a comma on the above output.
My desired output is 'A','A'
Please help.
Thank you so much.
推荐答案
试试这个代码
Try this code
DECLARE @TMP VARCHAR(100)
SET @TMP = (SELECT DISTINCT ''''+
STUFF((SELECT '''' + ''+ [Str] + ''+''','+''
FROM @t
WHERE [No] = 1
FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '')
FROM @t)
print SUBSTRING(@tmp,1,LEN(@tmp)-1)
查看此链接
http://blog.sqlauthority.com/2009/11/25/sql-server-comma-separated-values-csv-from-table-列/ [ ^ ]
这篇关于请帮助SQL问题 - 单引号数据然后逗号的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!