T-SQL Pivot with Custom Column names

Question

So I have a table that looks like this:

 User_ID |   Job_Func 
 --------------------
 Adams   |   DEV
 Adams   |   NET_ENG
 Brice   |   QA
 Cohen   |   DEV
 Cohen   |   NET_ENG
 Cohen   |   SUPERVISOR

It needs to look like this

 UserName  |     Developer    |  NetworkEngineer  |   QA    |  Supervisor
 -------------------------------------------------------------------------
 Adams     |        1         |          1        |    0    |      0
 Brice     |        0         |          0        |    1    |      0
 Cohen     |        1         |          1        |    0    |      1

I've read up on Pivots and dynamic pivots, but the concept is throwing me off a bit. I don't need to Sum on Job_Func, but place it into a bucket where each user has a single row, as well as spit out custom column names.

I know there are only 4 Job Functions that need to be listed (possibly 5 in the future).

This will eventually go into view for reporting purposes.

Any help on this, or just pointing me in a good direction would be helpful.

Just asked: https://stackoverflow.com/questions/54135039/flatten-table-rows-into-columns-in-sql-server/54135112?noredirect=1#comment95101988_54135112 — Brad, Jan 10 '19 at 19:29
Just asked too: https://stackoverflow.com/questions/54134488/insert-and-update-in-a-cursor-in-sql-server — Ilyes, Jan 10 '19 at 19:33

score 2 · Accepted Answer · answered Jan 10 '19 at 19:31

2

Easy way for this is to use group by, sum and case -- like so:

SELECT  UserName,
    SUM(CASE WHEN Job_Func = 'DEV' THEN 1 ELSE 0 END) AS Developer,
    SUM(CASE WHEN Job_Func = 'NET_ENG' THEN 1 ELSE 0 END) AS NetworkEngineer,
    SUM(CASE WHEN Job_Func = 'QA' THEN 1 ELSE 0 END) AS  QA,
    SUM(CASE WHEN Job_Func = 'SUPERVISOR' THEN 1 ELSE 0 END) AS  Supervisor
FROM TABLE_NAME
GROUP BY UserName

If you only want 1 even when a job function is listed more than once you can use MAX instead of SUM

answered Jan 10 '19 at 19:31

Hogan

69,564
10
76
117

This is the way I would do it. The problem will be that it isn't dynamic when job #5 comes along – KeithL Jan 10 '19 at 19:41
This worked perfectly. Apparently, I was over thinking it. I'll just modify the code and add a new SUM line when we apply the new position. Thank you – Impshial Jan 10 '19 at 19:50

Derviş Kayımbaşıoğlu · Answer 2 · 2019-01-10T19:58:59.007

0

You can use ANSI way to do this, But it has some manual work

SELECT DISTINCT
   USER_ID,
   (SELECT count(*) FROM TB1 WHERE TB1.JOB_FUNC = 'DEV' AND T.USER_ID = TB1.USER_ID) as Developer,
   (SELECT count(*) FROM TB1 WHERE TB1.JOB_FUNC = 'NET_ENG' AND T.USER_ID = TB1.USER_ID) as NetworkEngineer,
   (SELECT count(*) FROM TB1 WHERE TB1.JOB_FUNC = 'QA' AND T.USER_ID = TB1.USER_ID) as QA,
   (SELECT count(*) FROM TB1 WHERE TB1.JOB_FUNC = 'SUPERVISOR' AND T.USER_ID = TB1.USER_ID) as supervisor
 fROM TB1 T

or

SELECT  USER_ID,
    SUM(IFF(Job_Func = 'DEV', 1, 0)) AS Developer,
    SUM(IFF(Job_Func = 'NET_ENG', 1, 0)) AS NetworkEngineer,
    SUM(IFF(Job_Func = 'QA', 1, 0)) AS  QA,
    SUM(IFF(Job_Func = 'SUPERVISOR', 1, 0)) AS  Supervisor
FROM TB1
GROUP BY USER_ID

edited Jan 10 '19 at 19:58

answered Jan 10 '19 at 19:36

Derviş Kayımbaşıoğlu

28,492
4
50
72

what is COINT? do you mean COUNT? Is this ANSI SQL? – Hogan Jan 10 '19 at 19:45
IFF is not ANSI it is SQL Server – Hogan Jan 10 '19 at 19:49
Yes count :) let me fix it. First query is ansi second is not – Derviş Kayımbaşıoğlu Jan 10 '19 at 19:58
and should be noted first query is `O(4N)` and second is `O(N)` so 4 times slower – Hogan Jan 10 '19 at 20:02

T-SQL Pivot with Custom Column names

2 Answers2