0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for programming puzzle enthusiasts and code golfers

create table #temp
(
job char(10),
    category varchar(3),
    amount money
)

insert into #temp values ('job1', 'ABC', 1000.00)
insert into #temp values ('job1', 'DEF', 500.00)
insert into #temp values ('job1', 'GHI', 800.00)
insert into #temp values ('job12', 'DEF', 700.00)
insert into #temp values ('job133', 'ABC', 1100.00)

DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(category) + ',' FROM (select distinct category from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
    select job, amount, category from #temp
) src
pivot 
(
    max(amount) for category in (' + @cols + ')
) piv'

execute(@query)

fork query

edited aug 9

created aug 9 anonymous