-
Dec 3rd, 2014, 06:11 AM
#1
pivot specific columns
Hi. I haven't used pivot in a looong, 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.
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_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
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 4th, 2014, 05:12 AM
#2
Re: pivot specific columns
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 seems troublesome.
Thanks.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 4th, 2014, 08:54 AM
#3
Re: pivot specific columns
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
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?
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 4th, 2014, 09:34 AM
#4
Re: pivot specific columns
Doing this
Code:
Declare @ReallyWideField varchar(max)
Select @ReallyWideField=IsNull(@ReallyWideField+', ','')
+convert(char(5), Session_dtmRealShow, 108)
From whatevertable...
Order by dtmRealShow
This will fill the VBL with all the values from each row. Put this in the sproc ahead of the SELECT you have for the dates and include it, or it it's multiple rows make a USER DEFINED FUNCTION.
Do you need it in separate rows? That can be done with Row_NUmber()
-
Dec 4th, 2014, 10:14 AM
#5
Re: pivot specific columns
I am not sure i get you.
Code:
use vista
declare @datefrom datetime,@dateto datetime
set @datefrom = '11/27/2014 00:00:00'
set @dateto = '12/03/2014 00:00:00'
Declare @ReallyWideField varchar(max)
Select @ReallyWideField=IsNull(@ReallyWideField+', ','')
+convert(char(5), Session_dtmRealShow, 108)
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
Order by Session_dtmRealShow
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,
s.Session_strComments
-- ,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)
-- 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
,s.Session_strComments
--ORDER BY Session_dtmRealShow ,S.Screen_bytNum
???????
This will not do anything.
What i need is:
column column column column
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
column column column column column column
27.11.2014 - 13.03.2014, CinemaX, TitleX , 17:00, 19:00, 21:00
If i have to write a used defined function then maybe i should better use vb.net .
thanks.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 4th, 2014, 10:24 AM
#6
Re: pivot specific columns
Here is how to do it with SQL using a temp table and ROW_NUMBER.
My situation is that I have a participant that can have many providers.
I first fill a temp table like this:
Code:
Create Table #PL (PartId int, ProvSeq int, ProvText varchar(100), ConType varchar(100), primary key (PartId,ProvSeq))
.
.
.
Insert into #PL
select partid,row_number() over (partition by partid order by contype)
,ConName,ConType from PartContact_V pc
where mrp='Y'
Then later on I select from and join to it like this
Code:
Select PT.PartId
,PT.FirstName + ' ' + PT.LastName
,PL1.ProvText
,PL2.ProvText
,PL3.ProvText
,PL4.ProvText
,PL5.ProvText
,PL6.ProvText
,PL7.ProvText
,PL8.ProvText
,PL9.ProvText
,PLP.ConType
From Participant_T PT
.
.
.
Left Join #PL PL1 on PL1.PartId=PT.PartId and PL1.ProvSeq=1
Left Join #PL PL2 on PL2.PartId=PT.PartId and PL2.ProvSeq=2
Left Join #PL PL3 on PL3.PartId=PT.PartId and PL3.ProvSeq=3
Left Join #PL PL4 on PL4.PartId=PT.PartId and PL4.ProvSeq=4
Left Join #PL PL5 on PL5.PartId=PT.PartId and PL5.ProvSeq=5
Left Join #PL PL6 on PL6.PartId=PT.PartId and PL6.ProvSeq=6
Left Join #PL PL7 on PL7.PartId=PT.PartId and PL7.ProvSeq=7
Left Join #PL PL8 on PL8.PartId=PT.PartId and PL8.ProvSeq=8
Left Join #PL PL9 on PL9.PartId=PT.PartId and PL9.ProvSeq=9
-
Dec 4th, 2014, 10:34 AM
#7
Re: pivot specific columns
Hey, can you give me one row of the expected result? What will it give out?
"partition by partid order by contype" this is where i put the time value?
Code:
,PL1.ProvText
,PL2.ProvText
,PL3.ProvText etc
is like i did
Code:
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,
P.S. i run the code you gave me, after i had the brain fart. This will just put the time rows in on big column but what i want to do is put the time rows in separate columns.
Also it will just include times from every date but what i want is include times for only the specific date, so p.e. 17:00 , 19:00 , 21:00 for the first row but the second row has other times so for example, 17:20, 21:00, 22:15 for the second row.
This is really hard for me as i am not an sql guy but an asp.net (as you know) hence the stupid questions.
But thanks for the help
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 4th, 2014, 10:39 AM
#8
Re: pivot specific columns
Ok - I abandoned the first idea to put them all in one column. In post #5 you said "column,column,column,column" - I listened!
This query I just gave you is quite simple. It put all the provider names from a participant contact table into a temp table with a row_number().
The PARTITION by is what re-starts that row_number at 1. That would be whatever primary key you have to this single row you want to produce.
The ORDER BY would be your time value - so it went it whatever order you wanted.
Like I said in another thread - SQL should be built a piece at a time. Do this TEMP TABLE in SQL SERVER Management Studio and get it looking the way you want with your PRIMARY KEY, the SEQ number and the TIME you want to display in each column.
Seeing that table built will make all your questions disappear. It will be obvious that the first time to go in the first column for time will have a SEQ of 1. Then the JOIN I have with SEQ of 1 is clear and the field that is included in the SELECT list with that JOIN ALIAS will make sense.
-
Dec 4th, 2014, 10:49 AM
#9
Re: pivot specific columns
Hey.
I do not have a primary key at all.
Code:
Cinema1 room 04 27.11.2014 - 03.12.2014 HORRIBLE BOSSES 2 HORRIBLE BOSSES 2 MYFILMS 27 Nov 2014 17:00
Cinema1 room 04 27.11.2014 - 03.12.2014 HORRIBLE BOSSES 2 HORRIBLE BOSSES 2 MYFILMS 27 Nov 2014 19:00
Cinema1 room 04 27.11.2014 - 03.12.2014 HORRIBLE BOSSES 2 HORRIBLE BOSSES 2 MYFILMS 27 Nov 2014 21:00
So what do i use? I don't have PK to bind it to.
It may look simple to you but i don't understand anything or almost anything. As i have done row_number() over i think this is due to a lot of fatigue from today. So i am going to stop this now and look at it tomorrow. I will ask if i have questions. Also if i can't make it i would go to the vb.net way and be done with this.
Thanks.
P.S. Can't rep but thanks.
P.S.2 Just thought of it. As i've said i do not want all the time values as cinema rows will not have all the time values.What you gave will work correct?
Last edited by sapator; Dec 4th, 2014 at 10:52 AM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 4th, 2014, 10:57 AM
#10
Re: pivot specific columns
Your primary key is whatever combination of values makes a row appear distinctly.
It's CINEMA, ROOM, DATE - MOVIE (?) - so that's the four fields in your TEMP TABLE for PK - it's the PARTITION BY fields as well.
Then it's a more complex join, but so what.
-
Dec 4th, 2014, 05:44 PM
#11
Re: pivot specific columns
Hmm, if i can i will look for a PK there, i was so tired that i may missed a PK.
Another question and i will try this tomorrow. So we create the temp table, find a PK, do the partition in the time value (and any group by i guess?) and after that i see you join this with a "From Participant_T PT". I cannot understand this one. What would be my joined table as i think i am joining this as your PartContact_V pc (so PartContact_V pc = all the tables i join "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 .....etc) so is this table needed in?
Thanks.
Last edited by sapator; Dec 4th, 2014 at 05:48 PM.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 5th, 2014, 03:20 AM
#12
Re: pivot specific columns
Ok, i officially don't get this one bit.
Code:
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 * from #PL
drop table #PL
I've cut down some fields. so what i get is:
Session_lngSessionId ProvSeq weekd Cinema_strname showdate timein
81794 1 27.11.2014 - 03.12.2014 Cinema 27 Nov 2014 19:00
81795 1 27.11.2014 - 03.12.2014 Cinema 27 Nov 2014 21:20
81796 1 27.11.2014 - 03.12.2014 Cinema 27 Nov 2014 23:40
81797 1 27.11.2014 - 03.12.2014 Cinema 28 Nov 2014 19:00
81798 1 27.11.2014 - 03.12.2014 Cinema 28 Nov 2014 21:20
81799 1 27.11.2014 - 03.12.2014 Cinema 28 Nov 2014 23:40
81800 1 27.11.2014 - 03.12.2014 Cinema 29 Nov 2014 16:50
81801 1 27.11.2014 - 03.12.2014 Cinema 29 Nov 2014 19:00
81802 1 27.11.2014 - 03.12.2014 Cinema 29 Nov 2014 21:20
81803 1 27.11.2014 - 03.12.2014 Cinema 29 Nov 2014 23:40
81804 1 27.11.2014 - 03.12.2014 Cinema 30 Nov 2014 16:50
81805 1 27.11.2014 - 03.12.2014 Cinema 30 Nov 2014 19:00
So i just putted 1's in every column. If this is correct then why don't i just created a column manually inserted '1' to that column?
Also, so what now???
Thanks.
P.S. Also note the date. there are rows with the same date so the date should be merged to one single column also.Does it need something extra?
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 5th, 2014, 03:30 AM
#13
Re: pivot specific columns
I Am guessing this?
But i will do nothing, still show the extra rows:
Code:
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
-- ,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 ,
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
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 5th, 2014, 03:51 AM
#14
Re: pivot specific columns
This will bring null,null,null etc in time rows. Also even if we can fix this there are extra rows as there is the date row.
This is getting worst and worst
Code:
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
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 8th, 2014, 11:02 AM
#15
Re: pivot specific columns
As i could not do it the way you suggested i did a double cursor loop da loop to fix this.
Seems pity since your solution seemed promising and only 20-30 rows but with the cursors i had to go to 300+ rows. :/
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 8th, 2014, 11:03 AM
#16
Re: pivot specific columns
Sorry - I've been real busy. If you had some scripts that I could pop into a query window to make some temp tables with your data I could show you more - just hard to make all that test data myself...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|