locked
using coalesce function RRS feed

  • Question

  • Hell team,

    I want to write a query that gives me the result as this:

    Get the first name and last name for all members.

    Create a column titles: "Contact info". If the member has a phone number provided list that in the contact info column. if the member has no phone number, list the email address, if the member has neither to display 'preference update needed'.

    I appreciate your response.

    CloudsInSky


    CloudsInSky

    Monday, February 26, 2018 2:56 AM

Answers

  • Coalesce('phonenumber', 'email ', 'preference update needed'.) as  'Contact info'




    • Edited by Jingyang Li Monday, February 26, 2018 3:12 AM
    • Marked as answer by cloudsInSky Monday, February 26, 2018 6:16 AM
    Monday, February 26, 2018 3:10 AM
  • SELECT FirstName,
    LastName,
    COALESCE(CAST(Phonenumber AS varchar(50)),Email,'Preference Update Needed') AS ContactInfo
    FROM TableName

    Assuming Phonenumber, Email etc are available as separate columns in your table

    In case they're stored as attributes with attribute type column, you've to first pivot them onto different columns before you apply the above logic


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by cloudsInSky Monday, February 26, 2018 6:17 AM
    Monday, February 26, 2018 5:24 AM

All replies

  • Coalesce('phonenumber', 'email ', 'preference update needed'.) as  'Contact info'




    • Edited by Jingyang Li Monday, February 26, 2018 3:12 AM
    • Marked as answer by cloudsInSky Monday, February 26, 2018 6:16 AM
    Monday, February 26, 2018 3:10 AM
  • SELECT FirstName,
    LastName,
    COALESCE(CAST(Phonenumber AS varchar(50)),Email,'Preference Update Needed') AS ContactInfo
    FROM TableName

    Assuming Phonenumber, Email etc are available as separate columns in your table

    In case they're stored as attributes with attribute type column, you've to first pivot them onto different columns before you apply the above logic


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by cloudsInSky Monday, February 26, 2018 6:17 AM
    Monday, February 26, 2018 5:24 AM
  • Hello Visakh16,

    Why should I pivot them? 

    I have used coalesce function and it has brought back the result.

    Regards,

    CloudsInSky


    CloudsInSky

    Monday, February 26, 2018 6:22 AM
  • Hello Visakh16,

    Why should I pivot them? 

    I have used coalesce function and it has brought back the result.

    Regards,

    CloudsInSky


    CloudsInSky

    Please read the complete sentence :)

    The pivoting is required only if the phonenumner, email etc values are stored as attributes

    which is what i've told in the sentence below

    In case they're stored as attributes with attribute type column, you've to first pivot them onto different columns before you apply the above logic


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 26, 2018 6:40 AM
  • Visakh16

    -The pivoting is required only if the phonenumner, email etc values are stored as attributes

    Do you mean that they are field header?

    -In case they're stored as attributes with attribute type column, you've to first pivot them onto different columns before you apply the above logic

    Can you please illustrate what you say?

    Regards,

    CloudsInSky


    CloudsInSky

    Monday, February 26, 2018 7:34 AM
  • Visakh16

    -The pivoting is required only if the phonenumner, email etc values are stored as attributes

    Do you mean that they are field header?

    -In case they're stored as attributes with attribute type column, you've to first pivot them onto different columns before you apply the above logic

    Can you please illustrate what you say?

    Regards,

    CloudsInSky


    CloudsInSky

    I meant EAV model

    i.e both Attribute types (ex: Phonenumber,Mobile) as well as their values (ex: 045339375, 9976875787) are stored as column values within two columns of a generic lookup/attribute table


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 26, 2018 7:37 AM
  • Visakh16,

    I need more explanation.

    Do you mean we have two columns/ fields/ attributes which contain two values (one phone number and one cell phone number)?

    How is that possible that the field header and values of the field to be mixed in together?

    If yes, I don't know how pivoting take care of this?

    Regards,

    CloudsInSky<sub></sub><sup></sup><strike></strike>


    CloudsInSky

    Monday, February 26, 2018 7:48 AM
  • Visakh16,

    I need more explanation.

    Do you mean we have two columns/ fields/ attributes which contain two values (one phone number and one cell phone number)?

    How is that possible that the field header and values of the field to be mixed in together?

    If yes, I don't know how pivoting take care of this?

    Regards,

    CloudsInSky<sub></sub><sup></sup><strike></strike>


    CloudsInSky

    Read about Entity Attribute Model and you will get an idea

    It cant be explained descriptively within scope of a forum like this

    https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 26, 2018 8:16 AM
  • Visakh16,

    I read it, but I didn't catch it. Do you mean parent and child table/ PK and FK?

    Thanks,

    CloudsInSky


    CloudsInSky

    Wednesday, February 28, 2018 4:05 AM
  • Visakh16,

    I read it, but I didn't catch it. Do you mean parent and child table/ PK and FK?

    Thanks,

    CloudsInSky


    CloudsInSky

    yes

    child table will have two columns one storing the type and other storing the value

    types will be phonenumber, email etc and values will have their corresponding values


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 28, 2018 5:22 AM
  • Visakh16,

    Now I feel better, one is the attribute and one is the values (contents) for that attribute; in this context, we determine data type for that attribute.

    Regards,

    CloudsInSky


    CloudsInSky

    Wednesday, February 28, 2018 5:42 AM
  • Visakh16,

    Now I feel better, one is the attribute and one is the values (contents) for that attribute; in this context, we determine data type for that attribute.

    Regards,

    CloudsInSky


    CloudsInSky

    yes

    datatype would be set as a generic one (mostly varchar or nvarchar) to ensure it can hold all types of values integer,decimal, varchar, dates etc

    Another option is to use sql_variant datatype.


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 28, 2018 5:45 AM
  • Visakh16,

    Then how is that related to pivoting data?

    Regards,

    CloudsInSky


    CloudsInSky

    Wednesday, February 28, 2018 6:43 AM
  • Visakh16,

    Then how is that related to pivoting data?

    Regards,

    CloudsInSky


    CloudsInSky

    Didnt understand your question

    Without pivoting how do you think you will get them onto different columns?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, February 28, 2018 7:21 AM