Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 6, 2007

SQLServer: Dynamic Cross-Tab

By Muthusamy Anantha Kumar aka The MAK

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.

» See All Articles by Columnist MAK



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date