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