• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

Query or SPROC to Transpose

I am using SQL Server 2005 and I have records as follows:

Person     Section     Question     Raw_Score
  A               1                1                  8
  A               1                2                  2
  A               1                3                  4
  A               1                4                  5
  A               1                5                  2
  A               2                1                  4
  A               2                2                  3
  A               2                3                  6
  A               3                1                  2
  A               3                2                  9
  A               3                3                  5
  A               3                4                  6
  A               4                1                  9
  A               4                2                  2
  A               5                1                  5
  A               5                2                  5
  A               5                3                  4
  A               6                1                  1
  A               6                2                  1
  A               6                3                  4
  A               6                4                  3

I need to produce a result like:
Person   S1Q1   S1Q2   S1Q3   S1Q4   S1Q5   S2Q1   S2Q2   S2Q3   S3Q1   S3Q2 etc.
A               8         2           4         5         2          4         3          6          2          9    etc.

I will never have more than 6 Sections, but I could have an unlimited number of Questions per Section.  This structure will be used over and over for different situations, and within each situation, the number of questions in each section will always be the same.

For example, in situation 1, there might be 500 Persons, with 6 sections: 5 questions in section 1, 3 questions in section 2, 4 questions in section 3, etc.

In situation 2, I will have the same structure, but this time, there might be 1,000 Persons, with 6 sections: section 1 will have 20 question, section 2 will have 10 questions, section 3 will 15 questions, etc.

My code/queries need to be able to accommodate all situations without having to customize.

Is there a SQL function that will allow me to do this?  Can I use some sort of Query (View)?  Or is this a job for a SPROC?

I suspect the answer is a SPROC, but need to ask first.
0
wsturdev
Asked:
wsturdev
  • 6
1 Solution
 
SharathData EngineerCommented:
So the number of columns are not fixed. right?
0
 
wsturdevAuthor Commented:
The number of columns in the original data is fixed, but the number of columns I will potentially have in the output is not fixed.
0
 
SharathData EngineerCommented:
hmmm....this is difficult.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SharathData EngineerCommented:
Author or Moderator - give me some time. ll look into this.
0
 
Daniel WilsonCommented:
A view will not handle this.  You need a stored procedure that will count the number of sections & questions, then create dynamic SQL for the result.

Want the code for that?
0
 
SharathData EngineerCommented:

-- Assume that your table name is A. The data from your table is
SELECT * FROM A
/*
Person	Section	Question	Raw_Score
A	1	1	8
A	1	2	2
A	1	3	4
A	1	4	5
A	1	5	2
A	2	1	4
A	2	2	3
A	2	3	6
A	3	1	2
A	3	2	9
A	3	3	5
A	3	4	6
A	4	1	9
A	4	2	2
A	5	1	5
A	5	2	5
A	5	3	4
A	6	1	1
A	6	2	1
A	6	3	4
A	6	4	3
*/
 
-- Here is the solution.
 
 SELECT 'S'+CONVERT(nvarchar,Section)+'Q'+CONVERT(nvarchar,Question) AS col INTO #Temp FROM A
DECLARE @Select nvarchar(max)
DECLARE @col nvarchar(max)
    SET @col = ''
    SET @Select = 'SELECT'
  WHILE @col IS NOT NULL
  BEGIN
    SET @col = (
 SELECT MIN(col) FROM #Temp WHERE col > @col)
     IF @col IS NOT NULL 
    SET @Select = (SELECT @Select + '(SELECT Raw_Score FROM A WHERE Section = ' + SUBSTRING(col,2,1) + 'AND Question = ' + SUBSTRING(col,4,1) + ')AS ' + col + ',' 
                    FROM #Temp WHERE col = @col)
    END
  
    SET @Select = SUBSTRING(@Select,1,LEN(@Select)-1)
   EXEC(@Select)
   DROP TABLE #Temp 
/*
S1Q1	S1Q2	S1Q3	S1Q4	S1Q5	S2Q1	S2Q2	S2Q3	S3Q1	S3Q2	S3Q3	S3Q4	S4Q1	S4Q2	S5Q1	S5Q2	S5Q3	S6Q1	S6Q2	S6Q3	S6Q4
8	2	4	5	2	4	3	6	2	9	5	6	9	2	5	5	4	1	1	4	3
*/

Open in new window

0
 
SharathData EngineerCommented:

--  You can create a SP like this.
CREATE PROCEDURE dbo.sp_Report AS
 SELECT 'S'+CONVERT(nvarchar,Section)+'Q'+CONVERT(nvarchar,Question) AS col INTO #Temp FROM A
DECLARE @Select nvarchar(max)
DECLARE @col nvarchar(max)
    SET @col = ''
    SET @Select = 'SELECT'
  WHILE @col IS NOT NULL
  BEGIN
    SET @col = (
 SELECT MIN(col) FROM #Temp WHERE col > @col)
     IF @col IS NOT NULL 
    SET @Select = (SELECT @Select + '(SELECT Raw_Score FROM A WHERE Section = ' + SUBSTRING(col,2,1) + 'AND Question = ' + SUBSTRING(col,4,1) + ')AS ' + col + ',' 
                    FROM #Temp WHERE col = @col)
    END
  
    SET @Select = SUBSTRING(@Select,1,LEN(@Select)-1)
   EXEC(@Select)
   DROP TABLE #Temp 
----------------------------------------------
 
-- Execute the SP
 EXEC DBO.SP_REPORT

Open in new window

0
 
SharathData EngineerCommented:
solution was given to this question
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now