Error in pivot statement in sqlserver

Posted by Klbaiju under Sql Server on 4/23/2014 | Points: 10 | Views : 1326 | Status : [Member] | Replies : 1
Hi All,

following is my working code

declare @dte as datetime ='2013-07-01'
declare @StDt as Datetime = DATEADD(dd,-(DAY(GETDATE())-1),@dte)
declare @EnDt as datetime = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dte)+1,0))
Declare @Cnt as int = datepart(dd,@EnDt)
Declare @inc as int = 0
Create table #temp (Month_date datetime)
while @inc < @cnt
begin
insert into #temp
select DATEADD(dd, @inc, DATEADD(dd,-(DAY(@dte)-1),@dte))
set @inc = @inc + 1
end

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(bus_id)
from busmaster
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @Query='SELECT CONVERT(VARCHAR(20), Month_date,106 )as tourdate ,'+ @cols +'
from (
select t.Month_date,b.bus_id,b.tour_date,b.[status]
from #temp t
left outer join Busdetails b on t.Month_date=b.tour_date
) x
pivot
(
max(status)
for bus_id in (' + @cols + ')
) p
order by Month_date'
exec(@query)
drop table #temp

output is like below

tourdate 100 101 102 103 104 105
01 Jul 2013 booked booked NULL NULL NULL NULL
02 Jul 2013 NULL NULL NULL NULL NULL NULL
03 Jul 2013 booked NULL NULL NULL NULL booked
;;

;

;
30 Jul 2013 NULL NULL NULL NULL NULL NULL
31 Jul 2013 NULL NULL NULL NULL NULL NULL

my requirement is i want to display dates in column heder.means replace id with dates like this

Busid 01 Jul 2013 02 Jul 2013 03 Jul 2013 04 Jul 2013 ; 31 jul 2013
100 booked booked NULL NULL NULL NULL
101 NULL NULL NULL NULL NULL NULL
102 booked NULL NULL NULL NULL booked

103 booked NULL NULL NULL NULL booked

104

105


;;

how it is possible

Regards

Baiju




Responses

Posted by: Klbaiju on: 4/24/2014 [Member] Starter | Points: 25

Up
0
Down
i have created temporary table and change the code
Hi All,

following is my working code.i have created temporary tables anybody can run this code


declare @dte as datetime ='2013-10-01'
declare @StDt as Datetime = DATEADD(dd,-(DAY(GETDATE())-1),@dte)
declare @EnDt as datetime = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dte)+1,0))
Declare @Cnt as int = datepart(dd,@EnDt)
Declare @inc as int = 0
Create table #temp (Month_date datetime)
while @inc < @cnt
begin
insert into #temp
select DATEADD(dd, @inc, DATEADD(dd,-(DAY(@dte)-1),@dte))
set @inc = @inc + 1
end
create table #bus_master(bus_id int,bus_name varchar(50))
insert into #bus_master values(100,'A')
insert into #bus_master values(101,'B')
insert into #bus_master values(102,'C')
insert into #bus_master values(103,'D')
insert into #bus_master values(104,'E')
insert into #bus_master values(105,'F')
create table #busdetails( bus_id int,tour_date datetime,status varchar(10))
insert into #busdetails values(103,'2013-10-01','booked')
insert into #busdetails values(102,'2013-10-01','booked')
insert into #busdetails values(100,'2013-10-02','booked')

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),@cols1 as nvarchar(max)
--select @cols1 = STUFF((SELECT distinct ',' + QUOTENAME(bus_id)
-- from baiju.dbo.busmaster
-- FOR XML PATH(''), TYPE
-- ).value('.', 'NVARCHAR(MAX)')
-- ,1,1,'')
--CONVERT(VARCHAR(20), Month_date,106 )
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(CONVERT(VARCHAR(20), Month_date,106 ))
from #temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')


SET @Query='SELECT bus_id, '+ @cols +'
from (
select t.Month_date,b.tour_date,b.bus_id,b.[status]
from #Busdetails b
left outer join #temp t on t.Month_date=b.tour_date



) x
pivot
(
max(status)
for tour_date in (' + @cols + ')
) p
order by Month_date'
exec(@query)
drop table #temp
drop table #bus_master
drop table #busdetails

iam getting output as

bus_id 01 oct 2013 02 oct 2013 ......................... 31 oct 2013

102 booked NULL ... ................................. NULL
103 booked NULL .........................................NULL
100 NULL booked ..................................... NULL

here bus_id is coming from busdetails that's why itshowing only 3 record in above code.bus_id should come from #busmaster

my requirement should be

bus_id 01 oct 2013 02 oct 2013 ......................... 31 oct 2013

100 NULL booked ............................... NULL

101 NULL NULL ........................................ NULL

102 booked NULL ......................................... NULL

103 booked NULL ...................................... NULL

104 NULL NULL..................................... NULL

105 NULL NULL....................................... NULL

this is for booking site,it's showing the booking of a month.bus_id should come from #busmaster.how it is possible







Klbaiju, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response