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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 pivot specific rows

Author  Topic 

sapator
Constraint Violating Yak Guru

462 Posts
Posted - 2014-12-03 : 06:10:26
Hi. I haven't used pivot so i am not sure if this is the way to go.
I have a result such like
27.11.2014 - 13.03.2014, CinemaX, TitleX ...etc , 17:00
27.11.2014 - 13.03.2014, CinemaX, TitleX ...etc , 19:00
27.11.2014 - 13.03.2014, CinemaX, TitleX ...etc , 21:00
etc
So this will bring titles and their playtime.
What i need to do is Have everything as is but the time.
So it will be something like:

27.11.2014 - 13.03.2014, CinemaX, TitleX ,17:00,19:00,21:00

This should be per hour so it will have 12 hour columns(or 24 but i am not yet sure about that, anyhow that is the least of the issue).
So is there a way to accomplish that?
Thanks.



declare @datefrom datetime,@dateto  datetime
set @datefrom = '11/27/2014 00:00:00'
set @dateto = '12/03/2014 00:00:00'

SELECT  
convert(varchar, @datefrom, 104)  + ' - ' + convert(varchar, @dateto, 104) as week,
TC.Cinema_strname,Screen_strName,Film_strTitle,Film_strTitlealt, D.Distrib_strName, 
convert(varchar,Session_dtmRealShow,106) as Show_date,
convert(char(5), Session_dtmRealShow, 108) as Show_time
-- S.Screen_bytNum , 
--S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
--D.Distrib_strHODistribCode, D.Distrib_strCode
FROM tblSession S LEFT JOIN tblFilm F ON S.Film_strCode = F.Film_strCode 
LEFT JOIN tblCinema_Screen CS ON CS.Screen_bytNum = S.Screen_bytNum 
--LEFT JOIN tblPrice_Group PG ON S.PGroup_strCode = PG.PGroup_strCode  LEFT JOIN tblSession_Types ST on S.SType_strSessionTypeCode = ST.SType_strSessionTypeCode 
LEFT JOIN tblEvent E on E.Event_strCode = S.Event_strCode  LEFT JOIN tblCinema_Operator CO ON S.CinOperator_strCode = CO.CinOperator_strCode
LEFT JOIN tblDistributor D ON F.Distrib_strCode = D.Distrib_strCode 
inner join tblCinema TC on TC.Cinema_strcode = CO.Cinema_strcode
WHERE Session_strStatus IN ('O','P','A') AND (Session_dtmRealShow >= @datefrom AND Session_dtmRealShow < @dateto)
and (convert(char(5), Session_dtmRealShow, 108)  >= '15:00:00' and  convert(char(5), Session_dtmRealShow, 108) <= '23:59:00')
and co.cinoperator_strcode = 'FM'
and s.screen_bytNum in(4,5)

Group by 
-- S.Screen_bytNum , 
Screen_strName,  
Film_strTitle,Film_strTitlealt,
S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
D.Distrib_strHODistribCode, D.Distrib_strCode, D.Distrib_strName,s.Screen_bytNum,Session_dtmRealShow,TC.Cinema_strname
--ORDER BY   Session_dtmRealShow ,S.Screen_bytNum

sapator
Constraint Violating Yak Guru

462 Posts
Posted - 2014-12-04 : 05:11:00
I was thinking, if that is not possible, to create 2 steps.
One to bring everything, till the tickets and the other to pivot the tickets.
Then, somehow to merge the two sets but this is out of my league.
Thanks.
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts
Posted - 2014-12-04 : 08:55:24
or doing:
[CODE]
declare @datefrom datetime,@dateto datetime
set @datefrom = '11/27/2014 00:00:00'
set @dateto = '12/03/2014 00:00:00'

