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!

From stackoverflow

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

  Hope that could help you.

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

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

• 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
  

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

  le dorfier : Interesting. We have someone who is offended by simply Access and/or Excel. And won't identify themselves. I guess a certain % of your answers are subject to random vandalism. Oh well.

• 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!