altering data

Hello!

I have a problem that I really could use some help to solve. I have a table
which looks like this:

id1, id2, id3, rate, ratenr

Examples of a select * from this table would be:

1047336399 21000 1 617 1
1047336399 21000 1 624B 1
1047336399 21000 1 621D 1
1047336399 21000 2 624B 1
1047336399 21000 2 612A 1
1047336399 21000 2 621D 1
1047336399 21000 3 617 1
1047336399 21000 3 624B 1
1047336399 21000 3 621D 1

I would like to transform this table into something like this:

1047336399 21000 1 617 1 624B 1 621D 1
1047336399 21000 2 624B 1 612A 1 621D 1
1047336399 21000 3 617 1 624B 1 621D 1

the three first columns should be the primary key.

Any help is appreciated

Gunnar> I would like to transform this table into something like this:
> 1047336399 21000 1 617 1 624B 1 621D 1
> 1047336399 21000 2 624B 1 612A 1 621D 1
> 1047336399 21000 3 617 1 624B 1 621D 1

This is not a normalised result set. You shouldn't create a table like this
in your database because the rate columns are a repeating group (violation
of 1st Normal Form). Your initial table appears to be a normalised design.

If this is supposed to be crosstab report then do it in your application or
report generator. Or Google search this group for "crosstab" to find lots of
examples.

--
David Portas
----
Please reply only to the newsgroup
--sql

Alter table

Hi to all!
I have a silly problem, but I'm not able to solve it!
I know how to add a field in a table via Enterprise manager and I know also via ALTER TABLE command.
...But the problem is: I want to add a field NOT in the last position, but on the middle (for istance)
So, this is possible via Enterprise Manager but with the ALTER TABLE command? How can I add a field without put it in the last position?
...Thanks a lot!
Sergio

p.s.: Sorry for my poor english..I hope you understand my question!Do it in enterprise manager->design table, click on the little scroll with briefcase icon (thirs on the tool bar) and copy the generated SQL code from there, and exit design without saving.|||Originally posted by HanafiH
Do it in enterprise manager->design table, click on the little scroll with briefcase icon (thirs on the tool bar) and copy the generated SQL code from there, and exit design without saving.

THANKS A LOT! I SOLVED MY PROBLEM!..

I never take care about icons .........bur from NOW I will consider IT!
Once again thank you!
Sergio

allows blank value for integer Parameter

Hi have a problem to solve and I hope that this is not a SSRS Bug.

I created a Reports(using SQL Server Project) which has several parameters which values are passed to a SP.

One of these parameter is an Integer and it is an optional value, so if the user fill it is used by the SP, otherwise the SP uses NULL and run anyway.

I starts to define tha parameter:

Datatype = integer

Allow blank value

Available: Non queried

Default: Null

if I want to Preview the report I have to provide an integer to the parameter's field ...

If for instance I set:

Default: Not queried = 0

In the moment I deploy and I use the ReportViewer in my window application the parameter's field is unabled!!

So I tried this solution:

Datatype = integer

Allow blank value

Allow null value

Available: Non queried

Default: Null

In the preview the checkbox: NULL is checked and I click on the View Report.

But when I deploy it,in the ReportViewer in my window application the parameter's field this checkbox is unchecked.

Do I forget something during my setting?I have to control it programmatically?

N.B. By default the user will not user this parameter so the best is that he can click directly on "View Report" without any additional "work" on the parameter!!

Thank you for any help!

hi,

On the report parameters form do this please:

Add a parameter, name it to something,

choose data type integer,

mask as checked the "Allow null value",

select null as default value.

It was working on that way on my box as you wished. I'm using SSRS 2005 wih SP2

Regards,

Janos

All from one table and all from another

I'm hoping someone could help me write the SQL code to solve this problem

I have two tables and a master one if I need it. All tables can be linked with the Master_ID. Table1 and Table2 can each have 0, one or many records for each Master_ID. The other column in the two tables is a number representing a volumn of two different fluids.

Table1
Master_id
Volume1_amount

Table2
Master_id
Volume2_amount

Master
Master_id
Master_name

How can I return all of the rows in Table1 and all of the rows in Table2 for each Master_id such that it looks like this if Table1 has 2 records and Table2 has 1 record for a given Master_id and then Table2 has 2 records and Table1 has 0 for a differnt Master_id

Master_id Volume1_amount Volume2_amount
100235 25.3 m 62.1 m
100235 22.0 m null
220000 null 85.66 m
220000 null 59.0 m

Any help would very much be appreciated.What are the primary keys of Table1 and Table2? What is it that links the 62.1m Table2 value to the 25.3m Table1 value rather than to the 22.0m value?|||The tables are actually temporary tables so there is/are no primary key(s) define but the Master_id is what links them all together. The master_id in Table1 will match the Master_id in Table2 which both match to Master_id in the master table|||Yes, but my other question was:

What is it that links the 62.1m Table2 value to the 25.3m Table1 value rather than to the 22.0m value?

You haven't answered that.|||Oh sorry - nothing except for which ever is first in the table. The two volumes don't relate to each other at all except that they both relate to the master_id. Make sense?|||OK, well the concept of "first in the table" is meaningless in a relational database without something to order by. What DBMS are you using? For Oracle I know a trick you can use. Otherwise, I would suggest you need to add an extra column to Table1 and Table2:

Table1
Master_id
Volume1_amount
Seq_no

Table2
Master_id
Volume2_amount
Seq_no

where Seq_no is 1 for the 1st record for each Master_id, 2 for the second etc.

Then your query becomes:

select coalesce(t1.master_id,t2.master_id), t1.volume1_amount, t2.volume2_amount
from t1
full outer join t2
on
(t1.master_id = t2.master_id
and t1.seq_no = t2.seq_no
);|||Excellent! Thank you very much