Post reply

query assistance

  • January 6, 2012 at 9:47 am

    #253006

    I am creating a view to display data from several tables. Need some assistance with figuring out how to do the following:

    Table 'Client'

    Fields:ClientID, Firstname, Lastname

    Table 'Telephone'

    Fields:TelephoneID, ClientID, Number, Type (where Type is 'Home', 'Office', 'Fax', 'Cell')

    The view needs to display FirstName, LastName, OfficePhone, HomePhone, CellNumber, FaxNumber

    I'm not sure how to write the query to filter by Type and get the correct phone numbers in the view.

    Thanks

  • January 6, 2012 at 9:51 am

    #1429727

    WHERE Type IN ( 'Home', 'Office', 'Fax', 'Cell')

  • January 6, 2012 at 11:18 am

    #1429769

    I can use the Where IN clause to filter results for just Home, Office, Fax, and Cell. This will still return a row for each record in the Telephone table.

    What I'd really like to do though is something like this:

    Table Client

    ClientID FirstName LastName

    1 Jeff Jones

    Table Telephone

    TelephoneID ClientID Number Type

    1 1 1234567890 Home

    2 1 1234567891 Office

    3 1 1234567892 Cell

    4 1 1234567893 Fax

    View results:

    FirstName LastName HomePhone OfficePhone CellNumber FaxNumber

    Jeff Jones 1234567890 1234567891 1234567892 1234567893

    Thanks

  • January 6, 2012 at 11:19 am

    #1429771

    Look up the section on PIVOT in Books Online.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 7, 2012 at 9:34 am

    #1430019

    TK-484813 (1/6/2012)

    I can use the Where IN clause to filter results for just Home, Office, Fax, and Cell. This will still return a row for each record in the Telephone table.

    What I'd really like to do though is something like this:

    Table Client

    ClientID FirstName LastName

    1 Jeff Jones

    Table Telephone

    TelephoneID ClientID Number Type

    1 1 1234567890 Home

    2 1 1234567891 Office

    3 1 1234567892 Cell

    4 1 1234567893 Fax

    View results:

    FirstName LastName HomePhone OfficePhone CellNumber FaxNumber

    Jeff Jones 1234567890 1234567891 1234567892 1234567893

    Thanks

    Since you're new to forums, please read the following article for how to get better answers quicker the next time you post...

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    The following code builds some test data for your problem using the methods in the article above so you have another example and so we can demonstrate one solution to this problem...

    --===== Conditionally drop the test tables to make reruns in SSMS easier.

    -- Note that this is not a part of the solution.

    IF OBJECT_ID('tempdb..#Client' ,'U') IS NOT NULL DROP TABLE #Client;

    IF OBJECT_ID('tempdb..#Telephone','U') IS NOT NULL DROP TABLE #Telephone;

    --===== Create and populate the test table for the client data.

    -- Note that this is not a part of the solution.

    CREATE TABLE #Client

    (

    ClientID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    FirstName VARCHAR(30) NOT NULL,

    LastName VARCHAR(30) NOT NULL

    )

    ;

    INSERT INTO #Client

    (FirstName, LastName)

    SELECT 'Jeff' , 'Jones' UNION ALL

    SELECT 'John' , 'Smith' UNION ALL

    SELECT 'Sally', 'Smith'

    ;

    --===== Create and populate the test table for the telephone data.

    -- Note that this is not a part of the solution.

    CREATE TABLE #Telephone

    (

    TelephoneID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    ClientID INT NOT NULL,

    Number CHAR(10) NOT NULL CHECK (Number NOT LIKE '%[^0-9]%'),

    Type CHAR(6) NOT NULL

    )

    ;

    INSERT INTO #Telephone

    (ClientID,Number,Type)

    SELECT 1,'1234567890','Home' UNION ALL

    SELECT 1,'1234567891','Office' UNION ALL

    SELECT 1,'1234567892','Cell' UNION ALL

    SELECT 1,'1234567893','Fax' UNION ALL

    SELECT 2,'2234567890','Home' UNION ALL

    SELECT 2,'2234567891','Office' UNION ALL

    SELECT 2,'2234567892','Cell' UNION ALL

    SELECT 3,'1234567891','Office' UNION ALL

    SELECT 3,'1234567892','Cell' UNION ALL

    SELECT 3,'1234567893','Fax'

    ;

    If you take the time to post your problem like that (has readily consumable test data), folks will climb over each other trying to help you instead of just giving you suggestions.

    Here's one solution that uses the test data above. You'll need to change the table names to match your own...

    --===== Solve the problem using a CROSS TAB

    SELECT FirstName ,

    LastName,

    HomePhone = MAX(CASE WHEN t.Type = 'Home' THEN Number ELSE '' END),

    OfficePhone = MAX(CASE WHEN t.Type = 'Office' THEN Number ELSE '' END),

    CellNumber = MAX(CASE WHEN t.Type = 'Cell' THEN Number ELSE '' END),

    FaxNumber = MAX(CASE WHEN t.Type = 'Fax' THEN Number ELSE '' END)

    FROM #Client c

    LEFT OUTER JOIN #Telephone t

    ON c.ClientID = t.ClientID

    WHERE t.Type IN ('Home','Office','Cell','Fax')

    GROUP BY c.ClientID, FirstName, LastName

    ;

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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