CODEWITHSUNDEEP
sql-servert-sqlpivotpivot-tablesql-server-2014
Impshial
Impshial

Reputation: 25

T-SQL Pivot with Custom Column names

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.

Upvotes: 1

Views: 241

Answers (2)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

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

Upvotes: 0

Hogan
Hogan

Reputation: 70529

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

Upvotes: 2

Related Questions