Setting up a SQL table query

Question

I have the following table:

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

I need a query that will return all of these values, but in one line, for example:

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

Does anyone know how to do this with SQL? I am using MySQL.

Answer 1

You can do this in MySQL by attaching the table to yourself several 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 2

Try the PIVOT command. Here is a good example.

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

The necessary command 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 3

, . SQL Server PIVOT - . ORACLE CONNECT BY.

, , :

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 that you do not include columns A, B, and C in the GROUP BY clause.

Sloppy and slow, but it works.

Another approach, which can be faster on most database systems (be careful with Cartesians here if one type has multiple entries for the 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 4

In addition to other offers, you can join the table yourself:

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'

It is assumed that (uid, type) is a unique identifier for a string.