如何在sql server 2008中连接未知行数? [英] How do I concatenate unknown number of rows in sql server 2008?
本文介绍了如何在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屋!
查看全文