Producing a cross-tab report with static number of columns
is a straightforward process. When comes to the dynamic number of columns, it
gets tricky. This article illustrates how to generate a cross-tab query on the
fly, using the EXEC statement and simple string concatenation.
Let us assume a typical objective type Exam paper. Let us
create the table, QUESTION_ANSWER, that holds the questions and the
corresponding answers as shown below.
if exists
(select * from dbo.sysobjects where id = object_id(N’[QUESTION_ANSWER]’)
and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [QUESTION_ANSWER]
GO
CREATE TABLE QUESTION_ANSWER (QUESTIONID INT,
QUESTION VARCHAR(100), ANSWER CHAR(1))
GO
INSERT INTO QUESTION_ANSWER SELECT 1,’QUESTION1′,’A’
INSERT INTO QUESTION_ANSWER SELECT 2,’QUESTION2′,’C’
INSERT INTO QUESTION_ANSWER SELECT 3,’QUESTION3′,’B’
INSERT INTO QUESTION_ANSWER SELECT 4,’QUESTION4′,’B’
INSERT INTO QUESTION_ANSWER SELECT 5,’QUESTION5′,’B’
INSERT INTO QUESTION_ANSWER SELECT 6,’QUESTION6′,’A’
INSERT INTO QUESTION_ANSWER SELECT 7,’QUESTION7′,’A’
INSERT INTO QUESTION_ANSWER SELECT 8,’QUESTION8′,’H’
INSERT INTO QUESTION_ANSWER SELECT 9,’QUESTION9′,’J’
INSERT INTO QUESTION_ANSWER SELECT 10,’QUESTION10′,’I’
GO
Let us
query the table by executing the following SQL Statement.
SELECT * FROM QUESTION_ANSWER
The
result would look like the image shown below. Refer Fig 1.0
Fig 1.0
Let us create the table, ANSWERSHEET, that holds the
answers from the candidates John, Aylin and Jaime. Execute the following SQL
Statement to create the table and generate data.
if exists
(select * from dbo.sysobjects where id = object_id(N’[ANSWERSHEET]’)
and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [ANSWERSHEET]
GO
CREATE TABLE ANSWERSHEET (QUESTIONID INT,
ANSWER CHAR(1), USERID VARCHAR(10))
GO
INSERT INTO ANSWERSHEET SELECT 1,’A’,’John’
INSERT INTO ANSWERSHEET SELECT 2,’C’,’John’
INSERT INTO ANSWERSHEET SELECT 3,’B’,’John’
INSERT INTO ANSWERSHEET SELECT 4,’A’,’John’
INSERT INTO ANSWERSHEET SELECT 5,’B’,’John’
INSERT INTO ANSWERSHEET SELECT 6,’A’,’John’
INSERT INTO ANSWERSHEET SELECT 7,’A’,’John’
INSERT INTO ANSWERSHEET SELECT 8,’B’,’John’
INSERT INTO ANSWERSHEET SELECT 9,’B’,’John’
INSERT INTO ANSWERSHEET SELECT 10,’B’,’John’
INSERT INTO ANSWERSHEET SELECT 1,’A’,’Jaime’
INSERT INTO ANSWERSHEET SELECT 2,’C’,’Jaime’
INSERT INTO ANSWERSHEET SELECT 3,’B’,’Jaime’
INSERT INTO ANSWERSHEET SELECT 4,’B’,’Jaime’
INSERT INTO ANSWERSHEET SELECT 5,’B’,’Jaime’
INSERT INTO ANSWERSHEET SELECT 6,’A’,’Jaime’
INSERT INTO ANSWERSHEET SELECT 7,’C’,’Jaime’
INSERT INTO ANSWERSHEET SELECT 8,’C’,’Jaime’
INSERT INTO ANSWERSHEET SELECT 9,’C’,’Jaime’
INSERT INTO ANSWERSHEET SELECT 10,’B’,’Jaime’
INSERT INTO ANSWERSHEET SELECT 1,’C’,’Aylin’
INSERT INTO ANSWERSHEET SELECT 2,NULL,’Aylin’
INSERT INTO ANSWERSHEET SELECT 3,’B’,’Aylin’
INSERT INTO ANSWERSHEET SELECT 4,’A’,’Aylin’
INSERT INTO ANSWERSHEET SELECT 5,’C’,’Aylin’
INSERT INTO ANSWERSHEET SELECT 6,’C’,’Aylin’
INSERT INTO ANSWERSHEET SELECT 7,’A’,’Aylin’
INSERT INTO ANSWERSHEET SELECT 8,’B’,’Aylin’
INSERT INTO ANSWERSHEET SELECT 9,’C’,’Aylin’
INSERT INTO ANSWERSHEET SELECT 10,NULL,’Aylin’
GO
Let us
query the table by executing the following SQL Statement.
SELECT * FROM ANSWERSHEET
The
result would look like the imag shown below. Refer Fig 1.1
Fig 1.1
Let us
create a query that would generate the cross-tab report. Execute the following
SQL Statement.
SELECT LOGINID, SUM(SCORE) AS SCORE,
MAX(CASE QUESTID WHEN 1 THEN QANSWER ELSE ” END) AS [QUESTION1-A],
MAX(CASE QUESTID WHEN 2 THEN QANSWER ELSE ” END) AS [QUESTION2-C],
MAX(CASE QUESTID WHEN 3 THEN QANSWER ELSE ” END) AS [QUESTION3-B],
MAX(CASE QUESTID WHEN 4 THEN QANSWER ELSE ” END) AS [QUESTION4-B],
MAX(CASE QUESTID WHEN 5 THEN QANSWER ELSE ” END) AS [QUESTION5-B],
MAX(CASE QUESTID WHEN 6 THEN QANSWER ELSE ” END) AS [QUESTION6-A],
MAX(CASE QUESTID WHEN 7 THEN QANSWER ELSE ” END) AS [QUESTION7-A],
MAX(CASE QUESTID WHEN 8 THEN QANSWER ELSE ” END) AS [QUESTION8-H],
MAX(CASE QUESTID WHEN 9 THEN QANSWER ELSE ” END) AS [QUESTION9-J],
MAX(CASE QUESTID WHEN 10 THEN QANSWER ELSE ” END) AS [QUESTION10-I]
FROM (SELECT A.USERID AS LOGINID, A.QUESTIONID AS QUESTID,A.ANSWER AS QANSWER,
B.ANSWER AS REALANSWER,SCORE = CASE WHEN A.ANSWER=B.ANSWER
THEN 1 ELSE ” END FROM ANSWERSHEET A LEFT JOIN
QUESTION_ANSWER B ON A.QUESTIONID=B.QUESTIONID) AS MYTABLE GROUP BY LOGINID
The
result of this query would look like the image shown below Refer Fig 1.2
Fig 1.2
If I add new questions in the QUESTION_ANSWER table and
their corresponding answers by the candidates in ANSWERSHEET table, the above
cross-tab will not show those results.
Let us create a stored procedure that would generate the
SQL Statement, which could produce the cross-tab report.
Execute
the statement as shown below.
if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[crosstab]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[crosstab]
GO
CREATE PROCEDURE CROSSTAB
AS
DECLARE @SQL1 VARCHAR(8000)
DECLARE @VAR1 INT
DECLARE @VAR2 INT
DECLARE @VAR3 VARCHAR(100)
SET @VAR1=(SELECT MIN(QUESTIONID) FROM QUESTION_ANSWER)
SET @VAR2=(SELECT MAX(QUESTIONID) FROM QUESTION_ANSWER)
SET @SQL1=’SELECT LOGINID, SUM(SCORE) AS SCORE,’
WHILE @VAR1<=@VAR2
BEGIN
SET @VAR3=(SELECT QUESTION +’-‘ + ANSWER FROM QUESTION_ANSWER WHERE QUESTIONID=@VAR1)
SET @SQL1=@SQL1 +’ MAX(CASE QUESTID WHEN ‘+CONVERT(VARCHAR(10),@VAR1)+’ THEN QANSWER ELSE ”” END) AS [’+CONVERT(VARCHAR(100),@VAR3)+’],’
SET @VAR1=@VAR1+1
END
SET @SQL1=LEFT(@SQL1,LEN(@SQL1)-1)
SET @SQL1=@SQL1+’
FROM (SELECT A.USERID AS LOGINID,
A.QUESTIONID AS QUESTID,
A.ANSWER AS QANSWER,
B.ANSWER AS REALANSWER,
SCORE = CASE WHEN A.ANSWER=B.ANSWER
THEN 1 ELSE ”” END
FROM ANSWERSHEET A LEFT JOIN QUESTION_ANSWER B
ON A.QUESTIONID=B.QUESTIONID) AS MYTABLE GROUP BY LOGINID’
PRINT @SQL1
EXEC (@SQL1)
Go
Now, let
us execute the procedure as shown.
Exec Crosstab
Go
This
produces two results. One is the actual SQL Statement and the other is the
result. Refer Fig 1.3.
SQL
Statement
SELECT LOGINID, SUM(SCORE) AS SCORE,
MAX(CASE QUESTID WHEN 1 THEN QANSWER ELSE ” END) AS [QUESTION1-A],
MAX(CASE QUESTID WHEN 2 THEN QANSWER ELSE ” END) AS [QUESTION2-C],
MAX(CASE QUESTID WHEN 3 THEN QANSWER ELSE ” END) AS [QUESTION3-B],
MAX(CASE QUESTID WHEN 4 THEN QANSWER ELSE ” END) AS [QUESTION4-B],
MAX(CASE QUESTID WHEN 5 THEN QANSWER ELSE ” END) AS [QUESTION5-B],
MAX(CASE QUESTID WHEN 6 THEN QANSWER ELSE ” END) AS [QUESTION6-A],
MAX(CASE QUESTID WHEN 7 THEN QANSWER ELSE ” END) AS [QUESTION7-A],
MAX(CASE QUESTID WHEN 8 THEN QANSWER ELSE ” END) AS [QUESTION8-H],
MAX(CASE QUESTID WHEN 9 THEN QANSWER ELSE ” END) AS [QUESTION9-J],
MAX(CASE QUESTID WHEN 10 THEN QANSWER ELSE ” END) AS [QUESTION10-I]
FROM (SELECT A.USERID AS LOGINID, A.QUESTIONID AS QUESTID,A.ANSWER AS QANSWER,
B.ANSWER AS REALANSWER,SCORE = CASE WHEN A.ANSWER=B.ANSWER
THEN 1 ELSE ” END FROM ANSWERSHEET A LEFT JOIN
QUESTION_ANSWER B ON A.QUESTIONID=B.QUESTIONID) AS MYTABLE GROUP BY LOGINID
Result
Fig 1.3
Now let
us add some new answers to the QUESTION_ANSWER table.
INSERT INTO QUESTION_ANSWER SELECT 11,’QUESTION11′,’A’
INSERT INTO QUESTION_ANSWER SELECT 12,’QUESTION12′,’C’
Now let
us add some answers provided by the candidates to the ANSWERSHEET table.
INSERT INTO ANSWERSHEET SELECT 11,’A’,’John’
INSERT INTO ANSWERSHEET SELECT 12,’A’,’John’
INSERT INTO ANSWERSHEET SELECT 11,’A’,’Jaime’
Now, let
us execute the procedure as shown.
Exec Crosstab
Go
The
following SQL Statement and the results are generated. Refer Fig 1.4.
SELECT LOGINID,
SUM(SCORE) AS SCORE,
MAX(CASE QUESTID WHEN 1 THEN QANSWER ELSE ” END) AS [QUESTION1-A],
MAX(CASE QUESTID WHEN 2 THEN QANSWER ELSE ” END) AS [QUESTION2-C],
MAX(CASE QUESTID WHEN 3 THEN QANSWER ELSE ” END) AS [QUESTION3-B],
MAX(CASE QUESTID WHEN 4 THEN QANSWER ELSE ” END) AS [QUESTION4-B],
MAX(CASE QUESTID WHEN 5 THEN QANSWER ELSE ” END) AS [QUESTION5-B],
MAX(CASE QUESTID WHEN 6 THEN QANSWER ELSE ” END) AS [QUESTION6-A],
MAX(CASE QUESTID WHEN 7 THEN QANSWER ELSE ” END) AS [QUESTION7-A],
MAX(CASE QUESTID WHEN 8 THEN QANSWER ELSE ” END) AS [QUESTION8-H],
MAX(CASE QUESTID WHEN 9 THEN QANSWER ELSE ” END) AS [QUESTION9-J],
MAX(CASE QUESTID WHEN 10 THEN QANSWER ELSE ” END) AS [QUESTION10-I],
MAX(CASE QUESTID WHEN 11 THEN QANSWER ELSE ” END) AS [QUESTION11-A],
MAX(CASE QUESTID WHEN 12 THEN QANSWER ELSE ” END) AS [QUESTION12-C]
FROM
(SELECT A.USERID AS LOGINID, A.QUESTIONID AS QUESTID,A.ANSWER AS QANSWER,B.ANSWER AS REALANSWER,
SCORE = CASE WHEN A.ANSWER=B.ANSWER THEN 1 ELSE ” END FROM ANSWERSHEET A LEFT JOIN QUESTION_ANSWER B ON A.QUESTIONID=B.QUESTIONID)
AS MYTABLE GROUP BY LOGINID
Fig 1.4
You can see
that there are two new columns added to the report automatically and the
corresponding answers by the candidates.
Conclusion
As mentioned in the beginning of the article the objective
is to find the pattern in the SQL Statement that produces the cross-tab report
and then generate the statement by using EXEC statement and simple string
concatenation. Based on the above illustration, we could generate the SQL
statement needed to produce the actual cross-tab report.