MS SQL 多次透视另一个表

本文介绍了MS SQL 多次透视另一个表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个结构如下的数据库

I have a database with the following structure

ID Name 1 John 2 Doe 3 Dave 4 Smith

另一张桌子是员工亲属

ID EmpID RelativeType Name 1 1 Son x 2 1 Daughter y 3 1 Wife a 4 1 Friend b 5 1 Father c 6 1 Friend e

孩子最多可以固定 8 个，朋友最多可以固定 3 个

Childs can be fixed upto maximum of 8 and friends can be maximum of 3

我能够在下面进行透视查询以带孩子，但无法再次透视以获取 3 个朋友、父亲等.

I was able to make a pivot query below to bring childs, but unable to pivot again to fetch 3 friends, father etc.

这是我的查询:

select * from ( select e.ID, e.FirstName, CASE WHEN es.RelativeType = 'Son' or es.RelativeType = 'Daughter' THEN es.FirstName END as Child, CASE WHEN es.RelativeType = 'Son' or es.RelativeType = 'Daughter' THEN CONCAT('Child ',ROW_NUMBER() OVER (ORDER BY e.ID)) END as Relation from Employee e left join EmployeeRelatives es on e.ID = es.EmpID group by e.ID, e.FirstName,es.RelativeID,es.FirstName ) x pivot ( max(Child) for Relation in([Child 1],[Child 2],[Child 3], [Child 4], [Child 5], [Child 6], [Child 7], [Child 8]) ) as p1

以上查询返回如下结果.

The above query returns the following result.

EmpID , Name, Child 1, Child 2,Child 3, Child 4, Child 5, Child 6 1 John x y NULL NULL NULL NULL

当我制作多个枢轴时，它会返回多行，这不是必需的.我需要修改上面的查询以获得想要的结果

When I make multiple pivots it returns multiple rows, which is not required. I need to amend the above query to bring the desired result

所需的结果集如下

EmpID , Name, Child 1, Child 2, Child 3, Child 4, Wife, Friend 1, Friend 2, Friend 3, Father 1 John x y NULL NULL a b e NULL c

我使用的是 MS SQL 2014.

I am using MS SQL 2014.

推荐答案

你可以试试这个，希望对你有帮助 :)祝你好运！！:)

You may try this out and I hope this can help you :) Good Luck!! :)

WITH Children AS ( SELECT e.EmpID, e.FirstName EmpName, er.FirstName RelName, 'Child' + CONVERT(NVARCHAR(2), ROW_NUMBER() OVER (PARTITION BY er.EmpID ORDER BY er.RelativeID)) AS 'Relation' FROM Employee e LEFT JOIN EmployeeRelatives er ON e.EmpID = er.EmpID WHERE er.RelativeType IN ('Son', 'Daughter') ), ChildInRow AS ( SELECT * FROM Children PIVOT ( MAX(RelName) FOR Relation in([Child1],[Child2],[Child3], [Child4], [Child5], [Child6], [Child7], [Child8]) ) AS p1 ), Friends AS ( SELECT e.EmpID, e.FirstName EmpName, er.FirstName RelName, 'Friend' + CONVERT(NVARCHAR(2), ROW_NUMBER() OVER (PARTITION BY er.EmpID ORDER BY er.RelativeID)) AS 'Relation' FROM Employee e LEFT JOIN EmployeeRelatives er ON e.EmpID = er.EmpID WHERE er.RelativeType = 'Friend' ), FriendsInRow AS ( SELECT * FROM Friends PIVOT ( MAX(RelName) FOR Relation in([Friend1],[Friend2],[Friend3]) ) AS p1 ), AllWife AS ( SELECT e.EmpID, e.FirstName EmpName, er.FirstName RelName, 'Wife' AS 'Relation', ROW_NUMBER() OVER (PARTITION BY er.EmpID ORDER BY er.RelativeID) wife_row_num FROM Employee e LEFT JOIN EmployeeRelatives er ON e.EmpID = er.EmpID WHERE er.RelativeType = 'Wife' ), WifeInRow AS ( SELECT * FROM AllWife WHERE wife_row_num = 1 ), AllFather AS ( SELECT e.EmpID, e.FirstName EmpName, er.FirstName RelName, 'Father' AS 'Relation', ROW_NUMBER() OVER (PARTITION BY er.EmpID ORDER BY er.RelativeID) father_row_num FROM Employee e LEFT JOIN EmployeeRelatives er ON e.EmpID = er.EmpID WHERE er.RelativeType = 'Father' ), FatherInRow AS ( SELECT * FROM AllFather WHERE father_row_num = 1 ) SELECT e.EmpID, e.FirstName, ISNULL(Child1, '') Child1, ISNULL(Child2, '') Child2, ISNULL(Child3, '') Child3, ISNULL(Child4, '') Child4, ISNULL(Child5, '') Child5, ISNULL(Child6, '') Child6, ISNULL(Child7, '') Child7, ISNULL(Child8, '') Child8, ISNULL(w.RelName, '') Wife, ISNULL(Friend1, '') Friend1, ISNULL(Friend2, '') Friend2, ISNULL(Friend3, '') Friend3, ISNULL(fa.RelName, '') Father FROM Employee e FULL OUTER JOIN ChildInRow c ON e.EmpID = c.EmpID FULL OUTER JOIN FriendsInRow f ON e.EmpID = f.EmpID FULL OUTER JOIN WifeInRow w ON e.EmpID = w.EmpID FULL OUTER JOIN FatherInRow fa ON e.EmpID = fa.EmpID

结果应该是这样的: