Professional Web Applications Themes

An easier way to Transpose? - Microsoft SQL / MS SQL Server

I was wondering if their is an easier way to do a simple rotating of a column into a row. I looked through past post and most people recomend cross-tab queries or Pivot tables, but I don't think that gives me what I want. I have two columns (Date, Value1) and I want to return one row with 24 columns (one per hour). Currently I do it with a nested select (i.e. SELECT (SELECT Value1 WHERE Date = 01:00) as 1, (SELECT Vaule1 WHERE Date = 02:00) as Col2 ...) It gets a bit messy though, if I add a ...

  1. #1

    Default An easier way to Transpose?

    I was wondering if their is an easier way to do a simple rotating of a
    column into a row. I looked through past post and most people recomend
    cross-tab queries or Pivot tables, but I don't think that gives me what I
    want.
    I have two columns (Date, Value1) and I want to return one row with 24
    columns (one per hour). Currently I do it with a nested select (i.e. SELECT
    (SELECT Value1 WHERE Date = 01:00) as 1, (SELECT Vaule1 WHERE Date = 02:00)
    as Col2 ...)
    It gets a bit messy though, if I add a third column (Date, Vaule1, Value2)
    and I want one row of 24 columns of Value 1 and the next row to be Value2.
    Is this the same thing as a Pivot table and I just dont see it, or is there
    a simple way to flip a row into a column that I've missed?
    Thanks,
    Travis


    Travis Guest

  2. #2

    Default Re: An easier way to Transpose?

    You can check out certain 3rd party utilities like RAC (www.rac4sql.net) for
    such client-side formatting functionalities.

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  3. #3

    Default Re: An easier way to Transpose?

    Thanks. Yeah. I was hoping for something "easier". Right now installing a
    third part solution is a bit more complex than the nested select I'm using!
    I was just hoping for maybe a cleaner SQL statement that I had missed.
    thanks though,
    Travis



    "Anith Sen" <com> wrote in message
    news:phx.gbl... 
    for 


    Travis Guest

  4. #4

    Default Re: An easier way to Transpose?

    If the number of columns are not known upfront, you'll have to use Dynamic
    SQL with a string built with CASE expressions based on the number of items.
    The following link may help you :
    http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

    --
    - Anith
    ( Please reply to newsgroups only )


    Anith Guest

  5. #5

    Default Re: An easier way to Transpose?

    And "schemata" is the plural of schema, NOT schemas!
    And never start a sentence with "And".

    Do you want to recomend any report writers?

    thanks,
    Travis


    "Joe Celko" <edu> wrote in message
    news:%phx.gbl... [/ref]
    > or Pivot tables,... <<
    >
    > Yep, and it drives me nuts to read that; a cross tab is a report, not a
    > query; a pivot table is not a table (what entity or relationship does it
    > model?) and the term "pivot" is something that a guy who never took a
    > statistics class made up at microsoft becuase he did not know the term
    > cross tab.
    >
    > Anyway, the answer is that SQL is not a report writer; it is a data
    > retrieval language. Use a report writer for reports. Anything else is
    > a kludge.
    >
    > --CELKO--
    > ===========================
    > Please post DDL, so that people do not have to guess what the keys,
    > constraints, Declarative Referential Integrity, datatypes, etc. in your
    > schema are.
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it![/ref]


    Travis Guest

Similar Threads

  1. pivot/transpose report
    By susanring in forum Coldfusion Database Access
    Replies: 8
    Last Post: December 8th, 03:38 AM
  2. DataGrid transpose
    By Miguel Ramirez in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: May 4th, 05:32 PM
  3. [PHP] Is there an easier way?
    By David Nicholson in forum PHP Development
    Replies: 0
    Last Post: August 1st, 01:08 AM
  4. Is there an easier way?
    By Ryan A in forum PHP Development
    Replies: 0
    Last Post: August 1st, 01:03 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139