Grouping statistics for analog multilevel table heads

grouping | statistics
Original post address:
http://community.csdn.net/Expert/topic/3434/3434688.xml?temp=3.246486E-03

--Test data
CREATE TABLE Unit (unit code varchar (10), Unit name varchar (50))
Insert Unit table VALUES (' 01 ', ' Chinese Unit ')--level 1
Insert Unit table VALUES (' 0101 ', ' Shandong Unit ')--level 2
Insert Unit table VALUES (' 010101 ', ' Shandong Jinan Unit ')--level 3
Insert Unit table VALUES (' 010102 ', ' Shandong Qingdao Unit ')--level 3
Insert Unit table VALUES (' 01010201 ', ' Qingdao, Shandong, one ')--level 4
Insert Unit table VALUES (' 01010202 ', ' Qingdao, Shandong, two ')--level 4
Insert Unit table VALUES (' 0102 ', ' Shanxi Unit ')--level 2
Insert Unit table VALUES (' 010201 ', ' Shanxi Datong Unit ')--level 3
Insert Unit table VALUES (' 0103 ', ' Shaanxi Unit ')--level 2
Insert Unit table VALUES (' 010301 ', ' Shaanxi Xian Unit ')--level 3
--insert Unit table values (' 01030101 ', ' XI ' an unit ')--level 3
--insert Unit table values (' 0103010101 ', ' XI ' an AA unit ')--level 3
Insert Unit table VALUES (' 010302 ', ' 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 (' 0001 ', ' electronic weighing ', ' 010101 ', 1)
Insert Supply table VALUES (' 0002 ', ' Motor ', ' 010101 ', 1)
Insert Supply table VALUES (' 0001 ', ' electronic weighing ', ' 01010201 ', 1)
Insert Supply table VALUES (' 0002 ', ' Motor ', ' 01010201 ', 1)
Insert Supply table VALUES (' 0001 ', ' electronic weighing ', ' 010201 ', 1)
Insert Supply table VALUES (' 0003 ', ' electric brushes ', ' 010201 ', 1)
Insert Supply table VALUES (' 0004 ', ' electric car ', ' 010302 ', 1)
Go

/*--Requirements

The outline rollup and then transpose the following results:
Select ', ', ', ' Chinese unit ', ' Chinese unit ', ' Chinese unit ', ' Chinese unit ', ' Chinese unit ', ' Chinese unit ', ' Chinese unit ', ' Chinese unit ', ' Chinese unit '
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 ', ', ', ', ', ', ', ', ', ', '
UNION ALL SELECT ' Serial number ', ' Equipment name ', ' Total ', ' subtotal ', ' Shandong Jinan unit ', ' Qingdao, Shandong, is the unit one ', ' Shandong Qingdao is the tacit unit two ', ' subtotal ', ' Shanxi Datong unit ', ' subtotal ', ' Shaanxi Xian unit ', ' Shaanxi Xianyang unit '
UNION ALL SELECT ', ' Total ', ' 7 ', ' 4 ', ' 2 ', ' 2 ', ', ', ' 2 ', ' 2 ', ' 1 ', ', ' 1 '
UNION ALL SELECT ' 1 ', ' (0001) electronic weighing ', ' 3 ', ' 2 ', ' 1 ', ' 1 ', ', ' 1 ', ' 1 ', ', ', ', '
UNION ALL SELECT ' 2 ', ' (0002) motor ', ' 2 ', ' 2 ', ' 1 ', ' 1 ', ', ', ', ', ', ', ', ', ', ', '
UNION ALL SELECT ' 3 ', ' (0003) electric Brush ', ' 1 ', ', ', ', ', ', ', ', ', ', ' 1 ', ' 1 ', ', ', ', '
UNION ALL SELECT ' 4 ', ' (0004) electric vehicle ', ' 1 ', ', ', ', ', ', ', ', ', ', ', ', ', ' 1 ', ', ', ' 1 '


Statistical results show:
1. The Unit code fixed every two digits to 1 levels
2. All the last levels in the Statistical Unit table, if the unit has no data in the supply table, the corresponding display is 0
3. Subtotal is based on a combination of level two units
4. The table headers in the results are graded, level one unit in the first line, two units in the second line, if the unit has been in the statistical data, that is, "serial number", ' Equipment name ', ' Total ', the line appears, it is no longer in the corresponding series of the table head
--*/

--Query processing
DECLARE @i varchar (a), @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 ' ' Else '
+case when @i> ' 4 ' Then ', ', ' ', ', ', ', ', ', ', ', ', ', ' '
+ ' + ', ' ' ' + 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 a.c>= ' +@i+ ' then unit name Else ' "' end,a.* from # A, Unit table B where left (a.b, ' +@i+ ') =b. Unit code) a ' En D
+ @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 ' the 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 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) '
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))
, Equipment 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 material number, material 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 ' + @s11 + '
Select ' + @s12 + '
' + @s13 + '
EXEC (' + @s14 + ' + ' + ')
UNION ALL
Select ' ' Serial number ' ', ' ' ', ' ', ' ', ' ', ' ', ', ', ', ', ', ' + @s2 + '
UNION ALL
SELECT * from #t
'')
DROP TABLE #
')
Go

--Delete test
Drop table Unit table, Supply table

/*--test results (see for yourself)--*/