Selecting Values from Table as Column Headers

Question

I have a table with the following structure:

ID  KEY     VALUE   SEQ
1   Amount  5       2   
1   Amount  4       1
1   Type    T1      2   
1   Type    T1      1
2   Amount  10      2   
2   Amount  5       1
2   Type    T2      2
2   Type    T2      1

I would like to create a query to get this:

ID  Amount  Type
1   5       T1
2   10      T2

As you can see there could be multiple combinations of (ID, Key) but (ID, Key, Seq) is unique.

SELECT  T.ID, 
        T1.VALUE as Amount, 
        T2.VALUE as Type
FROM 
    (SELECT ID, MAX(SEQ) as MAXSEQ FROM TABLE GROUP BY ID) as T
    JOIN
        TABLE as T1
            ON T1.ID = T.ID
            AND T1.KEY = 'Amount'
            AND T1.SEQ = MAXSEQ
    JOIN
        TABLE as T2
            ON T2.ID = T.ID
            AND T2.KEY = 'Type'
            AND T2.SEQ = MAXSEQ

But I am getting results that I wasn't expecting

ID  Amount  Type

    1   5       T1
    1   4       T1
    1   10      T1
    1   5       T1
    2   10      T2
    2   5       T2
    2   4       T2
    2   5       T2

I already read this post but it doesn't apply to my case although it helps here

Any idea on who to fix this?

I'm getting the desired result, I think. http://sqlfiddle.com/#!3/f3e87/1 Am I mising something? — Andrew, Oct 29 '13 at 21:25
thanks for the comments Andrew.. You are using sql server on your example. I am using oracle — locorecto, Oct 30 '13 at 04:16

score 1 · Answer 1 · answered Oct 29 '13 at 21:35

1

SELECT 
  id, amount, type
FROM TABLE1
  natural join (SELECT ID, MAX(SEQ) as SEQ FROM TABLE1 GROUP BY ID) 
pivot (
  max(VALUE) for key in ('Amount' as amount, 'Type' as type)
)

fiddle

answered Oct 29 '13 at 21:35

Egor Skriptunoff

23,359
2
34
64

score 0 · Answer 2 · edited May 23 '17 at 10:29

0

Something you are missing from the linked question is the DISTINCT keyword.

See also the explanation below the query posted by ypercube. In fact, you are getting duplicates because you are joining a table on itself. Thus, rows will be mirrorred.

Your subquery should be:

(SELECT DISTINCT ID, MAX(SEQ) as MAXSEQ FROM TABLE GROUP BY ID) as T

edited May 23 '17 at 10:29

Community

1
1

answered Oct 29 '13 at 21:24

Guillaume Poussel

9,572
2
33
42

not really. By grouping by id I will get the same effect as DISTINCT. – locorecto Oct 30 '13 at 04:13

score 0 · Answer 3 · answered Oct 30 '13 at 15:34

I realized that there was another column, that I didn't put in the question (to avoid giving out real data)which was affecting the results. The unique constraint was (ID, Key, Seq, Time) instead of (ID, Key, Seq). As @Andrew Mentioned the query is returning the correct results.

Here goes the query again

SELECT  T.ID, 
        T1.VALUE as Amount, 
        T2.VALUE as Type
FROM 
    (SELECT ID, MAX(SEQ) as MAXSEQ FROM TABLE GROUP BY ID) as T
    JOIN
        TABLE as T1
            ON T1.ID = T.ID
            AND T1.KEY = 'Amount'
            AND T1.SEQ = MAXSEQ
    JOIN
        TABLE as T2
            ON T2.ID = T.ID
            AND T2.KEY = 'Type'
            AND T2.SEQ = MAXSEQ

Thanks Andrew for the clarification and the sqlfiddle. I apologize if I wasted anyone's time.

Selecting Values from Table as Column Headers

3 Answers3