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

结果应该是这样的:

MS SQL 多次透视另一个表

相关推荐