Teradata SQL pivot multiple results and eliminate duplicate values

Question

I have something like this:

ID      Result
1       value1
2       value1
2       value2
3       value1
3       value1
4       value1
4       value2
4       value3

Notice that ID 3 has the same result appearing in two rows.

Thanks to bluefeet (teradata sql pivot multiple occurrences into additional columns), I was able to produce something like this:

ID      Result1      Result2      Result3
1       value1
2       value1       value2
3                    value1
4       value1       value2       value3

I'm getting what I want, but because ID 3 has multiple results, they are counted as 2 and then included in the second column, which is for those results that return 2 occurrences for one ID.

What I would like it do is simply return the first occurrence of the duplicate ID 3 result in the Result1 column, like this:

ID      Result1      Result2      Result3
1       value1
2       value1       value2
3       value1
4       value1       value2       value3

How can I eliminate that duplicate result and prevent it from counting?

score 0 · Answer 1 · answered Sep 28 '13 at 10:24

TD14.10 finally added DENSE_RANK (which could be used instead of ROW_NUMBER in bluefeet's query), but before you have to add some kind of DISTINCT processing, e.g.:

SELECT id,
   MAX(CASE WHEN seq =1 THEN res END) result1,
   MAX(CASE WHEN seq =2 THEN res END) result2,
   MAX(CASE WHEN seq =3 THEN res END) result3
FROM
 (
   SELECT id, res, ROW_NUMBER() OVER(PARTITION BY id ORDER BY res) seq
   FROM
    (
      SELECT DISTINCT id, res
      FROM yourtable
    ) d
 ) d
GROUP BY id
ORDER BY id;

Teradata SQL pivot multiple results and eliminate duplicate values

1 Answers1