Concatenating Row Values in Transact-SQL, How to Convert ?

Concatenating Row Values in Transact-SQL,  How to Convert ?

                               Microsoft SQL Server is a relational database management system . The main purpose of this software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network , and also works for mobile application.

            
                                    While retrieving data , the Microsoft Sql Server retrieve data base on column name . For example if we write that “Select Surname from tbl_family”. It will retrieve all Surname from family table . It will be row wise , for example Surname1 will come first row , Surname2 will comes in the second rows.

Example :
           Select Surname from tbl_family

Output:
Surname
Surname 1
Surname 2
Surname 3
Surname 4


                  That is Good  . But I can across a form where it was asked , “ Please put , the surname of the family member :“ .I need to give all the surname of the family member in a single row , but Select query was returning in row , that is each Surname in different row . Then what is the solution of this problem ?

                 Suppose , I am designing a report where the same written  “ Please put , the surname of the family member :“ , now I need to put all surname in comma separated line .How to solve the problem ? The are two ways .



THE FIRST WAY:
 I am creating a table

CREATE TABLE tbl_family
(
id            INT IDENTITY,
Surname VARCHAR(500)
)


Now we are inserting values into this table

INSERT INTO tbl_family(Surname)
SELECT 'Surname 1'
UNION
SELECT 'Surname 2'
UNION
SELECT 'Surname 3'
UNION
SELECT 'Surname 4'
UNION
SELECT 'Surname 5'
UNION
SELECT 'Surname 6'
UNION
SELECT 'Surname 7'
UNION
SELECT 'Surname 8'
UNION
SELECT 'Surname 9'
 
No I am declaring a variable

declare @sql VARCHAR(MAX)
SELECT  @sql=''

Now I am building query
SELECT  @sql=@sql+CASE WHEN @sql<>'' THEN ',' ELSE '' END +Surname FROM tbl_family
SELECT @sql

Now the output
Surname 1,Surname 2,Surname 3,Surname 4,Surname 5,Surname 6,Surname 7,Surname 8,Surname 9

You will noticed that separator con be any character or string as you wish .This is a fast way to build string from row data.
THE SECOND WAY:
 This is the smartest method , which I preferred . In this method we need not any variable , we can build such string in query , just using xml and stuff.Let us see the example
 

SELECT STUFF((SELECT ',' + ISNULL(x.Surname,'')
          FROM  tbl_family  x
          FOR XML PATH ('')),1,1,'')

The output will be
Surname 1,Surname 2,Surname 3,Surname 4,Surname 5,Surname 6,Surname 7,Surname 8,Surname 9

Now , FOR XML PATH to the end of a query allows you to output the results of the query as XML elements.