Post reply

Summarizing Question

  • February 24, 2006 at 12:56 am

    #103097

    Hi Guys. I was wondering if you could help me with a little problem.Im sure it isnt that big of a deal but sometimes you just get stuck with the most simplest of things. For this example I will use made up information.

    My question concerns summarization of a result set that I have. I have the following result set.

    NAME      SURNAME      REF      AMOUNT     DATE

    Joe         Blogs            2333     500           01/01/2006

    Joe         Blogs            2333     500           01/01/2006

    Joe         Blogs            2333     600           01/01/2006

    Jane        Doe             1000     100           01/01/2005

    Jane        Doe             1000     200           01/01/2005

     

    Now what I want to do in SQL is the following. It should summarize the top information to only include this

    NAME      SURNAME      REF      AMOUNT     DATE

    Joe         Blogs           

                                    2333     500           01/01/2006

                                    2333     500           01/01/2006

                                    2333     600           01/01/2006

    Jane        Doe

                                    1000     100           01/01/2005

                                    1000     200           01/01/2005

     

    Is this possible?

  • February 24, 2006 at 1:11 am

    #623125

    I guess so Try something like this:

     

    declare @tbl table(Name varchar(100), Surname varchar(100), Ref int, Amount int, Date datetime)

    insert @tbl select 'Joe',         'Blogs',            2333,     500,           '01/01/2006'

    insert @tbl select 'Joe',        'Blogs',            2333,     500,           '01/01/2006'

    insert @tbl select 'Joe',         'Blogs',            2333,     600,           '01/01/2006'

    insert @tbl select 'Jane',        'Doe',             1000,     100,           '01/01/2005'

    insert @tbl select 'Jane',        'Doe',             1000,     200,           '01/01/2005'

    select dt.Name, dt.SurName, dt.Ref, dt.Amount, dt.Date

    from

    (

    select distinct Name as sortCol, 0 as Type, Name, SurName, '' as Ref, '' as Amount, '' as Date from @tbl

    union all

    select Name as sortCol, 1, '', '', cast(Ref as varchar), cast(Amount as varchar), convert(varchar, Date, 103) from @tbl

    )

    dt

    order by dt.sortCol, dt.Type

  • February 24, 2006 at 7:08 am

    #623186

    Or better if you are using SQL 2000 or later download and install reporting services to build this type of report for you. Although you can do as described it really is better handled by a reporting tool than by trying to perform this way, especially if you have a lot of records to work with.

  • February 24, 2006 at 9:17 am

    #623246

    It is a good idea not to try and do data layout in a stored procedure or SQL statement.  You should return the data as a set and have some application, Reporting Services works fine, or some custom web page display the data?

    Are the users that need the report formatted this way actually using Query Analyzer to retrieve their report?

  • February 26, 2006 at 8:57 pm

    #623387

    I agree but...

    I've found that when the "boss", who doesn't really know how to use a spreadsheet, says he wants to open the result set using Excel from a CSV file and have it "formatted" the way he wants it, will cause even the staunchest advocates of external formatting tools to do it in SQL.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • February 28, 2006 at 7:48 am

    #623686

    But reporting services can output to excel.

  • March 2, 2006 at 2:57 am

    #624226

    Thank you for all your suggestions! Ill try the Report Services method.

    Im totally new to SQL 2005 and all the little funky programs to go with it, but im learning slowly

    Also while on topic, how can I edit a result set in 2005 Query Analyser ? A friend of mine can just write a query and then edit the result set, however I am not able to do so.

  • March 2, 2006 at 4:59 am

    #624248

    Ah yes, absolutely true... I sometimes just forget about things I'm not allowed to use and, unfortunately, I work in a shop where .Net is not allowed.  Don't know why that is but they don't allow it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • March 2, 2006 at 5:40 am

    #624255

    If you set "Results in Text" in QA (menu Query), you can edit the resultset. However, I'm not sure how that helps you. I'd definitely prefer to save the result in a file first, and edit that file, if editing is necessary.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply