Dynamic SQL in creating columns and summary

• fshguo

  SSC Enthusiast

  Points: 145

  More actions

  February 11, 2010 at 1:40 pm

  #220560

  Hi...I am currently taking a new DBA role, and need some help:

  A manufacture company has many machines that are maintained by

  different contractors in their factories:

  Machine M1,M2,M3,M4… locate in different Factory F1, F2,F3,F4…, and

  they are maintained by different Contractors: C1,C2,C3,C4….

  The main Machine table structure is like:

  MachineID, Factory, Contractor

  M1, F3, C4

  M2, F2, C5

  …

  There is a request to get a summary of each contractor, how many

  machines they are maintaining in all the factories, the result report should

  be like:

  Contractor, Factory1, Factory2, Factory3, Factory4….

  C1, 50, 41, 32, 12,…

  That is: Contractor C1 maintains: 50 machines in Facotry1; 41 machines

  in Factory2; 32 machines in factory3 and 12 machines in Factory4…

  C2, 98, 120,35,65,…and so on...

  Search around and found the following query does do the work:

  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'

  exec(@sqltmp)

  But I do not understand how are the '''+factory+''' and ['+factory+']' really work in this

  query, and the puzzle many '''s.

  Could somebody explain this magic, so that I can completely understand and really re-use the same technics in the future.

  Thanks.

• LutzM

  SSC Guru

  Points: 107049

  More actions

  February 11, 2010 at 4:34 pm

  #1117836

  The concept itself is described in the last link in my signature.

  The reason for all the ''' is simple:

  If you want to get the dynamic sql to look like "...CASE factory WHEN 'F1' ..." you have to provide the quotation marks around F1, since those are not part of the value stored in your table.

  If you want to have a single quotation mark in your dynamic statement you need to surround it by separate quotation marks.

  It's basically the same concept as if you'd like to use the value O'Connor in an select statement:

  SELECT 'O'Connor' (invalid) vs.

  SELECT 'O''Connor' (valid) vs.

  SELECT 'O''Connor'+'''' (valid)

  ---

  
  Lutz
  A pessimist is an optimist with experience.

  How to get fast answers to your question[/url]
  How to post performance related questions[/url]
  Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  February 11, 2010 at 4:43 pm

  #1117840

  [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...

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]