如何在sql server 2008中连接未知行数？ [英] How do I concatenate unknown number of rows in sql server 2008?

问题描述

我想在sql server 2008中创建连接未知列数的函数



Ex：

如果输入是：

I want to create function to concatenate unknown number of columns in sql server 2008

Ex:
if input is:

id        Name        family
1         a           b

输出：

output:

id=1,name=a,family=b

如果输入是：

and if input is:

id        postalCode        address      telephone
1         111               ddd         12

输出：

output:

id=1,postalCode=111,address=ddd,telephone=12

我该怎么做?????

how Can I do it?????

推荐答案

试试这个：

Try this:

CREATE TABLE [dbo].[UserInfo](
    [Id] INT IDENTITY (1,1),
    [Name] [nvarchar](256) NOT NULL,
    [Family] [nvarchar](256) NOT NULL,
    [PostalCode] [nvarchar](256) NOT NULL,
    [Address] [nvarchar](256) NOT NULL,
    [Telephone] [nvarchar](256) NOT NULL,
)


INSERT [dbo].[UserInfo]
SELECT  'Kuthuparakkal', 'KU', '30084', 'New York', '078-211-0000'
UNION
SELECT  'Uthuparakkal', 'UT', '30074', 'Old York', '068-211-0000'
UNION
SELECT 'Thuparakkal', 'TH', '30064', 'Bad York', '058-211-0000'
UNION
SELECT  'Huparakkal', 'HU', '30054', 'Better York', '048-211-0000'
UNION
SELECT  'Uparakkal', 'UP', '30044', 'Best York', '038-211-0000'
UNION
SELECT 'Parakkal', 'PA', '30034', 'Worst York', '028-211-0000'



GO


DECLARE @Query NVARCHAR(MAX)
SELECT @Query = COALESCE(@Query + '+ ', '') +
    ISNULL(Name, 'N/A')
FROM
(
SELECT ''',' + name + '=''+' + ' CAST(ISNULL([' + name + '], ''<Empty>'') AS NVARCHAR(300)) '  AS Name
FROM sys.columns WHERE object_id = OBJECT_ID('[dbo].[UserInfo]')
)AS X


SET @Query = 'SELECT ''' + SUBSTRING(@Query,3, LEN(@Query)) + 'FROM [dbo].[UserInfo] WHERE [Id] = '

PRINT @Query

DECLARE @Rows INT = (SELECT COUNT(1) FROM [dbo].[UserInfo])

DECLARE @QueryResults AS TABLE
(
Result NVARCHAR(MAX)
)

DECLARE @TempQuery NVARCHAR(MAX)

WHILE(@Rows > 0)
BEGIN

    SET @TempQuery = @Query + ''''+ CAST(@Rows AS NVARCHAR(300)) + ''''
    INSERT @QueryResults
    EXEC (@TempQuery)
SET @Rows = @Rows -1
END


SELECT * FROM @QueryResults

谢谢，< br $>


Kuthuparakkal

Thanks,

Kuthuparakkal

如果您使用的是SQL Server 2012或更高版本，则可以使用内置的 CONCAT [ ^ ]因为它接受不同数量的参数。



编辑：



如果您使用的是旧版本，那么为什么不使用连接操作符+



例如

If you're using SQL Server 2012 or above you can use the built-in CONCAT[^] since it accepts varying amount of parameters.



If you're using older versions then why not use concatenation operator +

For example

SELECT 'id=' + id + ', Name=' + Name + ', family=' + family
...

这篇关于如何在sql server 2008中连接未知行数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！