CODEWITHSUNDEEP
sqloracle-databasepivot
Jake B
Jake B

Reputation: 15

SQL - how to transpose only some row values into column headers without pivot

I have a table similar to this:

stud_ID | first_name | last_name | email | col_num | user_value
1         tom          smith              50         Retail
1         tom          smith              60         Product
2         Sam          wright             50         Retail
2         Sam          wright             60         Sale

but need to convert it to: (basically transpose 'col_num' to column headers and change 50 to function, 60 to department)

 stud_ID | first_name | last_name | email | Function | Department
    1         tom          smith              Retail    Product
    2         Sam          wright             Retail    Sale

Unfortunately Pivot doesn't work in my system, just wondering if there is any other way to do this please?

The code that I have so far (sorry for the long list):

SELECT c.person_id_external as stu_id,
  c.lname,
  c.fname,
  c.mi,
  a.cpnt_id,
  a.cpnt_typ_id,
  a.rev_dte,
  a.rev_num,
  cp.cpnt_title AS cpnt_desc,
  a.compl_dte,
  a.CMPL_STAT_ID,
  b.cmpl_stat_desc,
  b.PROVIDE_CRDT,
  b.INITIATE_LEVEL1_SURVEY,
  b.INITIATE_LEVEL3_SURVEY,
  a.SCHD_ID,
  a.TOTAL_HRS,
  a.CREDIT_HRS,
  a.CPE_HRS,
  a.CONTACT_HRS,
  a.TUITION,
  a.INST_NAME,
  --a.COMMENTS,
  a.BASE_STUD_ID,
  a.BASE_CPNT_TYP_ID,
  a.BASE_CPNT_ID,
  a.BASE_REV_DTE,
  a.BASE_CMPL_STAT_ID,
  a.BASE_COMPL_DTE,
  a.ES_USER_NAME,
  a.INTERNAL,
  a.GRADE_OPT,
  a.GRADE,
  a.PMT_ORDER_TICKET_NO,
  a.TICKET_SEQUENCE,
  a.ORDER_ITEM_ID,
  a.ESIG_MESSAGE,
  a.ESIG_MEANING_CODE_ID,
  a.ESIG_MEANING_CODE_DESC,
  a.CPNT_KEY,
  a.CURRENCY_CODE,
  c.EMP_STAT_ID,
  c.EMP_TYP_ID,
  c.JL_ID,
  c.JP_ID,
  c.TARGET_JP_ID,
  c.JOB_TITLE,
  c.DMN_ID,
  c.ORG_ID,
  c.REGION_ID,
  c.CO_ID,
  c.NOTACTIVE,
  c.ADDR,
  c.CITY,
  c.STATE,
  c.POSTAL,
  c.CNTRY,
  c.SUPER,
  c.COACH_STUD_ID,
  c.HIRE_DTE,
  c.TERM_DTE,
  c.EMAIL_ADDR,
  c.RESUME_LOCN,
  c.COMMENTS,
  c.SHIPPING_NAME,
  c.SHIPPING_CONTACT_NAME,
  c.SHIPPING_ADDR,
  c.SHIPPING_ADDR1,
  c.SHIPPING_CITY,
  c.SHIPPING_STATE,
  c.SHIPPING_POSTAL,
  c.SHIPPING_CNTRY,
  c.SHIPPING_PHON_NUM,
  c.SHIPPING_FAX_NUM,
  c.SHIPPING_EMAIL_ADDR,
  c.STUD_PSWD,
  c.PIN,
  c.PIN_DATE,
  c.ENCRYPTED,
  c.HAS_ACCESS,
  c.BILLING_NAME,
  c.BILLING_CONTACT_NAME,
  c.BILLING_ADDR,
  c.BILLING_ADDR1,
  c.BILLING_CITY,
  c.BILLING_STATE,
  c.BILLING_POSTAL,
  c.BILLING_CNTRY,
  c.BILLING_PHON_NUM,
  c.BILLING_FAX_NUM,
  c.BILLING_EMAIL_ADDR,
  c.SELF_REGISTRATION,
  c.SELF_REGISTRATION_DATE,
  c.ACCESS_TO_ORG_FIN_ACT,
  c.NOTIFY_DEV_PLAN_ITEM_ADD,
  c.NOTIFY_DEV_PLAN_ITEM_MOD,
  c.NOTIFY_DEV_PLAN_ITEM_REMOVE,
  c.NOTIFY_WHEN_SUB_ITEM_COMPLETE,
  c.NOTIFY_WHEN_SUB_ITEM_FAILURE,
  c.LOCKED,
  c.PASSWORD_EXP_DATE,
  c.SECURITY_QUESTION,
  c.SECURITY_ANSWER,
  c.ROLE_ID,
  c.IMAGE_ID,
  c.GENDER,
  c.PAST_SERVICE,
  c.LST_UNLOCK_TSTMP,
  c.MANAGE_SUB_SP,
  c.MANAGE_OWN_SP,
  d.col_num,
  d.user_value

