sql-server - MS sql server 2014 使用数据透视列的最后已知值选择具有滞后的动态数据透视

Question

我还没有找到答案，所以这可能是不可能的。

我正在寻找一个数据透视查询，它将用非 NULL 列的最后一个可用值替换一个数据透视 NULL 行。如果第一行为 Null，则行为 NULL，直到某行具有值。

已更新 当 CID 更改时，行开始为新行。因此，如果 CID 3 的第一行为 Null，则该值为 null。

这是我的数据透视查询

DECLARE @Columns AS VARCHAR(MAX)
DECLARE @Query VARCHAR(MAX)
DECLARE @TEMP_DB VARCHAR(255)
SET @TEMP_DB = 'Demo_DataSet'

SELECT @Columns = 
        COALESCE(@Columns + ', ','') + QUOTENAME(AttrName)
FROM
(
    SELECT DISTINCT AttrName
FROM  Demo_FirstPass_Data_Raw
) AS B
ORDER BY B.AttrName

SET @Query = '

WITH PivotData AS
(
    SELECT 
          DocID
        , Customer
        , Version
        , CID
        , AttrName
        , AttrText
    FROM Demo_FirstPass_Data_Raw
)

SELECT 
      DocID
    , Customer
    , Version
    , CID
    , ' + @Columns + '
INTO Demo_FirstPass_Data_Pivot
FROM PivotData
PIVOT
(
    MAX(AttrText)
    FOR AttrName
    IN (' + @Columns + ')
) AS PivotResult
Where Version = Version
ORDER BY DocID, Version, CID'

DECLARE @SQL_SCRIPT VARCHAR(MAX)

SET @SQL_SCRIPT = REPLACE(@Query, '' + @TEMP_DB + '', @TEMP_DB)
EXECUTE (@SQL_SCRIPT)

我的结果是

DocID | Customer | Version | CID | Username | Sales_Order |    Date    | Description
1852  | Acme     |    1    |  2  |   User1  |    NULL     | 11/17/2010 | Product
1852  | Acme     |    2    |  2  |   NULL   |    NULL     |    NULL    | NULL 
1852  | Acme     |    3    |  2  |   NULL   |    NULL     | 12/15/2010 | NULL
1852  | Acme     |    4    |  2  |   NULL   |    NULL     |    NULL    | NULL
1852  | Acme     |    5    |  2  |   NULL   |    S-0001   | 11/17/2010 | NULL
1852  | Acme     |    7    |  2  |   NULL   |    S-0001   |    NULL    | NULL
1852  | Acme     |    8    |  2  |   NULL   |    NULL     |  1/14/2011 | NULL
1852  | Acme     |    9    |  2  |   NULL   |    NULL     |    NULL    | NULL
1852  | Acme     |   10    |  2  |   NULL   |    NULL     |    NULL    | NULL
1852  | Acme     |    1    |  3  |   User2  |    NULL     | 10/10/2010 | Product
1852  | Acme     |    2    |  3  |   NULL   |    NULL     |    NULL    | NULL 
1852  | Acme     |    3    |  3  |   NULL   |    NULL     | 12/15/2010 | NULL

我要找的是

DocID | Customer | Version | CID | Username | Sales_Order |    Date    | Description
1852  | Acme     |    1    |  2  |   User1  |    NULL     | 11/17/2010 | Product
1852  | Acme     |    2    |  2  |   User1  |    NULL     | 11/17/2010 | Product
1852  | Acme     |    3    |  2  |   User1  |    NULL     | 12/15/2010 | Product
1852  | Acme     |    4    |  2  |   User1  |    NULL     | 12/15/2010 | Product
1852  | Acme     |    5    |  2  |   User1  |    S-0001   | 11/17/2010 | Product
1852  | Acme     |    7    |  2  |   User1  |    S-0001   | 11/17/2010 | Product
1852  | Acme     |    8    |  2  |   User1  |    S-0001   |  1/14/2011 | Product
1852  | Acme     |    9    |  2  |   User1  |    S-0001   |  1/14/2011 | Product
1852  | Acme     |   10    |  2  |   User1  |    S-0001   |  1/14/2011 | Product
1852  | Acme     |    1    |  3  |   User2  |    NULL     | 10/10/2010 | Product
1852  | Acme     |    2    |  3  |   User2  |    NULL     | 10/10/2010 | Product
1852  | Acme     |    3    |  3  |   User2  |    NULL     | 12/15/2010 | Product

感谢任何帮助。

Answer 1

对于未知数量的列并集成到动态数据透视表中，一种选择是为递归 cte 生成代码，并使用它根据您的分区保留最后一个非空值，如下所示:

