SQL-如何在没有透视的情况下仅将一些行值转换为列标题

Question

我有一个类似的表：

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 

但需要将其转换为：(基本上将"col_num"转换为列标题，并将50更改为函数，将60更改为部门(

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

不幸的是，Pivot在我的系统中不起作用，只是想知道是否有其他方法可以做到这一点？

到目前为止，我的代码(很抱歉列表太长(：

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')

Answer 1

我只会使用条件聚合：

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;

您的代码似乎与示例数据无关。但是，我注意到您使用的是隐式联接语法。您确实需要学习如何使用正确、明确的标准JOIN语法。

Answer 2

我假设您拥有Sql Server 2000或2003。在这种情况下，您需要做的是用一个光标创建一个脚本。这个光标将创建一个文本，内容如下：string var="CREATE TABLE#Report(第1列VARCHAR(20(、第2列VARCharr(20("+ColumnName

这样，您就可以动态创建一个临时表，最后您需要对临时表进行选择，以准备好数据透视表。

如果你不熟悉游标，那就没那么容易了。

或

如果你的"pivot"列上只有几个值，而且它们不会增长，你也可以这样做：

使用SQL Server 2000 的透视

Answer 3

我无法理解您的代码，所以我只假设示例数据中提到的表为stud(因为stud_id(。这是我认为可以做枢轴的工作。

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

说明：我只是想在这里模拟一下PIVOT的作用。对于所需的每一列，在JOIN中创建一个新表，并将其固定为col_num列中的一个值。例如，如果s1中没有50的值，则OUTER JOIN将变为NULL，并且我们需要从s2中提取记录。

注意：如果您需要2个以上的新列，则可以使用COALESCE而不是ISNULL