FROM pa_cpnt_evthst a,
  pa_cmpl_stat b,
  pa_student c,
  pv_course cp,
  pa_stud_user d
WHERE a.cmpl_stat_id  = b.cmpl_stat_id
AND a.stud_id         = c.stud_id
AND cp.cpnt_typ_id(+) = a.cpnt_typ_id
AND cp.cpnt_id(+)     = a.cpnt_id
AND cp.rev_dte(+)     = a.rev_dte
AND a.CPNT_TYP_ID    != 'SYSTEM_PROGRAM_ENTITY'
AND c.stud_id = d.stud_id
AND d.col_num in ('10','30','50','60')

Upvotes: 1

Views: 562

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I would just use conditional aggregation:

select stud_ID, first_name, last_name, email,
       max(case when col_num = 50 then user_value end) as function,
       max(case when col_num = 60 then user_value end) as department
from t
group by stud_ID, first_name, last_name, email;

Your code seems to have nothing to do with the sample data. I do notice however that you are using implicit join syntax. You really need to learn how to use proper, explicit, standard JOIN syntax.

Upvotes: 1

Sahil Dhoked
Sahil Dhoked

Reputation: 372

I'm unable to understand your code, so I'll just assume the table mentioned in the sample data as stud(because of stud_id). So here is what I think can do the work of pivot.

SELECT  ISNULL(s1.stud_ID, s2.stud_id), 
        ISNULL(s1.first_name, s2.first_name), 
        ISNULL(s1.last_name, s2.last_name), 
        ISNULL(s1.email, s2.email), 
        s1.user_value as [Function], s2.user_value as Department
FROM stud s1 OUTER JOIN stud s2
ON s1.stud_ID = s2.stud_ID -- Assuming stud_ID is primary key, else join on all primary keys
AND s1.col_num = 50 AND s2.col_num = 60

Explanation: I'm just trying to simulate here what PIVOT does. For every column you want, you create a new table in the JOIN and constaint it to only one value in your col_num column. For example, if there are no values for 50 in s1, the OUTER JOIN will get make it NULL and we need to pull records from s2.

Note: If you need more than 2 new columns, then you can use COALESCE instead of ISNULL

Upvotes: 0

Andres
Andres

Reputation: 1

I'm assuming you have Sql Server 2000 or 2003. What you need to do in that case is create a script with one cursor. This cursor will create a text with something like this: string var = "CREATE TABLE #Report (Col1 VARCHAR(20), Col2, VARCHAR(20), " + ColumnName

That way you can create a temp table on the fly, at the end you will need to do a Select of your temp table to get your pivot table ready.

Its not that easy if you are not familiar with cursors.

OR

if there are only few values on your 'pivot' column and they are not going to grow you can also do something like this:

Pivot using SQL Server 2000

Upvotes: 0

Related Questions