declare @example table
(
GroupID integer
, ItemID integer
, Value1 varchar(10)
, Value2 varchar(10)
)
insertinto @example
select 1, 1,'First', 'James' union all
select 1, 2,'Last', 'Smith' union all
select 1, 3,'Country','UK' union all
select 1, 4,'Hobby', 'Rugby' union all
select 2, 1,'First', 'Alan' union all
select 2, 2,'Last', 'Jackson' union all
select 2, 3,'Country','USA' union all
select 2, 4,'Hobby', 'Fishing'
--select * from @example
select
GroupID
,max([First])+' '+max([Last])as [Name]
,max([Country])as Country
,max([Hobby])as Hobby
from
@example
pivot
(
max(Value2)for Value1 in([First], [Last], [Country], [Hobby])
) pvt
groupby GroupID
/*
GroupID Name Country Hobby
----------- ------------ ------- ----------
1 James Smith UK Rugby
2 Alan Jackson USA Fishing
*/