SQL Server - Pivot

Question

Hi Below is my data in table : 1 1M 2 1M 3 1M 4 1M 5 1M 6 1M 7 1M 8 1M 9 1M 10 1M and i want output like below 1M 1 2 3 1M 4 5 6 1M 7 8 9 1M 10 Thanks

Rasadul Alam Rashed · Answer

Some times we need to convert a table columns in to row. The following example will help you to do this instead of pivot: http://cybarlab.com/sql-query-to-convert-column-to-row

Apurva Mehta · Answer

Below is my test data and query followed by output INPUT 1 1M 2 1M 3 1M 4 1M 5 1M 6 1M 7 1M 8 1M 9 1M 10 1M 11 2M 12 2M 13 2M 14 2M 15 2M 16 2M 17 2M 18 2M 19 2M 20 2M 21 3M 22 3M 23 3M 24 3M 25 3M 26 3M 27 3M 28 3M 29 3M 30 3M QUERY Select Number  ( Case When ( Number = '1M' AND ROW_NUMBER () OVER ( ORDER BY [Id] DESC ) % 3 = 0 ) OR ( Number = '2M' AND ROW_NUMBER () OVER ( ORDER BY [Id] DESC ) % 3 = 0 ) OR ( Number = '3M' AND ROW_NUMBER () OVER ( ORDER BY [Id] DESC ) % 3 = 0 ) Then Id End ) As A  ( Case When ( Number = '1M' AND ROW_NUMBER () OVER ( ORDER BY [Id] DESC ) % 3 = 2 ) OR ( Number = '2M' AND ROW_NUMBER () OVER ( ORDER BY [Id] DESC ) % 3 = 2 ) OR ( Number = '3M' AND ROW_NUMBER () OVER ( ORDER BY [Id] DESC ) % 3 = 2 ) Then Id End ) As B  ( Case When ( Number = '1M' AND ROW_NUMBER () OVER ( ORDER BY [Id] DESC ) % 3 = 1 ) OR ( Number = '2M' AND ROW_NUMBER () OVER ( ORDER BY [Id] DESC ) % 3 = 1 ) OR ( Number = '3M' AND ROW_NUMBER () OVER ( ORDER BY [Id] DESC ) % 3 = 1 ) Then Id End ) As C From TEST ORDER BY Id OutPut 1M 1 NULL NULL 1M NULL 2 NULL 1M NULL NULL 3 1M 4 NULL NULL 1M NULL 5 NULL 1M NULL NULL 6 1M 7 NULL NULL 1M NULL 8 NULL 1M NULL NULL 9 1M 10 NULL NULL 2M NULL 11 NULL 2M NULL NULL 12 2M 13 NULL NULL 2M NULL 14 NULL 2M NULL NULL 15 2M 16 NULL NULL 2M NULL 17 NULL 2M NULL NULL 18 2M 19 NULL NULL 2M NULL 20 NULL 3M NULL NULL 21 3M 22 NULL NULL 3M NULL 23 NULL 3M NULL NULL 24 3M 25 NULL NULL 3M NULL 26 NULL 3M NULL NULL 27 3M 28 NULL NULL 3M NULL 29 NULL 3M NULL NULL 30 Required Output : Number Id Id Id 1M 1 2 3 1M 4 5 6 1M 7 8 9 1M 10 2M 11 12 13 2M 14 15 16 2m 17 18 19 2M 20 3M 21 22 23 3M 24 25 26 3M 27 28 29 3M 30 Any idea  how to remove null and shift cell up. PS : Using SQL Server 2008 R2

Jignesh Trivedi · Answer

hi I think this is not possible with Pivot Query because Pivot Query must have some aggregate function. so you have think differently to resolve the issue. i have tried some code and share here --drop table #test Create table #test ( Id int Name varchar(20) ) Insert into #test values(1'1M') Insert into #test values(2'1M') Insert into #test values(3'1M') Insert into #test values(4'1M') Insert into #test values(5'1M') Insert into #test values(6'1M') Insert into #test values(7'1M') Insert into #test values(8'1M') Insert into #test values(9'1M') Insert into #test values(10'1M') declare @max int declare @id int = 1 declare @pkid int = 1 select @max = MAX(id) from #test declare @d1 table ( DatabaseID int PId int Name varchar(10) ) declare @d2 table ( DatabaseID int PId int ) declare @d3 table ( DatabaseID int PId int ) While @id begin if(@id insert into @d1 values(@pkid@id '1M') set @id+=1 if(@id insert into @d2 values(@pkid@id) set @id+=1 if(@id insert into @d3 values(@pkid@id) set @id+=1 set @pkid +=1 end select d1.Named1.PIdd2.PIdd3.PId from @d1 d1 LEFT Outer join @d2 d2 on d1.DatabaseID = d2.DatabaseID LEFT Outer join @d3 d3 on d1.DatabaseID = d3.DatabaseID hope this will help you.