Flattening A Dataset - Crosstab/pivot

Hello,

I have a dataset that looks like this:

event_id answer_id answer 10001 20500 Red 10001 20501 Yes 10001 20502 No

I want to be able to turn this into a single row, so that my report output looks as follows:

event_id answer_20500 answer_20501 answer_20502 10001 Red Yes No

10002 Blue Maybe No

Can SSRS do this kind of transformation? For reasons not worth going into here, I'd rather not do this in SQL (although I CAN if I had to).

Thanks,

Michael

Hello Michael,

Yes, SSRS can do this for you. You'll need to use a matrix in your report with the error_id as your Row, answer_id as your Column, and answer as your Details.

With the sample data you gave, your report will look like this:

20500 20501 20502 10001 Red Yes No 10002 Blue Maybe No

Hope this helps.

Jarret

|||

That is most helpful! Thanks. I'll try that out and see how it goes. I'm still learning SSRS, and have a long way to go.

Thanks again,

Michael

|||

Jarret,

For each details column, I assume I will have to add a filter to bring back only the answer for the row with the specific question id? Or is there another way to accomplish that?

Michael

|||

Michael,

You won't have to do anything extra with the matrix report, it will handle that for you. The answer that is listed will be the answer that is associated with that answer_id and event_id.

Jarret

|||Hi Micheal and Jarrent,

Sorry to jump on your bandwagoon Micheal but I'm trying to do the same thing but in sql as I want to use it on a Gridwiew in ASP.Net web form.

Previously in MS Access, this is called a Crosstab query which I could do very easily as there is a wizard for it. The sql statement behind a completed crosstub query looks like this:

TRANSFORM Sum(ProfileData.Amount) AS SumOfAmount
SELECT ProfileData.[Project Number], Sum(ProfileData.Amount) AS [Total Of Amount]
FROM ProfileData
GROUP BY ProfileData.[Project Number]
PIVOT Format([ProfileDate],"mmm" & "-" & "yyyy");

Without having to list each date value in ProfileDate, this code dynamically puts every ProfileDate value as a colum, therefore I don't need to adjust the query every time a new ProfileDate value is inserted. Basically I'm having problems doing the same thing in sql server 2005. I know I'm new to it but I'm thinking there must be an easy way to do this.

Can you help please?

thank you

Aku