下面的pivot 是怎么使用的呢

Question

--Registry2
SELECT
LEFT(REPLACE(T.[Project Link], '[', ''), CHARINDEX('|', T.[Project Link]) - 2) [Project Key],
CONCAT(LEFT(REPLACE(T.[Project Link], '[', ''), CHARINDEX('|', T.[Project Link]) - 2), ' - ', T.SUMMARY) Project,
T.ISSUEKEY,
T.SUMMARY,
T.Status,
T.Description,
T.[Program or Project],
T.[Project Code (Finance)],
T.[Project Link],
CU.display_name [Project Manager],
T.[Budget Holder / Project Sponsor],
T.[Planned Project Start Date],
T.[Planned Project Closure Date]
FROM
(
SELECT
JI.ID,
JI.issuenum,
IT.pname,
CONCAT(P.PKEY, '-', JI.issuenum) ISSUEKEY,
JI.SUMMARY,
JI.PRIORITY,
JIS.pname Status,
CAST(JI.DESCRIPTION AS VARCHAR(MAX)) Description,
CF.cfname,
CASE
WHEN CFO.CUSTOMVALUE IS NOT NULL THEN CAST(CFO.CUSTOMVALUE AS VARCHAR(MAX))
WHEN CFV.DATEVALUE IS NOT NULL THEN CAST(CFV.DATEVALUE AS VARCHAR(MAX))
WHEN CFV.NUMBERVALUE IS NOT NULL THEN CAST(CFV.NUMBERVALUE AS VARCHAR(MAX))
WHEN CFV.TEXTVALUE IS NOT NULL THEN CAST(CFV.TEXTVALUE AS VARCHAR(MAX))
ELSE CAST(CFV.STRINGVALUE AS VARCHAR(MAX))
END VALUE
FROM jiraissue JI
LEFT JOIN project P ON JI.PROJECT = P.ID
LEFT JOIN issuetype IT ON JI.ISSUETYPE = IT.ID
LEFT JOIN issuestatus JIS ON JI.issuestatus = JIS.ID
LEFT JOIN customfieldvalue CFV ON JI.ID = CFV.ISSUE
LEFT JOIN customfield CF ON CFV.CUSTOMFIELD = CF.ID
LEFT JOIN customfieldoption CFO ON CFV.STRINGVALUE = cast(CFO.ID as varchar)
WHERE 1 = 1
AND P.ID = 13509 --Registry
AND IT.PNAME IN ('Project', 'Program')
) R
PIVOT(MAX(VALUE) FOR CFNAME IN ([Project Manager (Primary)], [Project Manager(s) (Secondary)], [Budget Holder / Project Sponsor], [Project Code (Finance)],
[Planned Project Start Date], [Planned Project Closure Date], [Program or Project], [Project Link])) T
LEFT JOIN app_user AU ON T.[Project Manager (Primary)] = AU.user_key
LEFT JOIN cwd_user CU ON AU.lower_user_name = CU.lower_user_name
LEFT JOIN cwd_directory CD ON CU.directory_id = CD.ID
WHERE 1 = 1
AND T.[Project Link] IS NOT NULL
AND (CD.active IS NULL OR CD.active = 1)

Answer 1

可以参照这个 https://www.cnblogs.com/ljhdo/p/4995554.html