Hey,I have been asked to produce a report from our database. I'm really not sure how to do it and would like some advice on how to approach it.Below I have included some code to build example data. As you can see we've many scorecards, each scorecard has many scores.I would like to cross tab the data, but instead of a calculation I would like it to output the answer. So it would look like thisscorecard id | weather | day | food | colour----------------------------------------------------------------------100 | sunny | sunday | ice cream | purple200 | raining | monday | choclate | redetc....
The person requesting the report is going to pick a set of scorecards, thus the possible questions are never static.DECLARE @Scorecards TABLE (ScorecardID Int)DECLARE @Score TABLE (ScoreID int, ScorecardID int, Question nvarchar(50), Answer nvarchar(50))INSERT INTO @Scorecards VALUES (100)INSERT INTO @Scorecards VALUES (200)INSERT INTO @Scorecards VALUES (300)INSERT INTO @Scorecards VALUES (400)INSERT INTO @Score VALUES (1, 100, 'Weather', 'Sunny')INSERT INTO @Score VALUES (2, 100, 'Day', 'Sunday')INSERT INTO @Score VALUES (3, 100, 'Food', 'Icecream')INSERT INTO @Score VALUES (4, 100, 'Colour', 'Purple')INSERT INTO @Score VALUES (5, 200, 'Weather', 'Raining')INSERT INTO @Score VALUES (6, 200, 'Day', 'Monday')INSERT INTO @Score VALUES (7, 200, 'Food', 'Chocolate')INSERT INTO @Score VALUES (8, 200, 'Colour', 'Red')INSERT INTO @Score VALUES (9, 300, 'Weather', 'Snowing')INSERT INTO @Score VALUES (10, 300, 'Day', 'Friday')INSERT INTO @Score VALUES (11, 300, 'Food', 'Apples')INSERT INTO @Score VALUES (12, 300, 'Colour', 'Green')INSERT INTO @Score VALUES (13, 400, 'Weather', 'Windy')INSERT INTO @Score VALUES (14, 400, 'Day', 'Wednesday')INSERT INTO @Score VALUES (15, 400, 'Food', 'Bananas')INSERT INTO @Score VALUES (16, 400, 'Colour', 'Black')SELECT *FROM @Scorecards Scorecard INNER JOIN @Score Score ON Score.ScorecardID = Scorecard.ScorecardID
Crosstab without Calculation
Posted - 2011-03-30 : 11:44:27
