How to convert the row data to column in sql server

Pardhu2020
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 2992 red flag
Rating: 5 out of 5  
 1 vote(s)

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.



Page copy protected against web site content infringement by Copyscape

About the Author

Pardhu2020
Full Name: Pardha Saradhi
Member Level:
Member Status: Member
Member Since: 3/19/2013 2:21:22 PM
Country: India

http://www.dotnetfunda.com

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)