Summarizing Question

Question

Post reply

Summarizing Question

MegaCale

SSC Enthusiast

Points: 118
More actions
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?

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

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

Jesper-244176 SSCertifiable Points: 7142 More actions · Answer 1

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, 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

Antares686 SSC Guru Points: 125444 More actions · Answer 2

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.

JeffB Hall of Fame Points: 3541 More actions · Answer 3

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?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

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)

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Antares686

SSC Guru

Points: 125444

February 28, 2006 at 7:48 am

#623686

But reporting services can output to excel.

MegaCale SSC Enthusiast Points: 118 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

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)

Vladan SSC-Insane Points: 22072 More actions · Answer 8

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.