Rows to Column in SQL Server

Method 1: Rows to Column Using COALESCE Function

--// Table Variable Declarion
Declare @Product Table
(
     ProductID        Int
    ,ProductName    Varchar(50)
)

--// Data Insertion to Table varaible.
Insert Into @Product Values
(1,'PC'),
(2,'Laptop'),
(3,'Mouse'),
(4,'Keyboard'),
(5,'Modem')

--// Get all Data from Table Variable normal manner.
Select * from @Product

--// Query for to get product name in single column model.
Declare @str Varchar(5000)

Select @str = Coalesce(@str + ',','') + ProductName From @Product

Select @str

The COALESCE Function Returns the first nonnull expression among its arguments.


Method 2: Rows to column without using variable.

Use Adventureworks
go

SELECT
    Name = SUBSTRING(
                        (
                            SELECT ',' + ISNULL(Name,'')
                            FROM Production.Product p
                            FOR XML PATH('')
                         ),2,1000000000
                    )
                   
                   
I suggest method 2 using a good practice in t-sql coding for rows to column purpose.