How to Manual Transpose, Pivot Columns to Rows.

 

— Create the tables…

Create Table [dbo].[QuestionnaireQuestions](

[QuestionnaireID] [int] Null,

[QuestionID] [int] Null,

[QuestionText] [nvarchar](1000) Null

) On [Primary]

 

Create Table [dbo].[Answers](

[AnswerID] [int] Null,

[QuestionnaireID] [int] Null,

[QuestionID] [int] Null,

[PersonID] [int] Null,

[AnswerText] [nvarchar](1000) Null

) On [Primary]

 

— Populate the tables…

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (1, 1, ‘How old are you?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (1, 2, ‘Where do you live?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (1, 3, ‘What is your name?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (1, 4, ‘What is your favourite colour?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (2, 1, ‘Where do you work?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (2, 2, ‘How long have you worked there?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (2, 3, ‘What is your job?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (2, 4, ‘How many people in your work place?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (3, 1, ‘Where did you study Uni?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (3, 2, ‘What was you specialized study?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (3, 3, ‘How many years did you study in Uni?’)

Insert dbo.QuestionnaireQuestions (QuestionnaireID, QuestionID, QuestionText) Values (3, 4, ‘Did you do a work placement at Uni?’)

 

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (1, 1, 1, 1, ’25’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (2, 1, 2, 1, ‘Madrid’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (3, 1, 3, 1, ‘Sven’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (4, 1, 4, 1, ‘Green’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (5, 2, 1, 1, ‘Worms Farm’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (6, 2, 2, 1, ‘2 months’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (7, 2, 3, 1, ‘Pig farmer’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (8, 2, 4, 1, ’12’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (9, 1, 1, 2, ’31’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (10, 1, 2, 2, ‘Londinium’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (11, 1, 3, 2, ‘Robert’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (12, 1, 4, 2, ‘Blue’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (13, 2, 1, 2, ‘Roman Villas’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (14, 2, 2, 2, ‘8 years’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (15, 2, 3, 2, ‘Visitor Guide’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (16, 2, 4, 2, ‘132’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (17, 3, 1, 2, ‘Newcastle’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (18, 3, 2, 2, ‘Law’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (19, 3, 3, 2, ‘5’)

Insert dbo.Answers (AnswerID, QuestionnaireID, QuestionID, PersonID, AnswerText) Values (20, 3, 4, 2, ‘Yes’)

 

 

Select * From QuestionnaireQuestions Order By QuestionnaireID, QuestionID

/*

— Output:

QuestionnaireID        QuestionID        QuestionText

1                                1                        How old are you?

1                                2                        Where do you live?

1                                3                        What is your name?

1                                4                        What is your favourite colour?

2                                1                        Where do you work?

2                                2                        How long have you worked there?

2                                3                        What is your job?

2                                4                        How many people in your work place?

3                                1                        Where did you study Uni?

3                                2                        What was you specialized study?

3                                3                        How many years did you study in Uni?

3                                4                        Did you do a work placement at Uni?

*/

 

Select * From Answers Order by AnswerID

/*

— Output:

AnswerID        QuestionnaireID        QuestionID        PersonID        AnswerText

1                        1                                1                        1                        25

2                        1                                2                        1                        Madrid

3                        1                                3                        1                        Sven

4                        1                                4                        1                        Green

5                        2                                1                        1                        Worms Farm

6                        2                                2                        1                        2 months

7                        2                                3                        1                        Pig farmer

8                        2                                4                        1                        12

9                        1                                1                        2                        31

10                        1                                2                        2                        Londinium

11                        1                                3                        2                        Robert

12                        1                                4                        2                        Blue

13                        2                                1                        2                        Roman Villas

14                        2                                2                        2                        8 years

15                        2                                3                        2                        Visitor Guide

16                        2                                4                        2                        132

17                        3                                1                        2                        Newcastle

18                        3                                2                        2                        Law

19                        3                                3                        2                        5

20                        3                                4                        2                        Yes

*/

 

Select

*

From

QuestionnaireQuestions QQ

Inner Join Answers A On QQ.QuestionnaireID = A.QuestionnaireID And QQ.QuestionID = A.QuestionID

Where

QQ.QuestionnaireID = 1

/*

— Output:

QuestionnaireID        QuestionID        QuestionText                                        AnswerID        QuestionnaireID        QuestionID        PersonID        AnswerText

1                                1                        How old are you?                                1                        1                                1                        1                        25

1                                1                        How old are you?                                9                        1                                1                        2                        31

1                                2                        Where do you live?                                2                        1                                2                        1                        Madrid

1                                2                        Where do you live?                                10                        1                                2                        2                        Londinium

1                                3                        What is your name?                                3                        1                                3                        1                        Sven

1                                3                        What is your name?                                11                        1                                3                        2                        Robert

1                                4                        What is your favourite colour?        4                        1                                4                        1                        Green

1                                4                        What is your favourite colour?        12                        1                                4                        2                        Blue

*/

 

— This is the Manual Transposing part..

Select

Q1.Question1

,Q1.Answer1

,Q2.Question2

,Q2.Answer2

,Q3.Question3

,Q3.Answer3

,Q4.Question4

,Q4.Answer4

From

(

Select

QuestionText As Question1

,AnswerText As Answer1

,PersonID

From

QuestionnaireQuestions QQ

Inner Join Answers A On QQ.QuestionnaireID = A.QuestionnaireID And QQ.QuestionID = A.QuestionID

Where

QQ.QuestionnaireID = 1

And QQ.QuestionID = 1

) As Q1

 

Inner Join

(

Select

QuestionText As Question2

,AnswerText As Answer2

,PersonID

From

QuestionnaireQuestions QQ

Inner Join Answers A On QQ.QuestionnaireID = A.QuestionnaireID And QQ.QuestionID = A.QuestionID

Where

QQ.QuestionnaireID = 1

And QQ.QuestionID = 2

) As Q2

On Q1.PersonID = Q2.PersonID

 

Inner Join

(

Select

QuestionText As Question3

,AnswerText As Answer3

,PersonID

From

QuestionnaireQuestions QQ

Inner Join Answers A On QQ.QuestionnaireID = A.QuestionnaireID And QQ.QuestionID = A.QuestionID

Where

QQ.QuestionnaireID = 1

And QQ.QuestionID = 3

) As Q3

On Q1.PersonID = Q3.PersonID

 

Inner Join

(

Select

QuestionText As Question4

,AnswerText As Answer4

,PersonID

From

QuestionnaireQuestions QQ

Inner Join Answers A On QQ.QuestionnaireID = A.QuestionnaireID And QQ.QuestionID = A.QuestionID

Where

QQ.QuestionnaireID = 1

And QQ.QuestionID = 4

) As Q4

On Q1.PersonID = Q4.PersonID

/*

— Output:

Question1                        Answer1        Question2                        Answer2                Question3                        Answer3        Question4                                                Answer4

How old are you?        25                Where do you live?        Madrid                What is your name?        Sven        What is your favourite colour?        Green

How old are you?        31                Where do you live?        Londinium        What is your name?        Robert        What is your favourite colour?        Blue                                                                                                                                                                                                                                                31                                                                                                                                                                                                                                                               Where do you live?                                                                                                                                                                                                                                               Londinium                                                                                                                                                                                                                                                        What is your name?                                                                                                                                                                                                                                               Robert    What is your favourite colour?    Blue

*/

Advertisements