Hi ,
In this article we will see how to do pivot without aggregate function.
In this article we will see how to do pivot without aggregate function.
Example::
1)Create table and fill with data::
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
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
Related Posts::
Pivot Unpivot data in sql server
example of Cursor in sql server
basic of Cursor in sql.
basic of view
insert into view with multiple tables
Insert into view with single table
Instead of update trigger and after update trigger in sql
Instead of Insert trigger and after insert trigger in sql
instead of delete trigger and after delete trigger in sql
basic of trigger and interview quetions on trigger
Knowledged Queries
Comman Table Expression
Functions In Sql
give me details about dbo.splitstring
ReplyDeletetable
Hello!
ReplyDeleteWhen 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...?
you need to create function dbo.SplitString() .
DeleteRefer below link to create function
http://sandipgsql.blogspot.in/2013/06/split-funtions-in-sql-server.html
Hi Sandeep,
ReplyDeleteI 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'.
---------------------------------------------
Refer below link to create split function,a split function return table with id column.
Deletehttp://sandipgsql.blogspot.in/2013/06/split-funtions-in-sql-server.html