Move multiple rows into multiple columns

Question

Post reply

Move multiple rows into multiple columns

Wayne Hollier

SSC Veteran

Points: 204
More actions
July 9, 2008 at 6:23 am

#71670

Please if anyone could it would be mostly appreciated. I have a situation where the client answers a questionnaire consisting of a CategoryA and CategoryB answer for a single question. The sample extract would look as follows:
Question ID EmpID ReportsToID CategoryA CategoryB
1 1001 2001 4 2
2 1001 2001 4 2
1 1002 2002 3 1
2 1002 2002 3 1
The result set required is as follows:
EmpID ReportsToID Q1CatA Q2CatA Q1CatB Q2CatB
1001 2001 4 4 2 2
1002 2002 3 3 1 1
We have placed the extract within a cursor using temp tables but the stored proc runs for about 30 minutes due to the size of the extract.
This is a SQL 2000 db.
Thanks

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004686 More actions · Answer 1

This looks like a classic "Cross Tab Report" and the method for creating one (it's pretty simple) is in Books Online...

That not withstanding, how different questions and categories do you have?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Wayne Hollier SSC Veteran Points: 204 More actions · Answer 2

There are 64 questions, each question may contain only 2 answers (i.e. CategoryA = 64 and CategoryB = 64), as I showed in the small example. Maybe I am getting confused with the summing portion of the cross tab report. All I want is that all the answers for CategoryA are moved from rows into columns followed by CategoryB.

Here is my data extract:

EmpID ID CatA CatB

934423021344

The result must be that all CategoryA is displayed in a column followed by CategoryB in the same row (i.e. you would end up with a table consisting of 130 columns). Although not included in the data extract the questionID's differ for each row.

Hope this makes sense.

Thanks again

Jeff Moden SSC Guru Points: 1004686 More actions · Answer 3

Heh... Nope! Doesn't make a bit of sense... all the rows you posted are identical and there is no question column.

Please see the link in my signature before you try again... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Wayne Hollier SSC Veteran Points: 204 More actions · Answer 4

Hey Jeff

Thanks for the feedback, we took your advice and used the books online about the cross and it worked like a charm, it is however hardcoded but I will tweak it to run dynamically. It is very difficlut to try and explain, but thanks again for the heads up.

Cheers

Wayne

Wayne Hollier SSC Veteran Points: 204 More actions · Answer 5

Sorry I forgot to add the questionID to the extract. Here is a sample of the proc that I wrote:

SELECT ISNULL(CQN.AssesseeEmployeeNo, 0) AS AssesseeEmployeeNo, ISNULL(CQN.AssessorEmployeeNo, 0) AS AssessorEmployeeNo,

ISNULL(Common..PERSON.REPORTS_TO_SEQ_NR, 0) AS REPORTS_TO_SEQ_NR,

SUM(CASE QuestionID WHEN '10CD91DA-C034-4D84-9777-38C95D2489A4' THEN CategoryA ELSE 0 END) AS QC1,

SUM(CASE QuestionID WHEN '10CD91DA-C034-4D84-9777-38C95D2489A4' THEN CategoryB ELSE 0 END) AS QI1

FROM CompletedQuestionnaire CQN INNER JOIN

CompletedQuestion CQ ON CQN.ID = CQ.CompletedQuestionnaireID INNER JOIN

Question Q ON CQ.QuestionID = Q.ID LEFT JOIN

Common..Person ON CQN.AssesseeEmployeeNo = Common..Person.EMP_NR

WHERE Q.ParentID IS NOT NULL

AND CQN.QuestionnaireID = '5c97e803-0950-407a-a373-9b2e2e3ce9eb'

ANDCQN.Status = 'CPL'

Group by CQN.AssesseeEmployeeNo, CQN.AssessorEmployeeNo, Common..Person.REPORTS_TO_SEQ_NR

Jeff Moden SSC Guru Points: 1004686 More actions · Answer 6

Looks ok to me... don't forget... you can use MAX in place of SUM for things that aren't numeric in nature provided they're unique.

And, thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)