CODEWITHSUNDEEP
sql
Joel S.
Joel S.

Reputation: 105

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.

Upvotes: 1

Views: 3128

Answers (3)

collapsar
collapsar

Reputation: 17238

I'd suggest to group your resultset by the combination of Employee_Num and Client_num, computing synthetic aggregate columns as you need them. From this interim resultset you can derive the table with the desired output structure, filling columns by appropriately combining the computed aggregates.

Example:

SELECT agg.Employee_Num
     , agg.Client_Num
     , agg.dmin                                             Deduction_1
     , CASE agg.dmax WHEN 30 THEN agg.dmax else NULL END    Deduction_30
  FROM (
            SELECT Employee_Num
                 , Client_Num
                 , MIN (Deduction_Num)  dmin
                 , MAX (Deduction_Num)  dmax
              FROM Employee_Deductions
          GROUP BY Employee_Num
                 , Client_num
       ) agg
     ;

Upvotes: 0

Gene
Gene

Reputation: 392

You can try:

SELECT DISTINCT Employee_Num, 
Client_Num, 
CASE WHEN Deduction_Num = 1
     THEN 1
     ELSE ''
     END Deduction_1, 
CASE WHEN Deduction_Num = 30
     THEN 30
     ELSE ''
     END Deduction_30
FROM Employee_Deductions
WHERE Client_Num = 1000
AND Deduction_Num IN (1,30)

Upvotes: 0

Randall
Randall

Reputation: 1521

To literally get what you asked for a case statement would work:

select Employee_Num
  , Client_Num
  , max(case when deduction_num = 1 then deduction_num end) as Deduction_1
  , max(case when deduction_num = 30 then deduction_num end) as Deduction_30
From Employee_Deductions
Group by Employee_Num
  , Client_Num

However it sounds like what you want to do is pivot the data as per this Technet article. Hope that helps.

Upvotes: 1

Related Questions