February 28, 2007 at 11:15 am
Hey all,
I need help trying to create a "pivot table" of sorts.
I have a table named Players. Looks like this:
| PlayersID | Description |
| 1 | Team |
| 2 | FirstName |
| 3 | LastName |
| 4 | Position |
| 5 | Hand |
I have been able to take that table and dynamically create a table named PlayersPivot. It looks like this:
| Team | FirstName | LastName | Position | Hand |
I have another table named Information. Looks Like this:
| InformationID | PlayersID | MappingID | Description |
| 1 | 1 | 1 | BlueJays |
| 2 | 2 | 1 | Frank |
| 3 | 3 | 1 | Thomas |
| 4 | 4 | 1 | DH |
| 5 | 5 | 1 | R |
| 6 | 1 | 2 | BlueJays |
| 7 | 2 | 2 | Roy |
| 8 | 3 | 2 | Halliday |
| 9 | 4 | 2 | Pitcher |
| 10 | 5 | 2 | R |
| 11 | 1 | 3 | BlueJays |
| 12 | 2 | 3 | A.J. |
| 13 | 3 | 3 | Burnett |
| 14 | 4 | 3 | Pitcher |
| 15 | 5 | 3 | R |
Here is my dilema. Each distinct MappingID forms one "row" that I need to insert into the PlayersPivot table. How can I "pivot" the data in Description field, by MappingID, into the appropriate columns. The PlayersID corresponds to the PlayersID in the Players table.
Any help would be greatly appreciated.
Thanks in advance,
Steve
March 1, 2007 at 1:29 am
Try the following link...you may have to search for follow-up articles as there were some revisions/bug fixes to this code.
http://www.sqlteam.com/item.asp?ItemID=2955
March 1, 2007 at 9:35 am
Thanks Andrew for the link, however, after a few coffees, and some quiet, I was able to do it with a nested cursor. I know, avoid at all costs, but in this case I couldnt figure how else to do it.
Thanks,
Steve
March 1, 2007 at 10:03 am
If the Players table does not change much, you may want to hard code like:
SELECT MAX(CASE PlayersID WHEN 1 THEN [Description] END) AS Team
,MAX(CASE PlayersID WHEN 2 THEN [Description] END) AS FirstName
,MAX(CASE PlayersID WHEN 3 THEN [Description] END) AS LastName
,MAX(CASE PlayersID WHEN 4 THEN [Description] END) AS Position
,MAX(CASE PlayersID WHEN 5 THEN [Description] END) AS Hand
FROM Information
GROUP BY MappingID
March 1, 2007 at 12:43 pm
That was attempt number one actually, util I was told that each "Teams" data may be different. Once I tried a different team, no dice.
The dynamic sql and cursor did solve the problem...now its battling the run time next.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply