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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | 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 get everything you need with a dynamic query:
1 2 3 4 5 6 7 8 9 10 11 | 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/