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.


#2

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

:slight_smile:
:slight_smile:


#3

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

image

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

image


#4

You forgot to add :grin::grin::grinning::grinning:


#5

Yosaiz

:slight_smile:
:slight_smile:

there
I added
:joy:
:rofl: