[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
The code above should generate a sql command something like this (reformatted for clarity):
, 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]
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...
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Forum Etiquette: How to post Reporting Services problemsForum Etiquette: How to post data/code on a forum to get the best help - by Jeff ModenHow to Post Performance Problems - by Gail Shaw