if EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]) And ObjectProperty (ID, n'susertable') = 1) Drop Table [dbo]. [Tabone] Go
Create Table [DBO]. [Tabone] ([Id] [INT] Identity (1, 1) Not NULL, [M] [VARCHAR] (50) Collate Chinese_PRC_CI_AS NULL, [N] [char] (10) Collate Chinese_PRC_CI_AS NOT NULL, [A] [INT] NOT NULL) ON [PRIMARY] GOCREATE PROC TABONE_PASDECLARE @SQL VARCHAR (8000) SET @ SQL = 'SELECT CASE WHEN (Grouping (M) = 1) TEN' '' ALL '' ' ' Else Isnull (M, ' ' '' Total '' ' ') End as M'Select @ SQL = @ SQL ', SUM (Case N when' ' RTRIM (n) ' ' THEN A ELSE '' '' 'End [' RTRIM (N) ']' from (SELECT DISTINCT N from Tab) Aselect @ SQL = @ SQL 'From Tabone Group by M, M with Rollup'Exec @SQL) Print (@SQL) GO
