SQL crosstab instance
A very simple thing, see a lot of friends on the Internet, "How to achieve crosstab?", The following is an example I wrote, the database is based on SQL Server 2000.
- ================================================================================================================================================================== ======
- Cross-table example
- ================================================================================================================================================================== ======
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]
Go
Insert 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 column tables is determined
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 column tables is uncertain
Declare @SQL VARCHAR (8000)
Set @SQL = 'SELECT NAME,'
SELECT @SQL = @SQL 'SUM (Case Subject when' ' Subject ' ''
THEN SOURCE ELSE 0 END) AS '' ' SUBJECT ' ','
From (SELECT DISTINCT SUBJECT from Test) AS A
SELECT @SQL = Left (@ SQL, LEN (@SQL) -1) 'from test group by name'
EXEC (@SQL)
Go
operation result:
