Originally posted: 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 table values ('01 ',' China unit ') --1 INSERT unit table VALUES (' 0101 ', 'Shandong Unit') --2 INSERT Unit Table Values ('010101', 'Shandong Jinan Unit') --3 INSERT Unit Values ('010102', 'Shandong Qingdao Unit ") --3 Insert Unit Table VALUES ('01010201', 'Shandong Qingdao is a union unit one') --4 level INSERT unit table VALUES ('01010202', 'Shandong Qingdao, Upset 2') --4 INSERT unit VALUES ('0102', 'Shanxi Unit') --2 INSERT Unit Table Values ('010201', 'Shanxi Datong Unit ") --3 INSERT Unit Values (' 0103 ',' Shaanxi Unit ') --2 Insert Unit Table 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 table 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 Table VALUES ('0004', 'Electric Vehicle', '010302', 1) GO
/ * - Require grading summary, then transfers the following results: select '', '', '', 'China Unit', 'China Unit', 'China Unit', 'China Unit', 'China Unit', 'China Unit', 'China Unit', 'China Unit', 'China Union All Select', '', ',' Shandong Unit ',' Shandong Unit ',' Shandong Unit ',' Shandong Unit ' , 'Shanxi Unit', 'Shanxi Unit', 'Shaanxi Unit', 'Shaanxi Unit', 'Shaanxi Union' Union All Select ',' ',' ',' ',', 'Shandong Qingdao Unit', ' Shandong Qingdao unit ',' ',' ',' ',' 'union all select' serial number ',' equipment name ',' total ',' small meter ',' Shandong Jinan Unit ',' Shandong Qingdao Aerium bit one ',' Shandong Qingdao is a default number 2 ',' small gauge ',' Shanxi Datong unit ',' small meter ',' Shaanxi Xi'an unit ',' Shaanxi Xianyang unit 'union all succ', 'total' , '7', '4', '2', '2', '1', '', '1'Union All Slect' 1 ',' (0001) Electronics Synthetic ',' 3 ',' 2 ',' 1 ',' 1 ',' ',' 1 ',' 1 ',' ',' ',' Union All Select '2', '(0002) motor ',' 2 ',' 2 ',' 1 ',' 1 ',' ',' ',' ',' ',' 'Union All Select' 3 ',' (0003) Electric Brushes', '1', '', '', '', '', '1', '1', '', '', '' Union All Select '4', '(0004) Electric Vehicle', '1' , '', '', '', '', '', '', '', '1' statistics results Description: 1. Unit code fixing every two digits 2. Statistics All of the latest levels, if the unit is not data in the supply table, the corresponding display is 03. Small count is based on the secondary unit merge 5. The head head in the result is a grading, and the first-level unit is in the first line. The secondary unit is in the second line. If the unit has appeared in statistics, "serial number ',' equipment name", 'total' ", no longer appears in the corresponding level of the corresponding series - * /
- Query declare @i varchar (10), @ s12 varchar (8000), @ s13 varchar (8000), @ s2 varchar (8000), @ s3 varchar (8000), @ s14 varchar (8000) SELECT @ S11 = ', @ S12 =', @ S13 = '', @ S14 = ', @ S2 =', @ S3 = ''
SELECT A = Left (unit code, 4), b = left (unit code, LEN (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 life' '' Else ' Case When @i>' 4 '' '' ',' '' '' '' 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. Unit code) A' end @ S13 , @ S14 = ' ' 'union all select' ' @' @ 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) Code) = 4 TEN ', [' b. Unit Name '_ small gauge] = Cast (SUM (Case Left (Case Left) WHEN' ' B. Unit Code ' 'THEN Supply Quantity ELSE 0 END) AS VARCHAR) 'ELSE', [' b. Unit Name '] = Cast (SUM (Case Unit Code when '' B. Unit Code '' 'THEN Supply Quantity ELSE 0 END) As varchar) 'endfrom units BWHERE LEN (unit code) = 4 or not exists WHERE unit code LIKE B. Unit code ' __ ') Order BY unit code set @ S2 = Replace (@ S2, '' '', '' '') Exec ('SELECT Serial = Cast (' '' AS VARCHAR (10)), Equipment Name = Case When Grouping = 1 Ten '' Total 'ELSE '' ('' Material Number '') '' Material Name End, Supply Quantity = Cast (Sum (Supply Number) AS VARCHAR) ' @ S3 ' Into #TFROM Supply Meter Group By Material Number, Material Name with Rolluphaving Grouping (Material Name) = 0 or Grouping = 1Order by Grouping DescDeclare @i intset @ i = = @ i 1, serial number = Case When = 0 Then '' 'Else Cast (@i as varchar) end
Declare @ varchar (10) ' @ S11 ' SELECT ' @ S12 ' ' @ S13 ' EXEC ('' ' @ S14 ' '' union allselect '' '' serial number '' ',' '' ' Equipment Name '' ',' '' Total '' '' ' @ S2 ' Union AllSelect * from #t '') Drop Table # ') GO
- Delete Test DROP TABLE unit table, supply table
/ * - Test results (see yourself) - * /
