Rolling up multiple rows into a single row

Problem :

I have one table have 30,000 phone Number and i need to path it to SMS services as one row to can send massage for this numbers in one time so i need to return all this rows in one row ,

Solution :

we have two solution either you can return it as XML by using XML Path or using STUFF FUNCTION to return all this rows in one row , Stuff function can be used in more things and this case one of this usage.

STUFF FUNCTION:

The STUFF string function inserts a string into another string.  It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.  The syntax of the STUFF string function is as follows: for more information about STUFF function check this LINK

STUFF ( < character_expression >, < start >, < length >,

< character_expression > )

 DEMO PART

• Create table name PhoneNumber
• Insert one record.
• Filling table by 30.000 Record using Filling Table Stored procedure GO FOR Download (SP after the execute it will print T-SQL Script Execute it to filling the table)
• Return All record in one row as XML
• Return all record in one row using stuff function.

Create Table

Use MSDB
GO
Create table phone_number
(
ID int primary KEY ,
number Nvarchar(20)
)
GO
Insert one Record
insert into phone_number
values
(1,’0543990968′)
GO

Fill the table by 30,000 of record
Exec FillingTable_v2 ‘dbo’,’phone_number’,’30000′
Select Count(1) from [phone_number] With(nolock)
Go

Return Records in one Row as XML
SELECT
number
FROM [phone_number]
FOR XML PATH(”)

GO

Return Records in one Row without XML

SELECT STUFF((SELECT ‘,’ + [Number]
FROM [phone_number]
ORDER BY [Number]
FOR XML PATH(”)), 1, 1, ”) AS [Output]

Follow the author:

View all my tips , LinkedIn , Website , Slideshare  , ABOUT ME