CODEWITHSUNDEEP
sqloracle-database
locorecto
locorecto

Reputation: 1203

Selecting Values from Table as Column Headers

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?

Upvotes: 0

Views: 1917

Answers (3)

locorecto
locorecto

Reputation: 1203

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.

Upvotes: 0

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23737

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

Upvotes: 1

Guillaume Poussel
Guillaume Poussel

Reputation: 9822

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

Upvotes: 0

Related Questions