https://community.spiceworks.com/topic/973727-sql-how-to-convert-multiple-rows-into-single-rows-with-more-columns

SQL - How to convert multiple rows into single rows with more columns?

Hello Everyone,

I have a SQL report which pulls a list of orders. It returns each product on a new row, so orders with multiple products have multiple rows, 5 products max. I need to convert this report into one which has a single row for each order. Please see the attached screen shot showing the format I have and the one that is needed. My Google search pulled up cross-tab SQL queries, but in my case I am not looking to aggregate anything, simply rearrange data. Could someone please advise how this can be accomplished in Microsoft SQL Server 2012 Standard?

Thank you for your help!

• local_offer
• SQL Server 2012star4

Spice (3)Reply (15)

flagReport

AK-47

SERRANO

Enter to win a 3 Win Smart TVs (plus Disney+) AND 8 Runner Ups

Contest ends Feb 24, 2023ContestsAnswer the question to be eligible to win!Contest DetailsView all contests

checkBest Answer

Robert for Microsoft

Brand Representative for Microsoft

HABANERO

May 27th, 2015 at 1:44 AM

This is how I would get the result you want using the sample table and data you posted.

SQL

-- Create table for sample query
Declare @Orders Table (OrderID int,
	Product varchar(10),
	Quantity int);

-- Insert sample data
Insert Into @Orders
Values (100, 'Cup', 1),
	(100, 'Plate', 2),
	(101, 'Cup', 1),
	(102, 'Cup', 2),
	(103, 'Cup', 1),
	(103, 'Plate', 2),
	(103, 'Glass', 1);


With Orders
As (Select ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID,
		*
	From @Orders)
Select OrderID As [Order#],
	Min(Product1) As Product1,
	Min(Quantity1) As Quantity1,
	Min(Product2) As Product2,
	Min(Quantity2) As Quantity2,
	Min(Product3) As Product3,
	Min(Quantity3) As Quantity3
From (Select ROW_NUMBER() Over (Partition By OrderID Order By OrderID) As RowID,
		OrderID,
		'Product' + Cast(RowID as varchar) As ProductNum,
		'Quantity' + Cast(RowID as varchar) As QuantityNum,
		Product,
		Quantity
	From Orders) As Pvt
Pivot (Min(Product)
	For ProductNum In ([Product1], [Product2], [Product3])) As Pvt1
Pivot (Min(Quantity)
	For QuantityNum In ([Quantity1], [Quantity2], [Quantity3])) As Pvt2
Group By OrderID;