Monday 17 June 2013

pivot without aggregate function in sql server

       Hi ,
                 In this article we will see how to do pivot without aggregate function.

Example::

1)Create table and fill with data::


Create table #Unpivot (date varchar(100) ,day_name Varchar(100))
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()),'monday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+1),'Tuesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+2),'Wednesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+3),'Thursday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+4),'monday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+5),'Tuesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+6),'Wednesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+7),'Thursday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+8),'monday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+9),'Tuesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+10),'Wednesday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+11),'Thursday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+12),'monday')
insert into #Unpivot (date,day_name)values(Convert(varchar,getdate()+13),'Tuesday')

2) Check below query for pivot data without aggregate and with Cursor.


select date,day_name from #Unpivot
declare @count int
select @count=count(*) from #Unpivot
create table #pivot(Monday datetime,
Tuesday datetime,
Wednesday datetime,
Thursday datetime
)
declare @flag varchar(100)
declare @str varchar(max)
declare @date varchar(100)
declare @date_name varchar(50)
set @flag ='First_Time'
set @str =''
declare @end int
set @end=0
declare @pivotdata CURSOR
set @pivotdata = CURSOR for
select date,day_name from #Unpivot
open @pivotdata
fetch next
from @pivotdata into @date,@date_name
while @@FETCH_STATUS = 0
begin
set @end=@end+1
if @flag ='First_Time'
begin
if(@date_name='Monday')
begin
set @str =@date
end

if(@date_name='Tuesday')
begin
IF @str =''
BEGIN
set @str ='0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@date_name='Wednesday')
begin
IF @str =''
BEGIN
set @str ='0'+','+'0'+','+@date
END
ELSE
BEGIN
set @str =@str+','+@date
END
end
if(@date_name='Thursday')
begin
if @str=''
begin
set @str ='0'+','+'0'+','+'0'+@date
end
else
begin
set @str =@str+','+@date
end
insert into #pivot (Monday,Tuesday,Wednesday,Thursday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data
set @str=''
set @flag='0'
end
end
else
begin

if(@date_name='Monday')
begin
set @str =@date
if @end =@count
begin

insert into #pivot (Monday,Tuesday,Wednesday,Thursday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data
end
end
if(@date_name='Tuesday')
begin
set @str =@str+','+@date
if @end =@count
begin

insert into #pivot (Monday,Tuesday,Wednesday,Thursday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data

end
end

if(@date_name='Wednesday')
begin
set @str =@str+','+@date
if @end =@count
begin
insert into #pivot (Monday,Tuesday,Wednesday,Thursday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data

end
end
if(@date_name='Thursday')
begin

set @str =@str+','+@date

insert into #pivot (Monday,Tuesday,Wednesday,Thursday)select * from (
select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data
set @str=''
end
end
fetch next
from @pivotdata into @date,@date_name
end
close @pivotdata
deallocate @pivotdata

3) Check output::

select * from #pivot

5 comments:

  1. give me details about dbo.splitstring
    table

    ReplyDelete
  2. Hello!
    When I copy your code and run it, I get errors:
    Invalid column name 's'.
    Invalid column name 'id'.

    The errors refer to the line:
    select * from dbo.SplitString(@str,',')) tab pivot (min(s) for id in([0],[1],[2],[3]) )data

    It seems something is missing...?

    ReplyDelete
    Replies
    1. you need to create function dbo.SplitString() .

      Refer below link to create function

      http://sandipgsql.blogspot.in/2013/06/split-funtions-in-sql-server.html

      Delete
  3. Hi Sandeep,

    I followed all above steps, including createing the dbo.SplitString function.

    Still getting below error in sql server 2008R2:-

    ---------------------------------------------
    Msg 207, Level 16, State 1, Line 66
    Invalid column name 'id'.
    Msg 207, Level 16, State 1, Line 81
    Invalid column name 'id'.
    Msg 207, Level 16, State 1, Line 91
    Invalid column name 'id'.
    Msg 207, Level 16, State 1, Line 102
    Invalid column name 'id'.
    Msg 207, Level 16, State 1, Line 112
    Invalid column name 'id'.
    ---------------------------------------------

    ReplyDelete
    Replies
    1. Refer below link to create split function,a split function return table with id column.

      http://sandipgsql.blogspot.in/2013/06/split-funtions-in-sql-server.html

      Delete

if you have any doubt any suggestions do comment