Original text: http://blog.9cbs.net/lihonggen0/archive/2003/05/26/13634.aspx
SQL Cross-Table Instance Database Based on SQL Server 2000
- ================================================================================================================================================================== ====== - Cross-table instance - ===================================== ==================
Construction form:
Run in the query analyzer: create table [test] ([ID] [INT] Identity (1, 1) Not null, [Name] [nvarchar] (50) collate chinese_prc_ci_as null, [Subject] [nvarchar] (50) Collate chinese_prc_ci_as null, [Source] [NuMERIC] (18, 0) NULL) ON [PRIMARY] GoInsert INTO [TEST] ([Name], [Subject], [Source]) VALUES (N 'Zhang San', N 'Language ', 60) INSERT INTO [TEST] ([Name], [Subject], [SOURCE]) VALUES (N' Li Si ', N' Mathematics', 70) Insert Into [Test] ([Name], [SUBJECT] [Source]) VALUES (N 'King 5', N 'English', 80) Insert Into [Test] ([Name], [Subject], [Source]) VALUES (N 'King 5', N 'Mathematics " , 75) INSERT INTO [TEST] ([Name], [Subject], [Source]) VALUES (N King 5 ', N' Language ", 57) Insert Into [Test] ([Name], [Subject], [Source]) VALUES (N 'Li Si', N 'Language', 80) Insert Into [Test] ([Name], [Subject], [Source]) VALUES (N 'Zhang San', N 'English ", 100)
Go
Implementation of the intersection statement:
- Used to: The number of crosstables is determined by SELECT NAME, SUM (Case Subject When 'Mathematics' Then Source Else 0 End) AS 'Mathematics', SUM (Case Subject When 'English' Then Source Else 0 End) AS 'English', SUM (Case Subject When 'Language' Then Source Else 0 End) AS 'Language' from Test Group By Name - Used to: The number of crosstables is uncertain Declare @SQL VARCHAR (8000) SET @ SQL = 'SELECT NAME,' SELECT @SQL = @SQL 'SUM (Case Subject when' ' SUBJECT ' '' 'THEN SOURCE ELSE 0 END) AS' '' '' ',' FROM (Select Distinct Subject From test) as aselect @SQL = left (@ SQL, LEN (@SQL) -1) 'from test group by name'exec (@sql) Go
