lundi 29 juin 2015

SQL Select - How to show multiple columns based on field value?

I have been trying to put together an SQL query that shows one line for each record with the values from another field broken out into their own columns. How would I be able to show multiple columns and a single row for each record?

I have a table with data similar to the following sample:

+--------------+------------+---------------+
| Employee_Num | Client_Num | Deduction_Num |
+--------------+------------+---------------+
|         1305 |       1000 |             1 |
|         1305 |       1000 |            30 |
|         1312 |       1000 |             1 |
|         1320 |       1000 |             1 |
|         1320 |       1000 |            30 |
|         1323 |       1000 |            30 |
|         1323 |       1000 |             1 |
+--------------+------------+---------------+

I have attempted a union but the results still show multiple records for each employee. Here's what I have tried thus far:

SELECT Employee_Num, Client_Num, Deduction_1, Deduction_30
FROM ( SELECT 
    Employee_Num,
    Client_Num, 
    Deduction_Num AS Deduction_1, 
    Deduction_Num AS Deduction_30
    FROM Employee_Deductions
    WHERE client_num = 1000
    AND Deduction_Num IN (1) 
UNION

    SELECT 
    Employee_Num,
    Client_Num, 
    Deduction_Num AS Deduction_1, 
    Deduction_Num AS Deduction_30
    FROM Employee_Deductions
    WHERE Client_Num, = 1000
    AND Deduction_Num IN (30)  
) AS Datum

WHERE Client_Num = 1000
ORDER BY Employee_Num

I would like this to be the desired result:

+--------------+------------+-------------+--------------+
| Employee_Num | Client_Num | Deduction_1 | Deduction_30 |
+--------------+------------+-------------+--------------+
|         1305 |       1000 |           1 |           30 |
|         1312 |       1000 |           1 |              |
|         1320 |       1000 |           1 |           30 |
|         1323 |       1000 |           1 |           30 |
+--------------+------------+-------------+--------------+

Any help would be appreciated.

Aucun commentaire:

Enregistrer un commentaire