I have not found an answer yet, so this may not be possible.
I am looking for a pivot query that will replace a pivoted NULL row with the last value available for the column that was not NULL. If the First row is Null then rows are NULL until a row has a value.
Updated When CID changes the rows start as new rows. So if the first row of CID 3 is Null, then the value is null.
Here is my pivot query
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)
My result is
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
What I am looking for is
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
Any help is appreciated.
For an unknown number of columns and to integrate into a dynamic pivot, one option is to generate the code for a recursive cte and use that to retain the last non null value based on your partitions like so:
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 demo: http://rextester.com/OQZOW62536
code generated:
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
results:
+-------+----------+---------+-----+------------+-------------+-------------+----------+----+
| 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 |
+-------+----------+---------+-----+------------+-------------+-------------+----------+----+