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