July 9, 2009 at 3:39 pm
I have a table that manages the relationship between overlapping geographical boundaries. The table structure looks like this:
RJ_Id | RegionId | JurisId
1 | 1 | 20
2 | 1 | 21
3 | 2 | 20
4 | 2 | 21
5 | 2 | 91
6 | 3 | 21
7 | 3 | 22
8 | 3 | 23
What I need to do is come up with a result set from the table that looks like this:
RegionId | Juris1 | Juris2 | Juris3
1 | 20 | 21 | NULL
2 | 20 | 21 | 91
3 | 21 | 22 | 23
I know this is possible, it is, after all, just a problem concerning sets, and T-SQL is generally very good about dealing with sets, but I am coming up empty.
Any ideas?
July 10, 2009 at 12:42 am
Yes. Use the PIVOT operator.
N 56°04'39.16"
E 12°55'05.25"
July 10, 2009 at 6:55 am
Dan Wood (7/9/2009)
I have a table that manages the relationship between overlapping geographical boundaries. The table structure looks like this:RJ_Id | RegionId | JurisId
1 | 1 | 20
2 | 1 | 21
3 | 2 | 20
4 | 2 | 21
5 | 2 | 91
6 | 3 | 21
7 | 3 | 22
8 | 3 | 23
What I need to do is come up with a result set from the table that looks like this:
RegionId | Juris1 | Juris2 | Juris3
1 | 20 | 21 | NULL
2 | 20 | 21 | 91
3 | 21 | 22 | 23
I know this is possible, it is, after all, just a problem concerning sets, and T-SQL is generally very good about dealing with sets, but I am coming up empty.
Any ideas?
Your juris1, 2, and 3 values are coming from where? I mean, it seems like you're just saying "the first of the values will be juris1, the second will be juris2, the third will be juris3" etc...
But there's no strict designation or ordering, so how do you decide what to assign to which jurisdiction?
With the pivot operator, you can take the values which exist in JurisID, and turn those into columns, but that doesn't seem to be what you're doing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply