create table #p (id char(2), [User] varchar(20), StartDate date)

insert into #p values
('P1',	'Fred',	'12/04/2012'),
('P2',	'Jane',	'01/12/2011'),
('P3',	'Mike',	'01/12/2011'),
('P4',	'Stu',	'01/12/2009')

create table #q (Product char(2), Descrip varchar(20), Value varchar(20))

insert into #q values
('P1',	'Colour',	'Red'),
('P2',	'Colour',	'Green'),
('P2',	'Hard',	'2.2'),
('P2',	'Weight',	'123'),
('P3',	'Colour',	'Black'),
('P4',	'Colour',	'White'),
('P4',	'Weight',	'8.12'),
('P6',	'Mass',	'12.2')

select p.id, p.[User]
		, max(case when rn = 1 then Descrip else '0' end) as Quality1
		, max(case when rn = 1 then Value else '0' end) as Value1
		, max(case when rn = 2 then Descrip else '0' end) as Quality2
		, max(case when rn = 2 then Value else '0' end) as Value2
		, max(case when rn = 3 then Descrip else '0' end) as Quality3
		, max(case when rn = 3 then Value else '0' end) as Value3
		, max(case when rn = 4 then Descrip else '0' end) as Quality4
		, max(case when rn = 4 then Value else '0' end) as Value4
from 
	(
	select Product, Descrip, Value,
			rn = ROW_NUMBER() over(partition by product order by descrip)
	from #q
	) q
join #p p on p.id = q.product
group by p.id, p.[User]