HP在SQL Server中使用合并功能?

本文介绍了HP在SQL Server中使用合并功能?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

如何使用合并函数生成逗号分隔的序列?
例如1,4,9,16,25,36 .....，400.

How do i use the coalesce function to generate a comma separated series?
For example, 1,4,9,16,25,36....., 400.

推荐答案

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Cast(empid as  varchar(10))
FROM table1
SELECT @listStr

这里的empid是我的整数列
而table1是我的桌子

对于您的情况，请尝试此

Here empid is my integer column
and table1 is my table

For your scenario, try this

declare @count as int=1
DECLARE @listStr VARCHAR(MAX)
while @count<=20
BEGIN
SELECT @listStr = COALESCE(@listStr+',' ,'') + Cast(SQUARE(@count) as varchar(10))
set @count=@count+1
END
select @listStr

输出:
1,4,9,16,25,36,49,64,81,100,121,144,169,196,225,256,289,324,361,400

output:
1,4,9,16,25,36,49,64,81,100,121,144,169,196,225,256,289,324,361,400

DECLARE @cols NVARCHAR(2000)

SET @cols = STUFF((SELECT DISTINCT ',' + T.[FieldName]
					FROM [DatabaseName].[dbo].[TableName] AS T
					ORDER BY ',' + T.[FieldName]
			FOR XML PATH('')),1,2,'') + ''

SELECT @cols AS [CommaSepratedData]

select substring(Result,0,Len(Result)) as List from
    (
        select 
		(  
			select convert(varchar,ColNm)+ ',' as [text()] from TblNm order by ColNm for xml path('')
		) as Result
    )
as a

祝您编码愉快！
:)

Happy Coding!
:)

这篇关于HP在SQL Server中使用合并功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！