Simple implementation of a crosstab 1: Using Stored procedures

A friend asked to help realize the cross table (some rows into columns), although the implementation of the previous, but did not tidy, and today by the way to tidy up, easy to their future reference, I hope to other users to help, welcome to point out the deficiencies ^_^

The original data in the database is similar:

Require the foreground to appear as:


CREATE TABLE structure: CREATE TABLE [dbo].  [T_score] (
[Scoreid]   [INT] IDENTITY (1, 1) not NULL,
[Subjectname]  [varchar] () not NULL,
[Studentname]  [varchar] (ten) Not NULL,
[Scorevalue]   [Real] Not NULL,
[Examdate]   [DateTime] Not NULL
)

Sample Data
INSERT into T_score (Subjectname, Studentname, Scorevalue, Examdate)
VALUES (' C language ', ' Sun Guang ', 80, ' 2006-01-05 ')

INSERT into T_score (Subjectname, Studentname, Scorevalue, Examdate)
VALUES (' Japanese ', ' Sun Guang ', 79, ' 2006-07-06 ')

INSERT into T_score (Subjectname, Studentname, Scorevalue, Examdate)
VALUES (' C language ', ' Sun Guang ', 89, ' 2006-08-09 ')

INSERT into T_score (Subjectname, Studentname, Scorevalue, Examdate)
VALUES (' English ', ' King II ', 77, ' 2006-09-10 ')

INSERT into T_score (Subjectname, Studentname, Scorevalue, Examdate)
VALUES (' English ', ' Sun Guang ', 77, ' 2006-07-06 ')

INSERT into T_score (Subjectname, Studentname, Scorevalue, Examdate)
VALUES (' C language ', ' King II ', 89, ' 2006-08-09 ')

1. Implemented through stored procedures
Implementation essentials:
A. Use a temporary table (#ScoreTbl) to store all the results of a specified student (depending on the actual situation, there may be more conditions)
Note: Table variables cannot be used here because table variables cannot be used in dynamic SQL statements.

B. Use an average sign bit in the temporary table #scoretbl because the average of each account is calculated and then deposited into the #scoretbl
Of course, according to the demand, can also increase the mark of the total score and so on

C. Creating a dynamic dateexam (exam time) column using a recursive SELECT statement (reference: Paul Nielsen's Microsoft SQL Server 2000 Treasure P353 12-7 recursive select variable)
Note: There is a potential bug in the recursive @sql statement here that the @sql length must be less than 8,000 characters (4,000 characters is required if the dynamic statement is executed using sq_executesql)
Brother Jiangjian. The solution of the string cannot exceed 8000 and the treatment of the crosstab discusses three alternative scenarios.
This example does not make this discussion, but only to implement the crosstab ^_^

D. Use case expressions to select Dateexam corresponding Scorevalue (score value) (reference: Paul Nielsen's Microsoft SQL Server 2000 Treasure P353 12-7-2 dynamic crosstab query)

E. Use aggregate functions (use sum here) to contain Scorevalue columns because Scorevalue does not appear in the GROUP BY clause


Concrete implementation: ALTER PROC Usp_getcrossscore4 (
@StuName varchar (10)
)
As
--Creates a temp table to hold the score records
CREATE TABLE #ScoreTbl (
Scoreid int,
Subjectname varchar (50),
Studentname varchar (10),
Scorevalue Real,
Examdate datetime,
Avgflag bit DEFAULT (0)--marks as the average of some subject
)
--Populates basic data of some student
INSERT into #ScoreTbl (Scoreid, Subjectname, Studentname, Scorevalue, Examdate)
SELECT Scoreid, Subjectname, Studentname, Scorevalue, examdate from T_score s WHERE s.studentname = @StuName
--calculates total for each subject and appends to the temp table
INSERT into #ScoreTbl (Scoreid, Subjectname, Studentname, Scorevalue, Examdate, Avgflag)
SELECT NULL, Subjectname, Studentname, AVG (Scorevalue), NULL, 1 from #ScoreTbl s GROUP by Subjectname, Stud Entname
--SELECT * from #ScoreTbl

DECLARE @Sql varchar (8000)
--Note:some known bug-> you to sure the length of the dynamical SQL is less than 8000.
--Fortunately, Mr Zou have made a deep discussion againt it at http://blog.csdn.net/zjcxc/archive/2003/12/29/20075.as Px.
SET @Sql = ' SELECT subjectname account '
SELECT   @sql   =   @sql   +   ',  sum (case examdate when  '   + &nbs P CONVERT (varchar (), examdate,  102)