在SQL查询中动态内插额外的行 [英] Dynamically interpolate extra rows in SQL query

问题描述

我正在使用SQL Server2012.我有以下查询:

I am using SQL Server 2012. I have the following query:

SELECT p.PortfolioGroupID, p.PortfolioGroupCode, p.DisplayOrder, p.MemberCode, m.ContactCode, m.Custom01, 'Separator' As 'PDFType'
  FROM [APXFirm].[AdvApp].[vPortfolioGroupMemberFlattened] p
  LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioInterestedParty] m
  on p.memberid = m.PortfolioID
WHERE m.ContactCode is not null
and p.PortfolioGroupCode like '%_Package'
  order by m.ContactCode, p.MemberCode

当前结果集

PortfolioGroupID    PortfolioGroupCode  DisplayOrder    MemberCode  ContactCode Custom01    PDFType
11224   Test1_Package   677 test1   recipient1_ind  DVAAnnual   Separator
11224   Test1_Package   730 test2   recipient1_ind  DVAAnnual   Separator
11221   Test2_Package   1825    test3   recipient2_ind  DVAAnnual   Separator
11221   Test2_Package   1826    test4   recipient2_ind  DVAAnnual   Separator
11221   Test2_Package   1827    test5   recipient2_ind  DVAAnnual   Separator

所需结果集

PortfolioGroupID    PortfolioGroupCode  DisplayOrder    MemberCode  ContactCode Custom01    PDFType
11224   Test1_Package   677 test1   recipient1_ind  DVAAnnual   Cover
11224   Test1_Package   677 test1   recipient1_ind  DVAAnnual   Separator
11224   Test1_Package   677 test1   recipient1_ind  DVAAnnual   Report
11224   Test1_Package   730 test2   recipient1_ind  DVAAnnual   Separator
11224   Test1_Package   730 test2   recipient1_ind  DVAAnnual   Report
11221   Test2_Package   1825    test3   recipient2_ind  DVAAnnual   Cover
11221   Test2_Package   1825    test3   recipient2_ind  DVAAnnual   Separator
11221   Test2_Package   1825    test3   recipient2_ind  DVAAnnual   Report
11221   Test2_Package   1826    test4   recipient2_ind  DVAAnnual   Separator
11221   Test2_Package   1826    test4   recipient2_ind  DVAAnnual   Report
11221   Test2_Package   1827    test5   recipient2_ind  DVAAnnual   Separator
11221   Test2_Package   1827    test5   recipient2_ind  DVAAnnual   Report

对于在PortfolioGroupID列中由新的PortfolioGroupID指示的组的每个实例. (在此示例中，有2组11224和11221)在放置单词分隔符之前，我需要添加一行作为第一行，该行的单词为 Cover .所有其他列与之前放置的行相同.

For every instance of a group indicated by a new PortfolioGroupID in the PortfolioGroupID column. (In this example there are 2 groups 11224 and 11221) I need one row added as the first row that has the word Cover before where I have placed the word separator. All other columns are identical to the row it is placed before.

对于组中的每一行，我需要在插入分隔符的行之后插值/替换一行，并添加报告 >.所有其他列均与放置在其后的行相同.

For every row in the group I need to interpolate/alternate a row with the word Report after the row where I placed the word separator. All other columns are identical to the row it is placed after.

如果出于某种原因将分隔符"一词放在报告"之前比较容易，我可以将分隔符"的硬编码值更改为报告".

If for some reason it's easier to place the word Separator before Report I could change the hard-coded value of Separator to Report.

这可能吗?

推荐答案

第一步是添加RowNumber列以标识每个PorfoilioGroupID的第一行:

The first step would be to add a RowNumber column to identify the first row for each PorfoilioGroupID:

SELECT  p.PortfolioGroupID, 
        p.PortfolioGroupCode, 
        p.DisplayOrder, 
        p.MemberCode, 
        m.ContactCode, 
        m.Custom01, 
        RowNumber = ROW_NUMBER() OVER(PARTITION BY P.PortfolioGroupID ORDER BY m.ContactCode, p.MemberCode)
FROM    [APXFirm].[AdvApp].[vPortfolioGroupMemberFlattened] p
        LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioInterestedParty] m
            ON p.memberid = m.PortfolioID
WHERE   m.ContactCode is not null
AND     p.PortfolioGroupCode like '%_Package'
ORDER BY m.ContactCode, p.MemberCode;

然后，您可以将结果集与具有3个值(Cover，Sperator，Report)的表值构造函数联接，只需添加where子句以确保"Cover"仅出现在第一行:

Then you can join your results set with a table valued constructor with your 3 values (Cover, Sperator, Report), and just add a where clause to ensure 'Cover' only appears for the first row:

WITH Data AS
(   SELECT  p.PortfolioGroupID, 
            p.PortfolioGroupCode, 
            p.DisplayOrder, 
            p.MemberCode, 
            m.ContactCode, 
            m.Custom01, 
            RowNumber = ROW_NUMBER() OVER(PARTITION BY P.PortfolioGroupID ORDER BY p.DisplayOrder)
    FROM    [APXFirm].[AdvApp].[vPortfolioGroupMemberFlattened] p
            LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioInterestedParty] m
                ON p.memberid = m.PortfolioID
    WHERE   m.ContactCode is not null
    AND     p.PortfolioGroupCode like '%_Package'
)
SELECT  d.PortfolioGroupID, 
        d.PortfolioGroupCode, 
        d.DisplayOrder, 
        d.MemberCode, 
        d.ContactCode, 
        d.Custom01, 
        t.PDFType
FROM    Data d
        CROSS JOIN 
        (   VALUES
                ('Cover'),
                ('Seperator'),
                ('Report')
        ) t (PDFType)
WHERE   d.RowNumber = 1
OR      t.PDFType != 'Cover'
ORDER BY ContactCode, MemberCode;

关于SQL Fiddle的示例

Example on SQL Fiddle

这篇关于在SQL查询中动态内插额外的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！