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.
hi raghuveer
I know this post is from 3 years ago
Everybody has forgotten
I am practicing my SQL
so trying to understand whats going on !!!
Or any Experts on this forum
Please help me understand whats going on !!!
I cant make sense of it
hi
I tried to make sense out of it
Created some of my own dummy data
and came up with a way
-- However few holes remain !!!!
I mean am i understanding this correctly
as what Raghuveer is trying to do and wants
etc etc etc
Here's what I did
drop create my own dummy data
use tempdb
go
drop table data
go
create table data
(
servername varchar(100) ,
GPO_Policy varchar(100) ,
Date1 date ,
RowNumber int identity(1,1)
)
go
insert into data select 'abc','def1','2016-01-22'
insert into data select 'abc','xyz2','2016-01-27'
insert into data select 'abc','ok1','2016-01-22'
insert into data select 'abc','why2','2016-01-27'
insert into data select 'abc','nice1','2016-01-22'
insert into data select 'abc','notbad2','2016-01-27'
go
select * from data
go
My SQL solution I think = i mean solution
SELECT a.servername,
a.[2016-01-22],
b.[2016-01-27]
FROM (SELECT Row_number()
OVER(
ORDER BY servername) AS rn,
servername,
gpo_policy AS [2016-01-22]
FROM data
WHERE date1 = '2016-01-22') a
JOIN (SELECT Row_number()
OVER(
ORDER BY servername) AS rn,
servername,
gpo_policy AS [2016-01-27]
FROM data
WHERE date1 = '2016-01-27') b
ON a.rn = b.rn
AND a.servername = b.servername
go
Results
I also tried to duplicate what Raghuveer had in the image
Raghuveer's SQL
select * FROM (
SELECT servername ,
gpo_policy ,
date1 ,
rownumber
FROM data ) p PIVOT ( min(gpo_policy) FOR date1 IN ([2016-01-22],
[2016-01-27]) )piv
Results .. Raghuveers Image
