Group statistics for simulating multi-level Headers

Original post address:
Http://community.csdn.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', 'unit of China') -- level 1
Insert unit table values ('20170101', 'shandong unit ') -- level 2
Insert unit table values ('20170101', 'shandong Jinan unit ') -- level 3
Insert unit table values ('20170101', 'shandong Qingdao unit ') -- level 3
Insert unit table values ('20170101', 'shandong Qingdao is Mo unit 1') -- level 4
Insert unit table values ('20170101', 'shandong Qingdao is Mo unit 2') -- level 4
Insert unit table values ('20170101', 'shanxi unit ') -- level 2
Insert unit table values ('20170101', 'shanxi Datong unit ') -- level 3
Insert unit table values ('20170101', 'shaanxi unit ') -- level 2
Insert unit table values ('20170101', 'shaanxi Xi'an unit ') -- level 3
-- Insert unit table values ('20170101', 'unit A of Xi'an, Shaanxi province ') -- level 3
-- Insert unit table values ('20170101', 'shaanxi Xi'an AA unit ') -- level 3
Insert unit table values ('20170101', 'shaanxi Xianyang unit ') -- level 3

Create Table Supply table (material number varchar (10), material name varchar (50), unit code varchar (10), supply quantity INT)
Insert Supply table values ('123', 'electronic name', '123', 1)
Insert Supply table values ('20170301', 'motors', '20160301', 1)
Insert Supply table values ('123', 'electronic name', '123', 1)
Insert Supply table values ('20170301', 'motors', '20160301', 1)
Insert Supply table values ('123', 'electronic name', '123', 1)
Insert Supply table values ('20170101', 'electric fl ', '20160301', 1)
Insert Supply table values ('20170301', 'electric Vehicle ', '20160301', 1)
Go

/* -- Requirement
 
Classify and summarize, and then transpose to get the following results:
Select '', 'China ', 'UNIT in China', unit in China', unit in China'
Union all select '', 'shandong ', 'shanxi ', 'shanxi unit ', 'shaanxi unit', 'shaanxi unit ', and 'shaanxi unit'
Union all select '','', 'shandong Qingdao unit ', 'shandong Qingdao unit ','','','', '',''
Union all select 'sequence No. ', 'equipment name', 'Total', 'subtotal', 'shandong Jinan unit ', 'shandong Qingdao is Mo Unit 1 ', 'shandong Qingdao is Mo unit 2', 'subtotal ', 'shanxi Datong unit', 'subtotal ', 'shaanxi Xi'an unit', 'shaanxi Xianyang unit'
Union all select '', 'Total', '7', '4', '2', '2','', '2', '2 ', '1', '', '1'
Union all select '1', '(0001) Electronic name', '3', '2', '1', '1', '', '1 ', '1 ','','',''
Union all select '2', '(0002) electric motors', '2', '2', '1', '1 ','','','', '','',''
Union all select '3', '(0003) Electric Brush', '1', '', '1', '1', '1 ', '','',''
Union all select '4', '(0004) Electric Vehicle', '1', '', '1 ', '', '1'

Statistical result description:
1. The unit code is fixed. Each two digits is 1 level.
2. the last level of all statistical unit tables. If this unit has no data in the Supply table, the corresponding value is 0.
3. The subtotal is obtained by merging the second-level units.
4. the table header in the result is classified. The level 1 unit is in the first row and the Level 2 unit is in the second row. If the unit is already in statistics, that is, "No.", "Equipment Name ', the 'Total' line does not appear in the header of the corresponding level.
--*/

-- 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 ='', @ S13 = '', @ S14 =''
, @ S2 = '', @ S3 =''

Select a = left (unit code, 4), B = left (unit code, Len (unit code)-2), c = Len (unit code)-2
Into # from Unit Table B
Where not exists (select * from Unit table 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' then''', ''' end 'else ''', ''' + unit name + ''' '''end' end
+ '+ '', ''' + Unit name + ''', @ = A from'
+ Case when @ I <= '4' then' # A, Unit Table B where left (A. B, '+ @ I +') = B. unit code'
Else '(select unit name = case when. c> = '+ @ I +' Then unit name else ''' end,. * From # A, Unit Table B where left (. b, '+ @ I +') = B. unit code) a' end
+ @ S13
, @ S14 = '+ ''Union all select'' + @' + @ I + @ S14
, @ I = @ I-2
Select @ S12 = stuff (@ S12, 1, 1 ,'')
, @ S14 = stuff (@ S14, 1, 13 ,'')

Select @ S2 = @ S2 + ', ''' + case when Len (B. unit code) = 4 then' subtotal 'else B. unit name end + ''''
, @ S3 = @ S3 + case
When Len (B. unit code) = 4
Then', ['+ B. unit name + '_ subtotal] = cast (sum (case left (unit code, 4) when''' + B. unit code + '''then quantity else 0 end) as varchar )'
Else ', [' + B. unit name + '] = cast (sum (case unit code when''' + B. unit code + '''then quantity else 0 end) as varchar )'
End
From Unit Table B
Where Len (unit code) = 4
Or not exists (select * from Unit table where unit code like B. unit code + '__')
Order by unit code
Set @ S2 = Replace (@ S2 ,'''','''''')

Exec ('
Select serial number = cast (''' as varchar (10 ))
, Device name = case
When grouping (material number) = 1 then ''total''
Else ''('' + material number + '')'' + material name end
, Supply quantity = cast (sum (supply quantity) as varchar) '+ @ S3 +'
Into # T
From Supply table
Group by item No., item name with Rollup
Having grouping (material name) = 0 or grouping (material number) = 1
Order by grouping (material number) DESC
Declare @ I int
Set @ I =-1
Update # t set @ I = @ I + 1, serial number = case when @ I = 0 then ''' else cast (@ I as varchar) End

Declare @ varchar (10) '+ @ S11 +'
Select '+ @ S12 +'
'+ @ S13 +'
Exec (''' + @ S14 + '+''
Union all
Select ''' number ''', ''' Equipment Name ''', ''' total ''' + @ S2 +'
Union all
Select * from # T
'')
Drop table #
')
Go

-- Delete test
Drop table unit table, Supply table

/* -- Test result (read by yourself )--*/