The STUFF 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.
Requirement
We need the report where what all the city’s did the user went in a single row . At ant given point of time I need to convert my data from row to column and send to WCF server with comma separated (,).
SQL Table :
Above the table, a new column called as Ticket_Booked_Place is needed where the customer id 1001 need to be in a single row means (Hyderabad,Chennai,Delhi ).
Table Script:
CREATE TABLE [dbo].[tbl_reservation_details](
[Reservation_id] [int] IDENTITY(1,1) NOT NULL,
[Customer_Id] [int] NULL,
[Customer_Name] [nvarchar](50) NULL,
[Ticket_Booked] [nvarchar](50) NULL,
CONSTRAINT [PK_reservation_details] PRIMARY KEY CLUSTERED
(
[Reservation_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [tbl_reservation_details]([Customer_Id],[Customer_Name],[Ticket_Booked]) VALUES (1001,'Pardha','Hyderabad')
INSERT INTO [tbl_reservation_details]([Customer_Id],[Customer_Name],[Ticket_Booked]) VALUES (1002,'rakesh','Vizag')
INSERT INTO [tbl_reservation_details]([Customer_Id],[Customer_Name],[Ticket_Booked]) VALUES (1003,'Akesh','Hyderabad')
INSERT INTO [tbl_reservation_details]([Customer_Id],[Customer_Name],[Ticket_Booked]) VALUES (1001,'Pardha','Chennai')
INSERT INTO [tbl_reservation_details]([Customer_Id],[Customer_Name],[Ticket_Booked]) VALUES (1004,'Ajay','Hyderabad')
INSERT INTO [tbl_reservation_details]([Customer_Id],[Customer_Name],[Ticket_Booked]) VALUES (1001,'Pardha','Delhi')
Using STUFF we can achieve this requirement:
The STUFF 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.
Syntax:
STUFF ( character_expression , start , length , replaceWith_expression )
Arguments:
character_expression:
Is an expression of character data. character_expression
can be a constant, variable, or column of either character or binary data.
start:
Is an integer value that specifies the location to start deletion and insertion. If start
or length
is negative, a null string is returned. If start
is longer than the first character_expression
, a null string is returned. start
can be of type bigint.
length:
Is an integer that specifies the number of characters to delete. If length
is longer than the first character_expression
, deletion occurs up to the last character in the lastcharacter_expression
. length can be of type bigint.
Replace with expression:
Is an expression of character data. character_expression
can be a constant, variable, or column of either character or binary data. This expression will replace length
characters of character_expression
beginning at start
.
Return Types:
Returns character data if character_expression
is one of the supported character data types. Returns binary data if character_expression
is one of the supported binary data types.
select distinct t.Customer_Id,Customer_Name,
STUFF((SELECT distinct ', ' + t1.Ticket_Booked
from tbl_reservation_details t1
where t.Customer_Id = t1.Customer_Id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'') AddressPointName
from tbl_reservation_details t;
Using the above code we can achieve the requirement.