Error in pivot statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error in pivot statement

Author

Topic

klbaiju
Starting Member

4 Posts

Posted - 2014-04-24 : 14:38:34

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 #bus_master

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 #bus_master.how it is possible

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-24 : 14:51:14

change your inner SELECT to:


select t.Month_date,b.tour_date,bm.bus_id,b.[status]
from #bus_master bm
left outer join #Busdetails b on b.bus_id = bm.bus_id
left outer join #temp t on t.Month_date=b.tour_date

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources