How to Convert Rows to Columns in SQL Server

I have a simple problem querying the SQL Server 2005 database. I have tables called Customer and Products (1-> M).

A customer has the most 2 products. Instead of spending as

Customer name, product name …

I would like to spend as

Customer Name, Product1Name, Product2Name …

Could someone help me?

Thank you so much!

Reply:
10 for the answer № 1
As others have said, you can use the PIVOT andUNPIVOT operators. Unfortunately, one of the problems with both PIVOT and UNPIVOT is that you need to know the values you are pivoting to in advance, or use dynamic SQL.

It sounds like you would have to go in your case using dynamic SQL. For this to work, you must create a list of the products used in your query. If you use the AdventureWorks database, your code looks like this:


USE AdventureWorks;
GO

DECLARE @columns NVARCHAR(MAX);

SELECT x.ProductName
INTO #products
FROM (SELECT p.[Name] AS ProductName
FROM Purchasing.Vendor AS v
INNER JOIN Purchasing.PurchaseOrderHeader AS poh ON v.VendorID = poh.VendorID
INNER JOIN Purchasing.PurchaseOrderDetail AS pod ON poh.PurchaseOrderID = pod.PurchaseOrderID
INNER JOIN Production.Product AS p ON pod.ProductID = p.ProductID
GROUP BY p.[Name]) AS x;

SELECT @columns = STUFF(
(SELECT ", " + QUOTENAME(ProductName, "[") AS [text()]
FROM #products FOR XML PATH ("")
), 1, 1, "");

SELECT @columns;

USE AdventureWorks;

DECLARE @columns NVARCHAR(MAX);

SELECT x.ProductName

INTO #products

FROM (SELECT p.[Name] AS ProductName

FROM Purchasing.Vendor AS v

INNER JOIN Purchasing.PurchaseOrderHeader AS poh ON v.VendorID = poh.VendorID

INNER JOIN Purchasing.PurchaseOrderDetail AS pod ON poh.PurchaseOrderID = pod.PurchaseOrderID

INNER JOIN Production.Product AS p ON pod.ProductID = p.ProductID

GROUP BY p.[Name]) AS x;

SELECT @columns = STUFF(

(SELECT ", " + QUOTENAME(ProductName, "[") AS [text()]

FROM #products FOR XML PATH ("")

), 1, 1, "");

SELECT @columns;

Now that you have your columns, you can get everything you need with a dynamic query:


DECLARE @sql NVARCHAR(MAX);

SET @sql = "SELECT CustomerName, " + @columns + "
FROM (
// your query goes here
) AS source
PIVOT (SUM(order_count) FOR product_name IN (" + @columns + ") AS p";

EXEC sp_executesql @sql

DECLARE @sql NVARCHAR(MAX);

SET @sql = "SELECT CustomerName, " + @columns + "

FROM (

// your query goes here

) AS source

PIVOT (SUM(order_count) FOR product_name IN (" + @columns + ") AS p";

EXEC sp_executesql @sql

Of course, if you need to make sure you get reasonable values, you may need to duplicate the logic that you use to create @columns and create a @coalesceColumns variable that contains the COALESCE code (col_name, 0) when You need such things in your query.

Source:

www.simple-talk.com/sql/t-sql-programming/creating-cross-tab-queries-and-pivot-tables-in-sql/

How to Convert Rows to Columns in SQL Server

Like this:

Leave a Comment X

Like this:

You may also like

Leave a Comment X