我需要根据以下四个表创建报告:
Table A: Columns(record_id, contact_id, role_id)
Table B: Columns(contact_id, fname, lname)
Table C: Columns(role_id,role_description)
Table D: Columns(Record_id, date)
询问:
select d.record_id,d.date,b.fname,b.lname,c.role_description
from a,b,c,d
where a.contact_id = b.contact_id and
a.role_id = c.role_id and
a.role_id in('legal','finance') and
a.record_id = d.record_id
该查询将产生多行,如下所示:
Record ID | Date | Fname | Lname | Role |
=============================================
1 |Jan 2 | Bob | Smith | Legal |
----------------------------------------------
1 |Jan 2 | Jim | Jones | Finance |
----------------------------------------------
我需要生产的是这样的:
-----------------------------------------------------------------------------
record ID | Date | Legal User | Finance User |
--------------------------------------------------------------------
1 | Jan 2 |Bob Smith | Jim Jones |
我了解联接和串联,但是无法弄清楚如何根据角色将2个用户排成一行。
SELECT *
FROM
(
select d.record_id,d.date
,b.fname + b.lname AS name
,c.role_description + ' User' as role_description
from a,b,c,d
where a.contact_id = b.contact_id and
a.role_id = c.role_id and
a.role_id in('legal','finance') and
a.record_id = d.record_id
) q
PIVOT
(
MAX(name)
FOR role_description IN ([Legal User], [Finance User])
)
您应该可以使用PIVOT。我知道这看起来很奇怪,因为您使用的是varchar的MAX(),但是这样做确实可行。
根据您对未简化的特定语法的评论,我可以这样做:
SELECT *
FROM
(
select
d.ctx_id
,effective_date,
b.first_name + b.last_name AS name
,c.description
from
ctx_contract_contacts a
INNER JOIN ctx_contacts b
ON a.contact_id = b.contact_id
INNER JOIN code_lookup c
ON a.association = c.lookup_code
INNER JOIN ctx_basic_info d
ON a.ctx_id = d.ctx_id
where
and a.association in(117601,117759)
) q
PIVOT
(
MAX(name)
FOR description IN ([Legal Reviewer], [NDA reviewer])
) p
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句