SQLSERVER列轉行

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 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.