SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Transposing table

    Hi,

    I'm having problems doing one thing in SQL.. I have a table with this data:

    Code:
    Col1   | Col2
    ---------------
    Name1  | Value1
    Name2  | Value2
    Name3  | Value3
    ...    | ...
    ...    | ...
    What I want to have in the end is:

    Code:
    NameCol1 | ValueCol1 | NameCol2 | ValueCol2 | NameCol3 | ValueCol3 | ...
    -------------------------------------------------------------------------
    Name1    | Value1    | NAme2    | Value2    | Name3    | Value3    | ...
    The number of records on the table I have is undetermined (it can be 1 or 20 or whatever). The final result should be a table with these records in one single entry. The number of columns in the result table should be the double of the entries on the original table.

    Is there anyway I can do this?

    Thanks in advance...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if this is a one-time fix, to permanently change the structure of the table, my advice is: don't do it

    if this is something you want to do repetitively, then you should be doing it in your front-end application prgram, not in the SQL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    38
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    if this is a one-time fix, to permanently change the structure of the table, my advice is: don't do it

    if this is something you want to do repetitively, then you should be doing it in your front-end application prgram, not in the SQL
    This is for generating reports and it really must be in that output format. I managed to do it by adding columns dynamically and then updating the fields (and renaming the columns after that) but it only works if the table doesn't exist. If it exists it won't even compile...

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by prl View Post
    This is for generating reports ...
    IF { wag(dog.tail) == TRUE } THEN { search(solution.other) }

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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