A quest for a query

Hello all!
Could somebody please help me with the following query: I have a table with
3 colums. First column are just identity numbers. Second column contains
data in the following pattern: 2 rows of data, 1 null (empty) row and then
again 2 rows on data, 1 null row, etc. Third column is as now empty.
Now, the query should take each second "data" row of the second column
(cells in rows 2, 5, 8, 11,14, etc.) and copy their contents to the third
column but one row higher (column 2, row 2 should be copied to column 3,
row, c2 r5 to c3 r4, etc.) in a sort of partial transposing of the
table...I don't have an idea how to accomplish that.
Thank you in advance!
HrvojeTry,
update
a
set
a.colC = b.colB
from
t as a
inner join
t as b
on a.colA = b.colB - 1
and (b.colB = 2 or b.colB % 3 = 2)
AMB
"Hrvoje Vrbanc" wrote:

> Hello all!
> Could somebody please help me with the following query: I have a table wit
h
> 3 colums. First column are just identity numbers. Second column contains
> data in the following pattern: 2 rows of data, 1 null (empty) row and then
> again 2 rows on data, 1 null row, etc. Third column is as now empty.
> Now, the query should take each second "data" row of the second column
> (cells in rows 2, 5, 8, 11,14, etc.) and copy their contents to the third
> column but one row higher (column 2, row 2 should be copied to column 3,
> row, c2 r5 to c3 r4, etc.) in a sort of partial transposing of the
> table...I don't have an idea how to accomplish that.
> Thank you in advance!
> Hrvoje
>
>|||How does the data get populated in this table? The problem is that you
can't normally control the order in which IDENTITY values are allocated
so when you say "each second row" or Nth row or whatever, there may be
no guarantee that the rows numbered 1,2 or 3 are what you expected them
to be. Do not rely on the IDENTITY values being sequential unless you
populate all the data using the SET IDENTITY_INSERT option (in which
case, why use IDENTITY at all?).
The second issue is with the principle of this table design. Leaving
aside the difficulty with IDENTITY, my impression is that this is not a
correctly normalized table. I'll assume then that you intend to create
a new table and that the purpose of your query is to transform the data
into the correct format.
With that assumption in mind it's difficult to make much sense of your
final paragraph. What does "one row higher" mean? Rows should be
identifiable from keys not from row "position numbers".
In short, we'll need some more information to help you out: DDL, sample
data INSERTs and required end result. Please do include keys and
constraints if you post DDL. See this article for info on how to do
this:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hello!
It's a static flat table, no further population and no changes except the
one I'm searching the query for.
One row higher means the following: every row has the primary key that is an
ordinal number. I want to copy the data from one colum to another but to the
row with lower ordinal number.
Hrvoje
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110205802.879690.42060@.o13g2000cwo.googlegroups.com...
> How does the data get populated in this table? The problem is that you
> can't normally control the order in which IDENTITY values are allocated
> so when you say "each second row" or Nth row or whatever, there may be
> no guarantee that the rows numbered 1,2 or 3 are what you expected them
> to be. Do not rely on the IDENTITY values being sequential unless you
> populate all the data using the SET IDENTITY_INSERT option (in which
> case, why use IDENTITY at all?).
> The second issue is with the principle of this table design. Leaving
> aside the difficulty with IDENTITY, my impression is that this is not a
> correctly normalized table. I'll assume then that you intend to create
> a new table and that the purpose of your query is to transform the data
> into the correct format.
> With that assumption in mind it's difficult to make much sense of your
> final paragraph. What does "one row higher" mean? Rows should be
> identifiable from keys not from row "position numbers".
> In short, we'll need some more information to help you out: DDL, sample
> data INSERTs and required end result. Please do include keys and
> constraints if you post DDL. See this article for info on how to do
> this:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks!
I'll try!
Hrvoje
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5F3F369B-96E8-4611-AF48-75817E7B0399@.microsoft.com...
> Try,
> update
> a
> set
> a.colC = b.colB
> from
> t as a
> inner join
> t as b
> on a.colA = b.colB - 1
> and (b.colB = 2 or b.colB % 3 = 2)
>
> AMB
>
> "Hrvoje Vrbanc" wrote:
>

A query to locate a specific column in various tables?

I have over 65 databases, all with various numbers of tables. Within these
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
Message posted via http://www.droptable.com
SELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer

A query to locate a specific column in various tables?

I have over 65 databases, all with various numbers of tables. Within these
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
--
Message posted via http://www.sqlmonster.comSELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer

A query to locate a specific column in various tables?

I have over 65 databases, all with various numbers of tables. Within these
tables I need to see which tables contain a specific field called vendor_id
Is there a query that can be ran for this?
Thanks
Jeff
Message posted via http://www.droptable.comSELECT Table_name from INFORMATION_SCHEMA.COLUMNS Where Column_Name LIKE
'YADAYADA'
HTH, Jens Suessmeyer

A good way to increment field value

Hi

I have a field containing numbers. I want to do some simple arithmetics with it, say value=value+1 or value=value-1 or or even value+2. What is to be done, is fixed at design time. I think this could be done by loading the row or record to my program and doing the calculations there. And then storing the record back. But this seems too complicated.

Is there a single query doing that in data table.

You can create all your calculation in an sql server user defined function.

And call this function while inserting the data into a table.

|||

Thanks. That seems interesting. And while searching for user defined functions I found help to other problem as well.

I found this:

create function getfulldate (@.date varchar(10))
returns datetime
as
begin
declare @.getfulldate datetime
set @.getfulldate = dateadd (mi,55,@.date)
return @.getfulldate
end

and normally we call this in the SQL statements as
select *, dbo.getfulldate('2006-05-03') from emp

If I undestand this right, this goes into code. Do you know how to put udf in sql server. Or rather, where to start learning it.

Regards

Leif

|||

I found a tutorial about T-sql.