Post reply

Function to list distinct pax name and ticket no?

  • March 14, 2014 at 11:37 am

    #305293

    Hi,

    I have data in a table Item_TB that I need to extract in a way that pulls out the distinct pax name and all the ticket numbers associated with the passenger per booking reference.

    The data is:

    Branch Folder ID Pax TktNo BookingRef

    HQ 123 1 Jim 4444 ABCDE

    HQ 123 2 Bob 5555 ABCDE

    HQ 123 3 Jim 6666 ABCDE

    HQ 123 4 Bob 7777 ABCDE

    HQ 124 1 Jenny 8888 FGHIJ

    HQ 124 2 Jenny 9999 FGHIJ

    HQ 124 3 Jenny 3333 FGHIJ

    I somehow need to get a function to pull the data out for each booking ref like so

    --BookingRef ABCDE

    Jim 4444/

    6666

    Bob 5555

    7777

    --BookingRef FGHIJ

    Jenny 8888/

    9999/

    3333

    I know I can get a simple function to return the all data, but I do not know how to only include the pax name once.

    Thanks in advance...

    -James

  • March 14, 2014 at 11:43 am

    #1697236

    Can you post your sample data and expected results in the form of DDL and INSERT statements?

    It looks like you need something like this: http://www.sqlservercentral.com/articles/comma+separated+list/71700/

    But if you post your data as I asked, it will become clear and I'll be happy to help you with a coded example.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 14, 2014 at 1:19 pm

    #1697281

    Thanks!

    Here you go...

    CREATE TABLE DBA.Items(

    strBBranchCode_FD CHAR(2) NOT NULL DEFAULT '',

    lFFoldNo_FD INT NOT NULL DEFAULT -1,

    nFiFoldItemID_FD SMALLINT NOT NULL DEFAULT -1,

    strFiPaxName_FD CHAR(100) NOT NULL,

    strFiTktNo_FD CHAR(100) NOT NULL,

    strFiBookingRef_FD CHAR(100) NOT NULL,

    PRIMARY KEY (strBBranchCode_FD, lFFoldNo_FD, nFiFoldItemID_FD )

    )

    GO

    INSERT INTO DBA.Items VALUES('HQ',123,1,'Jim','4444','ABCDE')

    INSERT INTO DBA.Items VALUES('HQ',123,2,'Bob','5555','ABCDE')

    INSERT INTO DBA.Items VALUES('HQ',123,3,'Jim','6666','ABCDE')

    INSERT INTO DBA.Items VALUES('HQ',123,4,'Bob','7777','ABCDE')

    INSERT INTO DBA.Items VALUES('HQ',124,1,'Jenny','8888','FGHIJ')

    INSERT INTO DBA.Items VALUES('HQ',124,2,'Jenny','9999','FGHIJ')

    INSERT INTO DBA.Items VALUES('HQ',124,3,'Jenny','3333','FGHIJ')

    GO

  • March 14, 2014 at 1:35 pm

    #1697288

    You didn't post any expected results.

    However, here are two options you could use depending on what you want.

    DECLARE @BookRef char(100) = 'ABCDE'

    SELECT strFiPaxName_FD

    ,STUFF((SELECT '/' + LTRIM(RTRIM(strFiTktNo_FD))

    FROM #Items x

    WHERE x.strFiPaxName_FD = i.strFiPaxName_FD

    AND x.strFiBookingRef_FD = i.strFiBookingRef_FD

    ORDER BY nFiFoldItemID_FD

    FOR XML PATH('')), 1, 1, '')

    FROM #Items i

    WHERE strFiBookingRef_FD = @BookRef

    GROUP BY strFiPaxName_FD, strFiBookingRef_FD

    SELECT CASE WHEN ROW_NUMBER() OVER(PARTITION BY strFiPaxName_FD ORDER BY nFiFoldItemID_FD) = 1

    THEN strFiPaxName_FD ELSE '' END strFiPaxName_FD

    ,strFiTktNo_FD

    FROM #Items

    WHERE strFiBookingRef_FD = @BookRef

    Please ask any questions that you have.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 14, 2014 at 1:46 pm

    #1697293

    Wow thanks Luis!!

    I like both, but will probably use the FOR XML option!

    -James

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply