CODEWITHSUNDEEP
sqlsql-server
Liang Wu
Liang Wu

Reputation: 1832

How to transform rows to columns

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!

Upvotes: 8

Views: 4359

Answers (4)

Jamal Hansen
Jamal Hansen

Reputation: 954

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

Upvotes: 1

anon
anon

Reputation:

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.

Upvotes: 10

mavera
mavera

Reputation: 3241

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 ;)

Upvotes: 6

MGC
MGC

Reputation: 505

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

Hope that could help you.

Upvotes: 1

Related Questions