Copyright Notice: 9CBS is this BLOG managed service provider. If this paper involves copyright issues, 9CBS does not assume relevant responsibilities, please contact the copyright owner directly with the article Author.
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:
