SQLTeam.com | Weblogs | Forums

Pivot-Issue


#1

I've written below script to pull data from table but not able to merge using PIVOT function.
With Cte1 as
(
SELECT iif((GPO_Policy != lead(dbo.GetCharacters(GPO_Policy,'0-9a-z')) over(ORDER BY GPO_Policy)),
(SELECT dbo.GetCharacters(GPO_Policy,'0-9a-z')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'CN_GPO1'
AND
TABLE_SCHEMA='DBO'
AND
ORDINAL_POSITION='4')
,GPO_Policy) as GPO_Test,, row_number()over (order by Convert(Date,DateTime,109)) As RepeatData
FROM CN_GPO1 Where --ServerName='CDPWA00A0232' and
convert(Date, DateTime, 109)>='2016-01-22' and convert(Date, DateTime, 109)<='2016-01-27'), CTE2 AS
(
Select Cte1.ServerName,CTE1.GPO_Policy, MIN (Convert(Date,CTE1.DateTime,109)) as Date1, row_number() over (Order by ServerName) as RowNumber
from CTE1
GROUP BY CTE1.ServerName,CTE1.GPO_Policy
HAVING COUNT(
) = 1
--Order by ServerName
), CTE3 As (Select CTE2.SErverName As ServerName,CTE2.GPO_Policy,CTE2.Date1,Cte2.RowNumber From CTE2)
Select * from Cte3
PIVOT
(
max(GPO_Policy)
FOR Date1 IN ([2016-01-22],[2016-01-27])
)piv

From the below output I need to merge null value with next date data.

If I remove RowNumber from CTE3 Then I'm getting only 35 Records or if change max to min in PIVOT then also I'm getting 35 other records.
After merging by date I should get around 70 to 74 rows.