Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Posts
    1

    Unanswered: How to convert verical table to horizontal table?

    Ive a vertical table as follows: CMDATA

    ID FormID RecordCount FieldName Value
    1 525 1204 Name John
    2 588 1204 Organization GE
    3 525 1257 Name Peter
    4 588 1257 Organization Intel
    5 525 1272 Name Rita

    And a horizontal table as follows: CMFORM

    ID FormID RecordCount
    1 525 1204
    2 525 1257
    3 525 1272
    4 588 1204
    5 588 1257

    Where
    CMDATA.FormID = CMFORM.FormID and
    CMDATA.RecordCount = CMFORM.RecordCount

    Now I want select records as follows:

    RecordCount Name Organization
    1204 John GE
    1257 Peter Intel
    1272 Rita NULL

    Ive written following query for that but it works properly only if all the record count has similar fieldnames.

    SELECT CMCF.RecordCount,
    CMD1.VALUE AS Name,
    CMD2.VALUE AS Organization
    FROM CMDATA CMD1
    JOIN CMFORMS CMCF ON CMD1.FORMID = CMCF.FORMID AND CMD1.RECORDCOUNT = CMCF.RECORDCOUNT
    JOIN CMDATA CMD2 ON CMD2.FORMID = CMCF.FORMID AND CMD2.RECORDCOUNT = CMCF.RECORDCOUNT
    WHERE CMD1.FIELDNAME = 'Name'
    AND CMD2.FIELDNAME = 'Organization'
    AND CMCF.RecordCount in(1204,1257,1272)

    Is there any other way to handle this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by supriyasalvi
    Is there any other way to handle this?
    yes, there is

    you have a table with FieldName and Value

    the solution: don't do that

    see
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2004
    Location
    Columbus, OH
    Posts
    59
    Well.....if you have to do something...

    Create a stored procedure. In this SP create a temporary table with the columns that you need. Now, just update the temporary table with the proper data and Wang-Chung you have taken vertical data and made it horizontal.

    Yes, I realize this method sucks and it really only works well when you are creating SP's for reports and such where you pass parameters so you can pull a block of data and not everything under the sun.

    The thing is....no one really likes a EAV data system until they need a EAV data system.
    Life....Just another opportunity to live another day like a pirate....

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    naaah. i always hate'em anyways.
    If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •