RSS

Rolling up multiple rows into a single row

07 Mar

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

XML

Return Records in one Row without XML

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

STUFF

Follow the author:

View all my tips , LinkedIn Website Slideshare  , ABOUT ME

 
Leave a comment

Posted by on March 7, 2015 in General topics

 

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s