Originally posted address: http://community.9cbs.net/expert/topic/3434/3434688.xml? Temp = 3.246486e-03 - Test data CREATE TABLE unit table (unit code varchar (10), unit name VARCHAR ( 50)) Insert Unit Values ('01 ',' China Unit ') - Level INSERT Unit Table Values (' 0101 ',' Shandong Unit ') --2 INSERT Unit Values (' 010101 ',' Shandong Jinan Unit ') --3 INSERT Unit Table Values (' 010102 ',' Shandong Qingdao Unit ") --3 INSERT Unit Table Values ('01010201', 'Shandong Qingdao 即 一') --4 INSERT Unit table VALUES ('01010202', 'Shandong Qingdao Usi 2') --4 level INSERT unit table VALUES ('0102', 'Shanxi Unit ") --2 INSERT unit VALUES (' 010201 ',' Shanxi Datong Unit ') --3 INSERT Units Values (' 0103 ',' Shaanxi Unit ') --2 INSERT Unit Values (' 010301 ',' Shaanxi Xi'an Unit ") --3 - INSERT Unit VALUES ('01030101', 'Shaanxi Xi'an A) --3 - INSERT unit VALUES (' 0103010101 ',' Shaanxi Xi'an AA unit ') --3 INSERT unit VALUES (' 010302 ',' Shaanxi Xianyang Unit ') --3 Create Table Supply Meter (Material Number VARCHAR (10), Material Name VARCHAR (50), unit code VARCHAR (10), supply quantity INT) INSERT Supply Meter Values (' 0001 ',' Electronics ',' 010101 ', 1) INSERT Supply Meter Values (' 0002 ',' Motor ',' 010101 ', 1) INSERT Supply Meter VALUES ('0001', 'Electronics ",' 01010201 ', 1) INSERT Supply Meter VALUES (' 0002 ',' Motor ',' 01010201 ', 1) INSERT Supply Meter Values (' 0001 ',' Electronics", '010201', 1) INSERT Supply Meter Values ('0003', 'Electric Brushes', '010201', 1) INSERT Supply Meter VALUES ('0004', 'Electric Vehicle', '010302', 1) GO / * - - Require grading summary, then transfers the following results: select '', '', '', 'China,' China Unit ',' China Unit ',' China Unit ',' China Unit ',' China Unit ','
China Unit ',' China Unit ',' China Union 'Union All Select', '', '', 'Shandong Unit', 'Shandong Unit', 'Shandong Unit', 'Shandong Unit', 'Shanxi Unit', 'Shanxi Unit', 'Shaanxi Unit', 'Shaanxi Unit', 'Shaanxi Unit' Union All Select ',' ',' ',' ',' Shandong Qingdao Unit ',' Shandong Qingdao Unit ',' ',' ',' ',' ',' Equipment Name ',' Total ',' Small Meter ',' Shandong Jinan Unit ',' Shandong Qingdao is an unnecessary unit one ',' Shandong Qingdao is a miles 2 ',' small meter ',' Shanxi Datong unit ',' small meter ',' Shaanxi Xi'an unit ',' Shaanxi Xianyang unit 'union all succ', 'total', '7', ' 4 ',' 2 ',' 2 ',' ',' 2 ',' 2 ',' 1 ',' ',' 1'Union All Select '1', '(0001) Electronics', '3' , '2', '1', '1', '', '1', '', '', '' Union All Slect '2', '(0002) Motor', '2', '2', '1', '1', '', '', '', '', ',' 'Union All Select' 3 ',' (0003) Electric Brushes', '1', '' , '', '', '', '', '' Union All Select '4', '(0004) Electric Vehicle', '1', '', '' , '', '', '', '', '1', '', '1' statistics Description: 1. Unit code fixing every two digits 2. All the latest levels in the statistical unit table, If the unit does not have data in the supply table, the corresponding display is 03. Small count is based on the secondary unit merge 5. The header in the result is a grading, the first-level unit is in the first line, the secondary unit is in the second unit OK, if the unit has appeared in statistics, "Serial No. ',' Equipment Name ',' Total '", no longer appears in the corresponding series of heads - * / - query processing DECLARE @i varchar (10), @ S11 varchar (8000), @ S12 varchar (8000), @ s13 varchar (8000), @ s2 varchar (8000), @ s3 varchar (8000), @
S14 varchar (8000) SELECT @ S11 = ', @ S12 =', @ S14 = ', @ S2 =', @ S3 = '' SELECT A = LEFT (unit code, 4 ), b = left (unit code) -2), c = len (unit code) -2into # from unit table Bwhere not exists (SELECT * FROM unit WHERE unit code LIKE B. Unit code " __ ') Order BY unit code SELECT @ i = max (len (b)) from #While @i>' 0 'select @ S11 =', @ ' @ i ' varchar (8000) ' @ S11, @ S12 = ', @' @ i '=' '' '' '' ',' '' '' '' ',' '' '' '' '', '' '' '' '' ',' '' '' '' '' ',' '' '' '' '' '' @ S12, @ S13 = 'Set @ = NULL SELECT @ ' @ i ' = @ ' @ i ' case when @ = a Then '' 'Else' Case When @i> '4' Ten '', '' '' '' 'end' else '', '' '' '' unit name '' '' '' 'end' end ' ' ',' '' '' unit name '' '' '' '', @ @ = a from ' Case When @i <=' 4 'TEN' # a, unit table B WHERE LEFT (ab, ' @ i ') = b. Unit Code 'Else' Select Unit Name = Case When AC> = ' @ i ' THEN Unit Name Else '' 'End, a. * from # A, Unit Table B WHERE LEFT (AB,' @ i ') = b. Mini code ) A 'end @ S13, @ S14 =' '' union all succ '' ' @' @ i @ S14, @ i = @ i-2select @
S12 = stuff (@ S12, 1, 1, '), @ S14 = stuff (@ S14, 1, 13,') select @ S2 = @ S2 ',' '' Case When Len (b. unit code ) = 4 THEN 'small meter' else b. Unit Name END '' ', @ S3 = @ S3 Case When Len (b. Unit code) = 4 THEN', [' b. Unit Name " Counter] = CAST (SUM (Case Left (Case Left (CASE Code, 4) WHEN '' B. Units Else 0 End) as varchar) 'Else', [' B. Mini Name '] = CAST (SUM (Case unit code when' ' b. Units code ' '' THEN Supply Quantity ELSE 0 END) AS VARCHAR) 'endFrom unit table Bwhere Len (unit code) = 4 or not exists (SELECT * FROM unit WHERE unit code LIKE B. Unit code '__') Order BY unit code set @ S2 = Replace (@ S2, '' ',' '' ') EXEC (' SELECT serial number = CAST '' 'AS VARCHAR (10)), equipment name = case when grouping (Material number) = 1 THEN' 'total' 'Else' '(' ' supplied ' ')' ' supplied Name End, supply Number = CAST (Sum (Supply Quantity) As Varchar) ' @ S3 ' Into #TFROM Supplies GROUP BY Material Number, Material Name with Rolluphaving Grouping (Material Name) = 0 or Grouping (Material Number) = 1ORDER by grouping (Material " Number) Descdeclare @i intSet @ i = -1update #t set @ i = @ i 1, serial number = case when @ i = 0 Then '' '' Else Cast (@i as varchar) endddeclare @ varchar (10) ' @ S11 'SELECT' @ S12 '' @ S13 'EXEC (' '' @ s14 ' ' 'union allselect' '' 'serial number' '', '' '' equipment name '' ', '' '' Total '' '' @ S2 'union allselect * from #t'
