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

• Moved by Kevin Cunnane [MSFT]Microsoft employee Thursday, April 24, 2014 7:51 PM Moving this to the Transact-SQL forum. That's a better place to ask coding related questions as SSDT forum focuses on issues with the Visual Studio SQL tooling

Change your query to be

SET @Query='SELECT bus_id, '+ @cols +' 
from (select b.bus_id, t.Month_date, t.tour_date, t.[status]
FROM #Bus_Master B LEFT JOIN (
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 ) t
ON b.bus_id = t.bus_id) x
pivot
(
max(status)
for tour_date in (' + @cols + ')
) p
order by Month_date'
exec(@query)

This should work fine. 

hi friend your code is working fine as i have changed a slight correction .that is order by bus_id not month_date.

I need a slight change in display.now it is display column header as 01 oct 2013.....

i want to change as 01 02 03.....31

how it is possible

Regards

Baiju

• Edited by baiju krishnan 6 hours 55 minutes ago
• Marked as answer by baiju krishnan 4 hours 23 minutes ago

hi friend your code is working fine as i have changed a slight correction .that is order by bus_id not month_date.

I need a slight change in display.now it is display column header as 01 oct 2013.....

i want to change as 01 02 03.....31

how it is possible

Regards

Baiju

Try this,

drop table #temp
drop table #bus_master
drop table #busdetails
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 @cols = STUFF((SELECT  ',' + QUOTENAME(day(Month_date)) from #temp 
FOR XML PATH('') , TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') 
SET @Query='SELECT bus_id, '+ @cols +' 
from (select b.bus_id, t.Month_date, day(t.tour_date) tour_date, t.[status]
FROM #Bus_Master B LEFT JOIN (
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 ) t
ON b.bus_id = t.bus_id) x
pivot
(
max(status)
for tour_date in (' + @cols + ')
) p
order by bus_id'
exec(@query)

Thank you very much dear friend.it's working fine now