Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Unanswered: TRANSFORM in SQL

    in MS access I can Do this

    TRANSFORM Sum([Edits vs New].cc) AS TTcc
    SELECT [Edits vs New].MM, [Edits vs New].YY
    FROM [Edits vs New]
    GROUP BY [Edits vs New].MM, [Edits vs New].YY
    PIVOT [Edits vs New].TT;

    what the SQL 2005 verson of it Please

    come up with a error Incorrect syntax near Prvot
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    PIVOT. However, this cannot handle variable numbers of columns in the result set. To do that, you need dynamic SQL.

    Essentially, TRANSFORM has no relational sense to it. A SQL statement should return a predictable resultset in the form of a table. As such, this sort of thing is normally eschewed by DBAs and database developers and all pivoting is done at the presentation layer.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Access is an application development tool. SQL Server is not. TRANSFORM is for cross-tab reports and that kind of reporting can be done in whatever presentation tier / reporting tool you are using rather than in T-SQL.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Bugger
    I want to do the TRANSFORM and put the data on web pages have to rethink the idea

    thanks for the input
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by myle
    I want to do the TRANSFORM and put the data on web pages have to rethink the idea
    What's the language? Reporting Services have matrixes. One blogger I read has an article on pivotting in asp.net. You won't be the first
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Thanks pootle

    will be creating bar charts with the Data

    just found this

    http://weblogs.sqlteam.com/jeffs/arc...5/02/4842.aspx

    and
    http://blogs.msdn.com/craigfr/archiv...-operator.aspx
    and
    http://www.dotnetspider.com/resource...QL-Server.aspx

    will be play with it to nite
    Last edited by myle; 01-22-09 at 19:58.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, the .NET article I mentioned is one of Jeff's. Here's another of his: .NET CrossTabs versus SQL Server CrossTabs
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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