In this article we will learn how to convert table rows into columns as comma seperated.
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 A
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.
