How to transform rows to columns

Question

I have a simple problem when querying the SQL Server 2005 database. I have tables called Customer and Products (1->M). One customer has most 2 products. Instead of output as

CustomerName, ProductName ...

I like to output as

CustomerName, Product1Name, Product2Name ...

Could anybody help me?

Thanks!

Answer 1

in sql2005, there are functions called "PIVOT" and "UNPIVOT" which can be used to transform between rows and columns.

Hope that could help you.

Answer 2

Here two link about pivot:

http://www.tsqltutorials.com/pivot.php

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

I solve my problem with pivot ;)

Answer 3

Like others have said, you can use the PIVOT and UNPIVOT operators. Unfortunately, one of the problems with both PIVOT and UNPIVOT are that you need to know the values you will be pivoting on in advance or else use dynamic SQL.

It sounds like, in your case, you're going to need to use dynamic SQL. To get this working well you'll need to pull a list of the products being used in your query. If you were using the AdventureWorks database, your code would look 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;

Now that you have your columns, you can pull everything that you need pivot on 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

Of course, if you need to make sure you get decent values, you may have to duplicate the logic you're using to build @columns and create an @coalesceColumns variable that will hold the code to COALESCE(col_name, 0) if you need that sort of thing in your query.

Answer 4

As others have mentioned, SQL 2005 has the PIVOT function which is probably the best for general use. In some cases, however, you can simply do something like this.

Select 
 Customer,
 Sum(Case When Product = 'Foo' Then 1 Else 0 End) Foo_Count,
 Sum(Case When Product = 'Bar' Then 1 Else 0 End) Bar_Count
From Customers_Products
Group By Customer

Answer 5

If this is a real-life question you are just asking if it's possible directly in SQL, then you might consider querying the database from Excel or Access, They have excellent built-in pivot tools accessible without building any SQL.

Answer 6

Thank you all of the help! I probably will make it a little clear. Suppose Customer 'AW0000002'has two products, and 'AW0000003' has one product (every customer has maxmium two products). I like to have the output as following:

CustomerName  ProductName1             ProductName2
AW00000002    Adjustable Race          Bearing Ball
AW00000003    Headset Ball Bearings    null

Thanks!