SELECT
convert(varchar, @datefrom, 104) + ' - ' + convert(varchar, @dateto, 104) as week,
TC.Cinema_strname,Screen_strName,Film_strTitle,Film_strTitlealt, D.Distrib_strName,
convert(varchar,Session_dtmRealShow,106) as Show_date,
convert(char(5), Session_dtmRealShow, 108) as Show_time1,
convert(char(5), Session_dtmRealShow, 108) as Show_time2,
convert(char(5), Session_dtmRealShow, 108) as Show_time3,
convert(char(5), Session_dtmRealShow, 108) as Show_time4,
convert(char(5), Session_dtmRealShow, 108) as Show_time5,
convert(char(5), Session_dtmRealShow, 108) as Show_time6,
convert(char(5), Session_dtmRealShow, 108) as Show_time7,
convert(char(5), Session_dtmRealShow, 108) as Show_time8,
convert(char(5), Session_dtmRealShow, 108) as Show_time9,
convert(char(5), Session_dtmRealShow, 108) as Show_time10,
-- S.Screen_bytNum ,
--S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
--D.Distrib_strHODistribCode, D.Distrib_strCode
FROM tblSession S LEFT JOIN tblFilm F ON S.Film_strCode = F.Film_strCode
LEFT JOIN tblCinema_Screen CS ON CS.Screen_bytNum = S.Screen_bytNum
--LEFT JOIN tblPrice_Group PG ON S.PGroup_strCode = PG.PGroup_strCode LEFT JOIN tblSession_Types ST on S.SType_strSessionTypeCode = ST.SType_strSessionTypeCode
LEFT JOIN tblEvent E on E.Event_strCode = S.Event_strCode LEFT JOIN tblCinema_Operator CO ON S.CinOperator_strCode = CO.CinOperator_strCode
LEFT JOIN tblDistributor D ON F.Distrib_strCode = D.Distrib_strCode
inner join tblCinema TC on TC.Cinema_strcode = CO.Cinema_strcode
WHERE Session_strStatus IN ('O','P','A') AND (Session_dtmRealShow >= @datefrom AND Session_dtmRealShow < @dateto)
and (convert(char(5), Session_dtmRealShow, 108) >= '15:00:00' and convert(char(5), Session_dtmRealShow, 108) <= '23:59:00')
and co.cinoperator_strcode = 'FM'
and s.screen_bytNum in(4,5)

Group by
-- S.Screen_bytNum ,
Screen_strName,
Film_strTitle,Film_strTitlealt,
S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
D.Distrib_strHODistribCode, D.Distrib_strCode, D.Distrib_strName,s.Screen_bytNum,Session_dtmRealShow,TC.Cinema_strname
--ORDER BY Session_dtmRealShow ,S.Screen_bytNum
[/CODE]

and then calculate if showtimes. Get the smallest showtime and then find if there is a next showtime and if so, put it to the next column, then take that showtime and if a next showtime exists then put it to the next column, etc.
Possible?
Go to Top of Page

sapator
Constraint Violating Yak Guru

462 Posts
Posted - 2014-12-05 : 03:53:37
I was advice to do this:

declare @datefrom datetime,@dateto  datetime
set @datefrom = '11/27/2014 00:00:00'
set @dateto =   '12/03/2014 00:00:00'
Create Table #PL (Session_lngSessionId int, ProvSeq int, weekd varchar(100),
Cinema_strname nvarchar(100)
,showdate varchar(100)
,timein nvarchar(100),
primary key (Session_lngSessionId))


Insert into #PL
select Session_lngSessionId,row_number() over (partition by Session_lngSessionId order by convert(char(5), Session_dtmRealShow, 108))
,convert(varchar, @datefrom, 104)  + ' - ' + convert(varchar, @dateto, 104) as week
,TC.Cinema_strname
,convert(varchar,Session_dtmRealShow,106) as Show_date
,convert(char(5), Session_dtmRealShow, 108) as Show_time
 FROM tblSession S LEFT JOIN tblFilm F ON S.Film_strCode = F.Film_strCode 
LEFT JOIN tblCinema_Screen CS ON CS.Screen_bytNum = S.Screen_bytNum 
--LEFT JOIN tblPrice_Group PG ON S.PGroup_strCode = PG.PGroup_strCode  LEFT JOIN tblSession_Types ST on S.SType_strSessionTypeCode = ST.SType_strSessionTypeCode 
LEFT JOIN tblEvent E on E.Event_strCode = S.Event_strCode  LEFT JOIN tblCinema_Operator CO ON S.CinOperator_strCode = CO.CinOperator_strCode
LEFT JOIN tblDistributor D ON F.Distrib_strCode = D.Distrib_strCode 
inner join tblCinema TC on TC.Cinema_strcode = CO.Cinema_strcode
WHERE Session_strStatus IN ('O','P','A') AND (Session_dtmRealShow >= @datefrom AND Session_dtmRealShow < @dateto)
-- excel has time between 3 to 11:59??
and (convert(char(5), Session_dtmRealShow, 108)  >= '15:00' and  convert(char(5), Session_dtmRealShow, 108) <= '23:59')
---poli specific edo all etsi to exei sto excel..
and co.cinoperator_strcode = 'FALM'
and s.screen_bytNum in(4,5)


