SQL Query problem?

Question

I have the following table:

UID      | Limit       |Type
8        | 4           |A
8        | 5           |B
8        | 6           |C

I want a query, that will return all of these values, but in one row, like this:

UID      | A | B | C
8        | 4 | 5 | 6

Anybody know how to do that with SQL? I use MySQL.

Answer 1

Try PIVOT command. Here is good example.

http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

The command you need may look like this:

SELECT UID, A, B, C
FROM (
SELECT * 
FROM table1) t1
PIVOT (SUM(Limit) FOR [Type] IN (A,B,C)) AS pvt

SUM can be replaced by any other aggregate function

Answer 2

Please indicate which database technology you're using. In SQL Server, PIVOT is a fine choice. In ORACLE, CONNECT BY syntax might work.

If your types are fixed and don't mind an ugly query that will work pretty much anywhere, the following will suffice:

SELECT 
  T.UID, 
  MIN((SELECT Limit FROM TheTable A WHERE A.UID = T.UID AND Type = 'A')) A, 
  MIN((SELECT Limit FROM TheTable B WHERE B.UID = T.UID AND Type = 'B')) B,
  MIN((SELECT Limit FROM TheTable C WHERE C.UID = T.UID AND Type = 'C')) C
FROM TheTable T
GROUP BY T.UID

The MIN function is used to avoid complaints from the database server about not including columns A, B, and C in your GROUP BY clause.

Sloppy and slow, but it works.

Another approach that may be quicker in most database systems (be wary of cartesians here if one type has multiple records per UID):

SELECT 
  Driver.UID, 
  A.Limit A, 
  B.Limit B,
  C.Limit C
  FROM TheTable Driver
  INNER JOIN TheTable A ON Driver.UID = A.UID AND A.Type = 'A'
  INNER JOIN TheTable B ON Driver.UID = B.UID AND B.Type = 'B'
  INNER JOIN TheTable C ON Driver.UID = C.UID AND C.Type = 'C'

Answer 3

You can do this in MySQL by joining the table unto itself a few times:

select
    t1.uid,
    t2.limit as A,
    t3.limit as B,
    t4.limit as C
from
    tbl t1
    inner join tbl t2 on
        t1.uid = t2.uid
        and t2.type = 'A'
    inner join tbl t3 on
        t1.uid = t3.uid
        and t3.type = 'B'
    inner join tbl t4 on
        t1.uid = t4.uid
        and t4.type = 'C'

Answer 4

In addition to the other suggestions, you can join the table on itself:

select base.uid, a.limit, b.limit, c.limit
from (select distinct uid from table) base
left join table a on base.uid = a.uid and a.type = 'A'
left join table b on base.uid = b.uid and b.type = 'B'
left join table c on base.uid = c.uid and c.type = 'C'

This is assuming that (uid,type) is a unique identifier for a row.