AS400 Query

I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this?? see example
ATEISO SOLDTO SHIPTO CABBV DABBV TO
007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
TOTAL 46,085.9
i want
007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.com
Of course this is a Microsoft SQL Server newsgroup, but the SQL needed
for this is pretty simple.
SELECT ATEISO, SOLDTO, SHIPTO, CABBV, DABBV,
SUM(TO) as TotalTO
FROM Whatever
GROUP BY ATEISO, SOLDTO, SHIPTO, CABBV, DABBV
That assumes that you know and apply whatever the workaround is in
your dialect of SQL for a column name (TO) that is a SQL reserved
word. Or maybe it is not reserved in your dialect.
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 16:11:53 GMT, Kathy
Goldie<kathy.goldie@.maps-na.com> wrote:

>I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this?? see example
> ATEISO SOLDTO SHIPTO CABBV DABBV TO
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
> TOTAL 46,085.9
>i want
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
>
>From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.htm
>Posted via DevelopmentNow.com Groups
>http://www.developmentnow.com

AS400 Query

I have a simple query - listing all line #'s an all info that goes with eac
h -(pretty much all of the columns are itentical - just the total is diff -
I need to get the sum of the invoice - which I have - BUT now I want just th
e summary of the invoice -
all of the columns from the detail with the total - how can I print this?
'? see example
ATEISO SOLDTO SHIPTO CABBV DABBV TO
007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
TOTAL 46,085.9
i want
007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
From http://www.developmentnow.com/g/118... />
server.htm
Posted via DevelopmentNow.com Groups
http://www.developmentnow.comOf course this is a Microsoft SQL Server newsgroup, but the SQL needed
for this is pretty simple.
SELECT ATEISO, SOLDTO, SHIPTO, CABBV, DABBV,
SUM(TO) as TotalTO
FROM Whatever
GROUP BY ATEISO, SOLDTO, SHIPTO, CABBV, DABBV
That assumes that you know and apply whatever the workaround is in
your dialect of SQL for a column name (TO) that is a SQL reserved
word. Or maybe it is not reserved in your dialect.
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 16:11:53 GMT, Kathy
Goldie<kathy.goldie@.maps-na.com> wrote:

>I have a simple query - listing all line #'s an all info that goes with each -(pre
tty much all of the columns are itentical - just the total is diff - I need to get t
he sum of the invoice - which I have - BUT now I want just the summary of the invoic
e -
all of the columns from the detail with the total - how can I print this'' see exampl
e
> ATEISO SOLDTO SHIPTO CABBV DABBV TO
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
> TOTAL 46,085.9
>i want
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
>
>From http://www.developmentnow.com/g/118.../>
-server.htm
>Posted via DevelopmentNow.com Groups
>http://www.developmentnow.com

AS400 Query

I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this'' see example
ATEISO SOLDTO SHIPTO CABBV DABBV TO
007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
TOTAL 46,085.9
i want
007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comOf course this is a Microsoft SQL Server newsgroup, but the SQL needed
for this is pretty simple.
SELECT ATEISO, SOLDTO, SHIPTO, CABBV, DABBV,
SUM(TO) as TotalTO
FROM Whatever
GROUP BY ATEISO, SOLDTO, SHIPTO, CABBV, DABBV
That assumes that you know and apply whatever the workaround is in
your dialect of SQL for a column name (TO) that is a SQL reserved
word. Or maybe it is not reserved in your dialect.
Roy Harvey
Beacon Falls, CT
On Fri, 08 Jun 2007 16:11:53 GMT, Kathy
Goldie<kathy.goldie@.maps-na.com> wrote:
>I have a simple query - listing all line #'s an all info that goes with each -(pretty much all of the columns are itentical - just the total is diff - I need to get the sum of the invoice - which I have - BUT now I want just the summary of the invoice - all of the columns from the detail with the total - how can I print this'' see example
> ATEISO SOLDTO SHIPTO CABBV DABBV TO
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 13,561.9
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 5,391.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 21,741.3
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 .0
> TOTAL 46,085.9
>i want
>007-04-02 6,537,000 6,537,002 FORDKT KTP10 46,085.9
>
>From http://www.developmentnow.com/g/118_2005_8_0_24_0/microsoft-public-sqlserver-server.htm
>Posted via DevelopmentNow.com Groups
>http://www.developmentnow.com

Arranging data on multiple rows into a sigle row (converting rows into columns)

Hello,

I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:

user1 answer1
user1 answer2
user1 answer3
user2 answer1
user2 answer2
user2 answer3

For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so thateach user will be on a single row with all of that user's answers on that single row (A column for each answer):

user1 answer1 answer2 answer3
user2 answer1 answer2 answer3

How can this be done? How can all answers of a user appear on a single row

Thanx,
Danny.

sql server 2005 or 2000?

In sql server 2005, I believe the answer is with the new pivot or unpivot commands. In 2000, it gets much trickier.

Of course, I think excel can do it's own pivoting as well.

Are nulls slow?

I have several tables anywhere from 50,000 to 100,000 rows with about 100 columns in each that allow nulls. How bad will this affect performance (if any)?

If it's significant, how can I replace nulls with zeros without doing it column by column? (100 columns will take too much time)

thanksI dont think null will affect the perfomance.Below code will generate update statement for ur tables,columns which allow null values.Run this code, copy and paste the result to query analyser and execute.

select 'update '+TABLE_NAME+' set '+COLUMN_NAME+'=0 where '+COLUMN_NAME+' is null'
from INFORMATION_SCHEMA.COLUMNS
where IS_NULLABLE='YES'