Post reply

select data and display in different coulumns

  • June 23, 2008 at 11:43 am

    #187400

    I have four tables

    customer(pk_Customerid,name,address,fk_phonetypeid)

    customerphone(pk_customerphoneid,fk_customerid,fk_phoneid)

    phone(pk_phoneid,fk_phonetypeid,phone)

    phonetype(pk_phonetypeid)

    phonetypeid : 1,2,3

    where 1 represents mobile,2 represent landline and 3 represents others or fax.

    In base table phone - phone,landline,fax are stored in the single column ie; phone and are identified by phonetypeid column, but for display i want those numbers to be in different columns.

    Now i want to write a stored procedure which retrieve the customer details like:

    customerid name mobile landline others or fax

    ----------------------------------------------------------

    based on input parameter @customerid

    Help Me!!!

  • June 24, 2008 at 8:58 am

    #833367

    kiran (6/23/2008)

    customer(pk_Customerid,name,address,fk_phonetypeid)

    customerphone(pk_customerphoneid,fk_customerid,fk_phoneid)

    phone(pk_phoneid,fk_phonetypeid,phone)

    phonetype(pk_phonetypeid)

    The structure of these tables isn't exactly the best... either fk_phonetypeid should be in customerphone, or there should be a master customer table. Otherwise you have to operate based on the name being the Identifier of a customer, and it's just not good.

    See normal forms or Domain Key Normal Form on how to correct.

    --How to do it with the structure above (without actual create statements I can't really test this)

    SELECT DISTINCT

    name,

    address

    INTO #customers

    SELECT

    a.name,

    a.address,

    mobilephone = d.phone,

    landline = f.phone,

    otherorfax = h.phone

    FROM

    #customers a JOIN customer b ON

    a.name = b.name and

    a.address = b.address

    LEFT JOIN customerphone c ON

    b.pk_customerid = c.fk_customerid

    JOIN phone d ON

    c.fk_phoneid = d.pk_phoneid and

    d.fk_phonetypeid = 1

    LEFT JOIN customerphone e ON

    b.pk_customerid = e.fk_customerid

    JOIN phone f ON

    e.fk_phoneid = f.pk_phoneid and

    f.fk_phonetypeid = 2

    LEFT JOIN customerphone g ON

    b.pk_customerid = g.fk_customerid

    JOIN phone h ON

    g.fk_phoneid = h.pk_phoneid and

    h.fk_phonetypeid = 3

    How I'd correct it

    CREATE TABLE customer (

    customer_id INTEGER IDENTITY(1,1) PRIMARY KEY,

    customer_name VARCHAR(120),

    customer_address VARCHAR(250),

    )

    CREATE TABLE phonetype(

    phonetypeid tinyint IDENTITY(1,1) PRIMARY KEY,

    phonetype_desc varchar(50) NOT NULL)

    CREATE TABLE customer_phone(

    cust_ph_id INTEGER IDENTITY(1,1) PRIMARY KEY,

    cust_ph_type TINYINT FOREIGN KEY REFERENCES phonetype(phonetypeid) NOT NULL,

    cust_ph_num VARCHAR(20) NOT NULL,

    customer_id INTEGER FOREIGN KEY REFERENCES customer(customer_id) NOT NULL

    )

    /* Sample Data */

    INSERT INTO PHONETYPE(phonetype_desc) VALUES('Mobile')

    INSERT INTO PHONETYPE(phonetype_desc) VALUES('Land Line')

    INSERT INTO PHONETYPE(phonetype_desc) VALUES('Fax or Other')

    INSERT INTO CUSTOMER(customer_name,customer_address) VALUES ('Information','1 Somewhere lane Some City, SW ?????')

    INSERT INTO CUSTOMER_PHONE (cust_ph_type,cust_ph_num,customer_id) VALUES(1,'800-555-1212',1)

    INSERT INTO CUSTOMER_PHONE (cust_ph_type,cust_ph_num,customer_id) VALUES(2,'888-555-1212',1)

    INSERT INTO CUSTOMER_PHONE (cust_ph_type,cust_ph_num,customer_id) VALUES(3,'877-555-1212',1)

    /* Query Below*/

    SELECT

    a.customer_name,

    b.cust_ph_num,

    c.cust_ph_num,

    d.cust_ph_num

    FROM

    customer a LEFT JOIN customer_phone b ON

    a.customer_id = b.customer_id

    JOIN phonetype b1 ON

    b.cust_ph_type = b1.phonetypeid

    and b1.phonetype_desc = 'Mobile'

    LEFT JOIN customer_phone c ON

    a.customer_id = c.customer_id

    JOIN phonetype c1 ON

    c.cust_ph_type = c1.phonetypeid

    and c1.phonetype_desc = 'Land Line'

    LEFT JOIN customer_phone d ON

    a.customer_id = d.customer_id

    JOIN phonetype d1 ON

    d.cust_ph_type = d1.phonetypeid

    and d1.phonetype_desc = 'Fax or Other'


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • June 24, 2008 at 9:28 am

    #833386

    Thank You!

  • June 24, 2008 at 3:38 pm

    #833620

    Hey Mark! Nice avatar!!!! 😀

    --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)
    Intro to Tally Tables and Functions

  • June 25, 2008 at 7:41 am

    #833951

    Thanks! 🙂


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • June 25, 2008 at 12:51 pm

    #834214

    Hi,

    In simnple way you can write queries with the Group by Clause and CASE statements.

    SELECT c.fk_customerid,

    MAX(DECODE (pfk_phonetypeid,1, phone) AS mobile_Phone,

    MAX(DECODE (pfk_phonetypeid,2, phone) AS LandLine_Phone,

    MAX(DECODE (pfk_phonetypeid,3, phone) AS Fax_Phone,

    FROM customerphone C,PHONE P

    where c.fk_phoneid = p.pk_phoneid

    group by c.fk_customerid

    DECODE is in Oracle, you can use CASE in SQL Server to achive it.

    Thanks -- Vj

    http://dotnetvj.blogspot.com

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


  • June 25, 2008 at 5:08 pm

    #834341

    Since Oracle 8.3, you also can use Case... 😉

    --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)
    Intro to Tally Tables and Functions

  • June 26, 2008 at 10:46 am

    #834843

    I actually have a similar question. I have a table that contains customer_id, customer_Fname, Contact_date, Contact_type. In running a query showing all contact dates / times for each customer_id, the result will be all customer_id with no contact displaying null values but those with multiple contacts will produce multiple rows for each contact_date/Contact_type.

    example: Customer_id, Contact_date, Contact_type

    01234 10/10/2008 phone call

    01234 10/15/2008 interview

    23456 09/01/2007 face2face

    23456 09/28/2008 phone call

    My goals is to create a report showing each instance of customer contact_type/date as an individual column heading.

    Example

    Customer_id, Contact_date01, Contact_type01, Contact_date02, Contact_type02,.....

    01234 10/10/2008 phone call 10/15/2008 interview

    23456 09/01/2007 face2face 09/28/2008 phone call

    Any thoughts would be appreciated.

    JT

  • June 26, 2008 at 5:32 pm

    #835048

    jthomastx (6/26/2008)

    I actually have a similar question. I have a table that contains customer_id, customer_Fname, Contact_date, Contact_type. In running a query showing all contact dates / times for each customer_id, the result will be all customer_id with no contact displaying null values but those with multiple contacts will produce multiple rows for each contact_date/Contact_type.

    example: Customer_id, Contact_date, Contact_type

    01234 10/10/2008 phone call

    01234 10/15/2008 interview

    23456 09/01/2007 face2face

    23456 09/28/2008 phone call

    My goals is to create a report showing each instance of customer contact_type/date as an individual column heading.

    Example

    Customer_id, Contact_date01, Contact_type01, Contact_date02, Contact_type02,.....

    01234 10/10/2008 phone call 10/15/2008 interview

    23456 09/01/2007 face2face 09/28/2008 phone call

    Any thoughts would be appreciated.

    JT

    See the following thread... well worth the read... many folks contributed to something that is article worthy for both SQL Server 2000 and 2005... in a nutshell, Cross Tabs win over pivots.

    http://qa.sqlservercentral.com/Forums/Topic521489-338-1.aspx

    --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)
    Intro to Tally Tables and Functions

  • June 26, 2008 at 6:39 pm

    #835065

    Jeff,

    I appreciate the article. Will spend the next hour reviewing all the different methods. Excellent - Thank you.

    JT

  • June 27, 2008 at 3:25 pm

    #835647

    Its nice one.

    Thanks -- Vj

    Thanks -- Vijaya Kadiyala
    www.dotnetvj.com
    SQL Server Articles For Beginers


Viewing 11 posts - 1 through 10 (of 10 total)

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