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.
LVL 1
wsturdevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
solution was given to this question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.