August 6, 2013 at 7:06 am
I have a table structure as follows.
id Field1 Field2
-- ------ ------
4 A A
4 S S
5 A A
5 R R
6 A A
6 X X
Now what I need is
id Field1 Field2 Field3 Field4
-- ------ ----- ------ ------
4 A A S S
5 A A R R
6 A A X X
The Pivot and Crosstab are generally talking about pivoting on one column into row.
Any help for the above? Thanks in advance
August 6, 2013 at 8:06 am
If you only have 2 rows, then it can be easy to do it with MAX and MIN for your columns.
SELECT id,
MIN( Field1) Field1,
MIN( Field2) Field2,
MAX( Field1) Field3,
MAX( Field2) Field4
FROM MyTable
GROUP BY id
If a different solution is needed, you need to provide more details.
August 6, 2013 at 11:14 pm
Thanks a lot.
My requirement is a set of records (any number of rows) would be given, and those records as a whole group must be tested against a table for their existance.
For Example,
F1 F2
--- ---
A A
Z Z
is given, then the look up table would have a list of records where exactly two records group like
id F3 F4
-- --- ---
4 A A
4 S S
5 A A
5 R R
6 A A
6 X Y
Now nowhere the whole group of A A and Z Z pair is available in the look up table. To do this comparision am trying to make the look up table values and given record set into rows. Is there any other better way to do this?
Moreover in your reply, it works fine for two set of records. Could you please let me know that what would be needed if the number of records gets increased?
August 7, 2013 at 8:12 am
I've dealt with this kind of thing a few different ways.
The first thing to remember is that hierarchical data represents a relationship. In your case, there is a one-to-many relationship between id and F1. Now, there are two possible things you can know about that relationship: How many potential values we can have for F1, and the exact potential values for F1 for a given id. Well-ordered data will have the latter, but if you have the former it's probably not terribly feasible to change the architecture to handle it better, so we'll do what we have to.
Let's say that you know that a given id can have up to 4 unique F1 values. If we don't know what those values will be, just that there can be up to 4 of them, then you can make use of a window function to help you out:
select RankingCTE as (
select id
, RANK() OVER (PARTITION BY id, ORDER BY F1) as Ranking
, F1
, F2
from MyTable )
select F1_1.id
, F1_1.F1 as 1_F1
, F1_1.F2 as 1_F2
, F1_2.F1 as 2_F1
, F1_2.F2 as 2_F2
, F1_3.F1 as 3_F1
, F1_3.F2 as 3_F2
, F1_4.F1 as 4_F2
, F1_4.F2 as 4_F2
from RankingCTE F1_1
left join RankingCTE F1_2
on F1_1.id = F1_2.id
and F1_2.Ranking = 2
left join RankingCTE F1_3
on F1_1.id = F1_3.id
and F1_3.Ranking = 3
left join RankingCTE F1_4
on F1_1.id = F1_4.id
and F1_4.Ranking = 4
where F1_1.Ranking = 1
This is the simplest approach I can come up with.
Now, if we know exactly which values we can have in F1, then we've got a little bit of flexibility here. The simplest approach here would be similar to the above, except we don't have to do our own ranking. Let's say you can have F1 values of 'A', 'B', and 'C':
with KeyExtractCTE as (
select distinct id
from MyTable
)
select Keys.id
, F1_A.F1 as F1_A
, F1_A.F2 as F2_A
, F1_B.F1 as F1_B
, F1_B.F2 as F2_B
, F1_C.F1 as F1_C
, F1_C.F2 as F2_C
from KeyExtractCTE Keys
left join MyTable F1_A
on Keys.id = F1_A.id
and F1_A.F1 = 'A'
left join MyTable F1_B
on Keys.id = F1_B.id
and F1_B.F1 = 'B'
left join MyTable F1_C
on Keys.id = F1_C.id
and F1_C.F1 = 'C'
The difficulty with this one is that if you're dealing with around ten or more possible values, performance really starts to suffer with this approach. However, if you make use of a physical table you can do pretty well (again, values of 'A','B','C'):
insert into MyTargetTable
select distinct id
from MyTable;
update TGT
set F1_A = SRC.F1
, F2_A = SRC.F2
from MyTargetTable TGT
inner join MyTable SRC
on TGT.id = SRC.id
and TGT.F1 = 'A';
update TGT
set F1_B = SRC.F1
, F2_B = SRC.F2
from MyTargetTable TGT
inner join MyTable SRC
on TGT.id = SRC.id
and TGT.F1 = 'B';
update TGT
set F1_C = SRC.F1
, F2_C = SRC.F2
from MyTargetTable TGT
inner join MyTable SRC
on TGT.id = SRC.id
and TGT.F1 = 'C';
This approach scales pretty well when you're dealing with a lot of data and a lot of potential codes. Yes, it's a lot of coding, but it is at least reliable. I've got a couple other tricks up my sleeve (including the use of a table-valued function and a cross apply), but the above examples have all the basic necessary components.
So, to reiterate:
We need a base entity that we will be flattening out by. This could be one column or a multi-column entity. In our example, id serves that purpose.
We need the sub-entity we're breaking down. In my example I assumed it was F1, but it could have easily been F2 or the combination of F1 and F2.
We need a way to differentiate the different sub-entites on the base entity. In the first example we just assigned a dummy number, which might not be a bad approach if you get your ordering and partitioning right but is almost never the preferred approach unless you're conforming relatively unlike data. In the second and third examples, we use our knowledge of the incoming data to write more exact queries.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply