Post reply

Display the output of table in given format

  • August 19, 2015 at 12:42 am

    #302034

    Hi Expert,

    I have a table Test123 having three column EmpID,AttribName,AttribValue.

    Please run the below query to generate table structure and data.

    Create Table Test123(EmpID int,AttribName varchar(50),AttribValue varchar(50))

    insert into Test123 values(1,'Name','X')

    insert into Test123 values(1,'Age',50)

    insert into Test123 values(1,'Salary',1000)

    insert into Test123 values(2,'Name','Y')

    insert into Test123 values(2,'Age',30)

    insert into Test123 values(2,'Salary',2000)

    insert into Test123 values(3,'Name','Z')

    insert into Test123 values(3,'Age',35)

    insert into Test123 values(3,'Salary','One Hundred')

    And I want output in below format.

    AttributeValueType

    =================

    AGE | NUMERIC

    NAME | ALPHABET

    SALARY | ALPHANUMERIC

    ==================

  • August 19, 2015 at 1:36 am

    #1820955

    Here's a simple one -

    select AttribName AS Attribute

    ,CASE WHEN ISNUMERIC(AttribValue)=1

    THEN 'NUMERIC'

    WHEN LEN(ATTRIBVALUE) = 1 AND ISNUMERIC(AttribValue) = 0

    THEN 'ALPHABET'

    ELSE 'ALPHANUMERIC'

    END

    from #Test123

    ____________________________________________________________

    AP

  • August 19, 2015 at 1:53 am

    #1820956

    dineshhp (8/19/2015)

    Hi Expert,

    I have a table Test123 having three column EmpID,AttribName,AttribValue.

    Please run the below query to generate table structure and data.

    Create Table Test123(EmpID int,AttribName varchar(50),AttribValue varchar(50))

    insert into Test123 values(1,'Name','X')

    insert into Test123 values(1,'Age',50)

    insert into Test123 values(1,'Salary',1000)

    insert into Test123 values(2,'Name','Y')

    insert into Test123 values(2,'Age',30)

    insert into Test123 values(2,'Salary',2000)

    insert into Test123 values(3,'Name','Z')

    insert into Test123 values(3,'Age',35)

    insert into Test123 values(3,'Salary','One Hundred')

    And I want output in below format.

    AttributeValueType

    =================

    AGE | NUMERIC

    NAME | ALPHABET

    SALARY | ALPHANUMERIC

    ==================

    SELECT

    Attribute = UPPER(AttribName),

    ValueType = CASE WHEN AttribName = 'Name' THEN 'ALPHABET' ELSE 'ALPHANUMERIC' END

    FROM #Test123

    WHERE EmpID = 1

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • August 19, 2015 at 2:12 am

    #1820958

    Anshul.P (8/19/2015)

    Here's a simple one -

    select AttribName AS Attribute

    ,CASE WHEN ISNUMERIC(AttribValue)=1

    THEN 'NUMERIC'

    WHEN LEN(ATTRIBVALUE) = 1 AND ISNUMERIC(AttribValue) = 0

    THEN 'ALPHABET'

    ELSE 'ALPHANUMERIC'

    END

    from #Test123

    Hi Anshul,

    But not getting results as I want.It will not handle alphabet case.

  • August 19, 2015 at 7:53 am

    #1821051

    People seem to be forgetting the aggregation. I hope that you're doing this to move out of this horrible design that will only create headaches.

    SELECT AttribName,

    CASE SUM( DISTINCT CASE WHEN AttribValue NOT LIKE '%[^0-9]%' THEN 1 ELSE 2 END)

    WHEN 1 THEN 'NUMERIC'

    WHEN 2 THEN 'ALPHABET'

    WHEN 3 THEN 'ALPHANUMERIC'

    END AS ValueType

    FROM Test123

    GROUP BY AttribName;

    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

  • August 20, 2015 at 7:56 am

    #1821401

    Thanks Luis !!! You have solve my problem.....

  • August 20, 2015 at 10:10 am

    #1821464

    dineshhp (8/20/2015)

    Thanks Luis !!! You have solve my problem.....

    That's great! Do you understand how or why does it work?

    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

Viewing 7 posts - 1 through 6 (of 6 total)

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