declare @Columns as nvarchar(max)
declare @Query nvarchar(max)
declare @temp_db nvarchar(255)
set @temp_db = 'Demo_DataSet'

select @Columns = 
        coalesce(@Columns + ', ','') + quotename(AttrName)
from
(
    select distinct AttrName
from  Demo_FirstPass_Data_Raw
) as B
order by B.AttrName

/* generate isnull statements for columns in recursive cte */
declare @isnull nvarchar(max) = stuff((
    select distinct ', isnull(t.'+quotename(d.AttrName)+',cte.'+quotename(d.AttrName)+')'
    from Demo_FirstPass_Data_Raw d
    order by 1
    for xml path (''), type).value('(./text())[1]','nvarchar(max)')
    ,1,2,'')

set @Query = 'with PivotData as (
  select Docid, Customer, Version, cid, AttrName, AttrText 
  from Demo_FirstPass_Data_Raw
)
, t as (
  select 
        Docid, Customer, Version, cid
      , ' + @Columns + '
      , rn = row_number() over (partition by DocId, Customer, cid order by Version)
  from PivotData
  pivot(max(AttrText) for AttrName in (' + @Columns + ')) as PivotResult
)
, cte as (
  select [Docid], [Customer], [Version], [cid], ' + @Columns + ', rn
  from t
  where version = 1
  union all
  select t.[Docid], t.[Customer], t.[Version], t.[cid]
      , '+ @isnull + '
      '+',t.rn
  from t
    inner join cte
      on t.rn = cte.rn+1
     and t.docid = cte.docid
     and t.customer = cte.customer
     and t.cid = cte.cid
)
select * 
from cte
order by docid, customer, cid, version
'

select @query
exec sp_executesql @query

rextester 演示:http://rextester.com/OQZOW62536

代码生成:

with PivotData as (
  select Docid, Customer, Version, cid, AttrName, AttrText 
  from Demo_FirstPass_Data_Raw
)
, t as (
  select 
        Docid, Customer, Version, cid
      , [Date], [Description], [Sales_Order], [Username]
      , rn = row_number() over (partition by DocId, Customer, cid order by Version)
  from PivotData
  pivot(max(AttrText) for AttrName in ([Date], [Description], [Sales_Order], [Username])) as PivotResult
)
, cte as (
  select [Docid], [Customer], [Version], [cid], [Date], [Description], [Sales_Order], [Username], rn
  from t
  where version = 1
  union all
  select t.[Docid], t.[Customer], t.[Version], t.[cid]
      , isnull(t.[Date],cte.[Date]), isnull(t.[Description],cte.[Description]), isnull(t.[Sales_Order],cte.[Sales_Order]), isnull(t.[Username],cte.[Username])
      ,t.rn
  from t
    inner join cte
      on t.rn = cte.rn+1
     and t.docid = cte.docid
     and t.customer = cte.customer
     and t.cid = cte.cid
)
select * 
from cte
order by docid, customer, cid, version

结果:

+-------+----------+---------+-----+------------+-------------+-------------+----------+----+
| Docid | Customer | Version | cid |    Date    | Description | Sales_Order | Username | rn |
+-------+----------+---------+-----+------------+-------------+-------------+----------+----+
|  1852 | Acme     |       1 |   2 | 2010-11-17 | Product     | NULL        | User1    |  1 |
|  1852 | Acme     |       2 |   2 | 2010-11-17 | Product     | NULL        | User1    |  2 |
|  1852 | Acme     |       3 |   2 | 2010-12-15 | Product     | NULL        | User1    |  3 |
|  1852 | Acme     |       4 |   2 | 2010-12-15 | Product     | NULL        | User1    |  4 |
|  1852 | Acme     |       5 |   2 | 2010-11-17 | Product     | S-0001      | User1    |  5 |
|  1852 | Acme     |       7 |   2 | 2010-11-17 | Product     | S-0001      | User1    |  6 |
|  1852 | Acme     |       8 |   2 | 2011-01-14 | Product     | S-0001      | User1    |  7 |
|  1852 | Acme     |       9 |   2 | 2011-01-14 | Product     | S-0001      | User1    |  8 |
|  1852 | Acme     |      10 |   2 | 2011-01-14 | Product     | S-0001      | User1    |  9 |
|  1852 | Acme     |       1 |   3 | 2010-10-10 | Product     | NULL        | User2    |  1 |
|  1852 | Acme     |       2 |   3 | 2010-10-10 | Product     | NULL        | User2    |  2 |
|  1852 | Acme     |       3 |   3 | 2010-12-15 | Product     | NULL        | User2    |  3 |
+-------+----------+---------+-----+------------+-------------+-------------+----------+----+