Declare @Actual Table

(

ItemState varchar(10)

, Car varchar(10)

, Truck int

, Boats int

, Planes int

)

insert into @Actual

select 'old', 'volvo', 89, 65, 67 union all

select 'new', 'pinto', 102, 55, 12 union all

select 'used', 'mercedes', 3, 22, 14 union all

select 'stolen', 'winnebago', 4, 11, 156

;

select * From @Actual

;

WITH CTE (ItemState, SortKey, colname, Value)

AS

(

select ItemState, 1, 'cars', cast(car as varchar(500))

from @Actual

UNION ALL

select ItemState, 2, 'Truck', cast(Truck as varchar(500))

from @Actual

UNION ALL

select ItemState, 3, 'Boats', cast(Boats as varchar(500))

from @Actual

UNION ALL

select ItemState, 4, 'Planes', cast(Planes as varchar(500))

from @Actual

)

select t2.colname, [old], [new], [used],[stolen]

from

(

select c.SortKey, c.ItemState, c.colname, c.Value

from cte c

) t1

Pivot

(

max(value) for t1.ItemState IN ([old], [new], [used],[stolen])

) as t2

order by t2.SortKey