How to Convert Rows to Columns in SQL Server – Programming, Pseudocode Example, C# Programming Example

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!

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:

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

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.


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.