Group by 
-- S.Screen_bytNum , 

Screen_strName,  
Film_strTitle,Film_strTitlealt,
S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
D.Distrib_strHODistribCode, D.Distrib_strCode, D.Distrib_strName,s.Screen_bytNum,Session_dtmRealShow,TC.Cinema_strname,
session_lngSessionId




SELECT  s.Session_lngSessionId,
convert(varchar, @datefrom, 104)  + ' - ' + convert(varchar, @dateto, 104) as week,
TC.Cinema_strname,Screen_strName,Film_strTitle,Film_strTitlealt, D.Distrib_strName, 
convert(varchar,Session_dtmRealShow,106) as Show_date,
--convert(char(5), Session_dtmRealShow, 108) as Show_time

PL1.timein
	,PL2.timein
	,PL3.timein
	,PL4.timein
	,PL5.timein
	,PL6.timein
	,PL7.timein
	,PL8.timein
	,PL9.timein
-- ,S.Screen_bytNum
 --, S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode
--,D.Distrib_strHODistribCode, D.Distrib_strCode
FROM tblSession S LEFT JOIN tblFilm F ON S.Film_strCode = F.Film_strCode 
LEFT JOIN tblCinema_Screen CS ON CS.Screen_bytNum = S.Screen_bytNum 
--LEFT JOIN tblPrice_Group PG ON S.PGroup_strCode = PG.PGroup_strCode  LEFT JOIN tblSession_Types ST on S.SType_strSessionTypeCode = ST.SType_strSessionTypeCode 
LEFT JOIN tblEvent E on E.Event_strCode = S.Event_strCode  LEFT JOIN tblCinema_Operator CO ON S.CinOperator_strCode = CO.CinOperator_strCode
LEFT JOIN tblDistributor D ON F.Distrib_strCode = D.Distrib_strCode 
inner join tblCinema TC on TC.Cinema_strcode = CO.Cinema_strcode
 left join #PL PL1 on PL1.Session_lngSessionId=S.Session_lngSessionId and PL1.ProvSeq=1
left Join #PL PL2 on PL2.Session_lngSessionId=S.Session_lngSessionId and PL2.ProvSeq=2
	Left Join #PL PL3 on PL3.Session_lngSessionId=S.Session_lngSessionId and PL3.ProvSeq=3
	Left Join #PL PL4 on PL4.Session_lngSessionId=S.Session_lngSessionId and PL4.ProvSeq=4
	Left Join #PL PL5 on PL5.Session_lngSessionId=S.Session_lngSessionId and PL5.ProvSeq=5
	Left Join #PL PL6 on PL6.Session_lngSessionId=S.Session_lngSessionId and PL6.ProvSeq=6
	Left Join #PL PL7 on PL7.Session_lngSessionId=S.Session_lngSessionId and PL7.ProvSeq=7
	Left Join #PL PL8 on PL8.Session_lngSessionId=S.Session_lngSessionId and PL8.ProvSeq=8
	Left Join #PL PL9 on PL9.Session_lngSessionId=S.Session_lngSessionId and PL9.ProvSeq=9
WHERE Session_strStatus IN ('O','P','A') AND (Session_dtmRealShow >= @datefrom AND Session_dtmRealShow < @dateto)
-- excel has time between 3 to 11:59??
and (convert(char(5), Session_dtmRealShow, 108)  >= '15:00' and  convert(char(5), Session_dtmRealShow, 108) <= '23:59')
---poli specific edo all etsi to exei sto excel..
and co.cinoperator_strcode = 'FALM'
and s.screen_bytNum in(4,5)

Group by 
-- S.Screen_bytNum , 
PL1.timein,PL2.timein
	,PL3.timein
	,PL4.timein
	,PL5.timein
	,PL6.timein
	,PL7.timein
	,PL8.timein
	,PL9.timein,
Screen_strName,  
Film_strTitle,Film_strTitlealt,
S.CinOperator_strCode, CO.CinOperator_strName, CO.CinOperator_strShortName , CO.Cinema_strCode,
D.Distrib_strHODistribCode, D.Distrib_strCode, D.Distrib_strName,s.Screen_bytNum,Session_dtmRealShow,TC.Cinema_strname,
s.session_lngSessionId
--ORDER BY   Session_dtmRealShow ,S.Screen_bytNum



drop table #PL


put i get null,null,null in the extra columns.Also even if i got the times, there is a date value that will create extra rows,
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site