Results 1 to 16 of 16

Thread: pivot specific columns

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  2. #2

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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()

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  12. #12

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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?
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  13. #13

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  14. #14

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  15. #15

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    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. :/
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width