SQL Server column forwarding

In this article we will learn how to convert table rows into columns as comma seperated.

-- Create a table variable to store user data
Declare @ mytable table
(
Username varchar (50 ),
Articlename varchar (50)
)

-- Insert some data to table to work on that data
Insert into @ mytable (username, articlename)
Values ('jack', 'asp. net ')
Insert into @ mytable (username, articlename)
Values ('jack', 'SQL Server ')
Insert into @ mytable (username, articlename)
Values ('jack', 'c #')
Insert into @ mytable (username, articlename)
Values ('jack', 'vb. net ')

Insert into @ mytable (username, articlename)
Values ('David', 'java ')
Insert into @ mytable (username, articlename)
Values ('David', 'java beans ')
Insert into @ mytable (username, articlename)
Values ('David', 'java script ')

Select username, articlename from @ mytable

-- This is how the table looks after inserting the data

Now I want all the articles related to Jack and David in a single column.
This how we can achieve this

-- Cross join each user with his article. By cross joining we will get all the articles for each user
Select distinct A. username, articles from @ mytable
Cross apply
(
-- Now get all the articles for each author in XML
Select articlename + ',' from @ mytable B where a. Username = B. Username
For XML Path ('')
) As C (articles)

The output of the below query is shown below.

-- By applying cross join I can able to get all the articles related with Jack and David.