SQLTeam.com | Weblogs | Forums

SQL pivot

hello I need some help
I have a query i have generated that is laid out as

Product | jobno

I want to pivot it as

JobNo | product1 | product 2

the problem is all the products are different but each jobno will have a maximum of 2 products against it

i cant wrap my head around how i can achieve this

Tyr using RowNum. This will only return Product1 and Product2, so if there are more than 2, it will not return those, but based on the order, it will return different results if a third is added.

Create table #Products (Product varchar(10), 
						JobNo int)

insert into #Products (Product, JobNo)
values
('Prod1',1),
('Prod2',1),
('Prod3',2),
('Prod4',2),
('Prod1',3),
('Prod5',3),
('Prod4',4),
('Prod1',4)

Select * from (
		Select JobNo, Product, 'Product' + cast(Row_number() over (partition by JobNo order by Product) as char(1)) as RowNum 
		  from #Products) t
pivot (Max(Product) for RowNum in ([Product1], [Product2])) pvt
SELECT 
    JobNo, MIN(Product) AS Product1,
	CASE WHEN COUNT(*) = 1 THEN NULL ELSE MAX(Product) END AS Product2
FROM dbo.your_table_name
GROUP BY JobNo