[previous post appeared while I was writing this one - this can be taken as an addendum to that]

If you really want that sort of information, just extract the data using a reporting tool (Excel, Crystal, SSRS etc) and ask it to cross tabulate it for you - it is easier and for the amount of data I imagine you have there it will work fine.

If you just want to understand the TSQL then:

''' <- the single quote is used to delimit strings, so if you want to include a single quote in a string you have to use two quotes together. There are three here because you have two quotes inside the string immediately followed by a single quote to mark the end of the string.

In this case:

...case factory when '''+factory+''' then 1 else 0 end

is building a string that checks for a specific "factory" value (e.g. F1) in the column factory and if it is found returns 1, otherwise it returns 0

e.g. if factory = F1 you get

...case factory when 'F1' then 1 else 0 end

and ['+factory+'] would give you : [F1] and putting all that together you get

...sum(case factory when 'F1' then 1 else 0 end) [F1]

which is generating the sql to query the data table and assign a value of 1 to that column whenever the factory is = F1 and sum up all the 1s, returning the value (which is actually a count of the number of rows with factory='F1' for each contractor) as column [F1]

declare @sqltmp varchar (3000)

set @sqltmp = 'select contractor'

select @sqltmp = @sqltmp + ', sum (case factory when '''+factory+''' then 1

else 0 end) ['+factory+']' from (select distinct factory from tbl_machines

order by factory) as tt

select @sqltmp = @sqltmp + 'from tbl_machines group by contractor order by

contractor'

The code above should generate a sql command something like this (reformatted for clarity):

select contractor

, sum(case factory when 'F1' then 1 else 0 end) [F1]

, sum(case factory when 'F2' then 1 else 0 end) [F2]

, sum(case factory when 'F3' then 1 else 0 end) [F3]

, sum(case factory when 'F4' then 1 else 0 end) [F4]

from tbl_machines

group by contractor

order by contractor

Personally, I would recommend letting Excel/Crystal/SSRS do the hard work for you - they are all very competent at it and won't care if you add factories/machines/contractors.

This kind of dynamic sql works up to a point, but it's not what SQL is good at - let your favoured reporting tool do the formatting...