Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    40

    Unanswered: Need Query Suggestion: Vertical Table to Horizontal

    I have a vertical table that I need to convert to a horizontal table for reporting purposes. I do not want the column tied to any particular field. I can't think of a way to do this with a crosstab because the column must be set to "group by." I'm drawing a blank on how to do this...

    Example of Vertical Table

    100 ABC
    100 LMN
    100 XYZ
    101 RST
    101 XYZ
    102 DEF
    103 XYZ
    103 ABC

    What I want:

    100 ABC LMN XYZ
    101 RST XYZ
    102 DEF
    103 XYZ ABC

    Any ideas?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This is SQL Server...but you should be able to convert it.

    http://weblogs.sqlteam.com/brettk/ar...1/05/3946.aspx
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2005
    Location
    Peoria, IL, USA
    Posts
    9
    If you only need to do this for a report, there's a way to build the report with the data layout you indicated without concatenating.

    First, create a subreport with your ABC/XYZ field in the detail area. (The query this is based on needs to have the 100/102 field in it as well, even though it isn't used in the report.) Take out all the "extra" space so that the report is sized to your ABC/XYZ field. Then go to the Columns tab under File --> Page Setup and select the number of columns, column spacing, etc. Make sure that Column Layout is set to Across, then Down. Save the subreport.

    Create your main report, using a header for the 100/102 field, but put nothing in this header -- put the 100/102 field under the detail section on the far left. On the right, insert your subform, linking it by the 100/102 field -- Size it so that it is the same height as your 100/102 field.

    I hope this makes sense to you. Your initial try may need some fiddling to get the number of columns and field sizes to look right.

    Regards,

    Bonnie

  4. #4
    Join Date
    Jan 2005
    Posts
    31
    Quote Originally Posted by beersnob
    I have a vertical table that I need to convert to a horizontal table for reporting purposes. I do not want the column tied to any particular field. I can't think of a way to do this with a crosstab because the column must be set to "group by." I'm drawing a blank on how to do this...

    <snip>

    Any ideas?
    Code:
    Sample Table:
    
    CREATE TABLE VerticalTable
    (VerticalTableID           AUTOINCREMENT
    ,Col1                      LONG
    ,Col2                      TEXT(3)
    ,CONSTRAINT pk_VerticalTable PRIMARY KEY (VerticalTableID)
    )
    
    Loaded with provided sample data:
    
    
    The Query:
    
    TRANSFORM MAX(VT1.Col2)
       SELECT VT1.Col1
         FROM VerticalTable AS VT1
     GROUP BY VT1.Col1
        PIVOT VT1.Col2
    
    This produces the results you want, but not exactly formatted the way you want.
    
    
    Col1   ABC   DEF   LMN   RST   XZY
    100    ABC         LMN         XYZ
    101                      RST   XYZ
    102          DEF
    103    ABC                     XYZ

Posting Permissions

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