SQLTeam.com | Weblogs | Forums

Pivot from row-level without loosing the ColNames


#1

Hi,
i need to transform this data from row-level to columnar data.
Pivot with ColName and ColValue

Regards
Nicole :wink:

ColName ColValue
itemClass yard
itemType SAL
employeeId tony
stationId 800
stage PWT
subStage AVM

#2

Will it always be these same rows (static) or could there be more rows (dynamic)

create table #gpc44(ColName varchar(150), ColValue varchar(150))

insert into #gpc44
select 'itemClass',	'yard' union
select 'itemType',	'SAL' union
select 'employeeId',	'tony' union
select 'stationId',	'800' union
select 'stage',	'PWT' union
select 'subStage',	'AVM' 


select itemClass, itemType, employeeId, stationId, stage, subStage
from
(
  select ColValue, ColName
  from #gpc44
) d
pivot
(
  max(ColValue)
  for ColName in (itemClass, itemType, employeeId, stationId, stage, subStage)
) piv;

select * From #gpc44

drop table #gpc44