Matriz SQL

Hi, I have the following problem
In a DB exist this inf:
(I can use many tables... and/or many columns)
-->
1 2 3 4
5 6 7 8
9 a b c
d e f g
<--
In need write a select sentence that move in "circle" all info, for
example
5 1 2 3
9 a 6 4
d b 7 8
e f g cDoes this help:
http://spaces.msn.com/drsql/Blog/cns!80677FB08B3162E4!908.entry
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148327031.695609.65500@.g10g2000cwb.googlegroups.com...
> Hi, I have the following problem
> In a DB exist this inf:
> (I can use many tables... and/or many columns)
> -->
> 1 2 3 4
> 5 6 7 8
> 9 a b c
> d e f g
> <--
> In need write a select sentence that move in "circle" all info, for
> example
> 5 1 2 3
> 9 a 6 4
> d b 7 8
> e f g c
>|||What is your table structure and how are you getting this output to begin
with?
Post DDL and an explanation of how the original data is generated/selected.
http://www.aspfaq.com/etiquette.asp?id=5006
Also, this sounds like a class assignment. If so, what sort of class is it?
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148327031.695609.65500@.g10g2000cwb.googlegroups.com...
> Hi, I have the following problem
> In a DB exist this inf:
> (I can use many tables... and/or many columns)
> -->
> 1 2 3 4
> 5 6 7 8
> 9 a b c
> d e f g
> <--
> In need write a select sentence that move in "circle" all info, for
> example
> 5 1 2 3
> 9 a 6 4
> d b 7 8
> e f g c
>|||the DB has not been created by now, the table structure can be anyone,
4 tables, 1 table/ 1 columns, 1 table 16 columns, etc.
Not class assignment.|||Can you explain the situation/application for this logic? It will certainly
help in determining a valid approach. Also, I am very curious as to how
this might be useful in a real world situation.
Anyway, if your matrix will always be 4x4, you can try this table setup. It
involves two tables, one storing the data with its original location in the
matrix (as columns and rows) and a second storing the matrix shift (original
cell and new cell). We use a case statement with an agregate function to
produce the matrix, and we join to the matrix shift table to determine the
new location. To shift the cells more than once we could join to the matrix
shift table as many times as we need to rotate the matrix values. We can
permanently update the values at each position in the matrix if needed.
Lastly, we could change the Value stored in the Data table to a FK pointing
to another table with as many columns as we need. This would still really
only work a column at a time, but you could use any column you wanted in the
matrix.
On SQL 2005, you could probably use the pivot/unpivot functions to
accomplish this.
/*
Create our table/view structure which has data in rows and columns (matrix
format)
*/
CREATE TABLE #tmpData (ColNum integer, RowNum Integer, Value varchar(10)
primary key(ColNum,RowNum))
go
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,1,'A');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,1,'B');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,1,'C');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,1,'D');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,2,'E');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,2,'F');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,2,'G');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,2,'H');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,3,'I');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,3,'J');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,3,'K');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,3,'L');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (1,4,'M');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (2,4,'N');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (3,4,'O');
INSERT #tmpData(ColNum,RowNum,Value) VALUES (4,4,'P');
go
/*
Display data in the matrix format
*/
select
max(case when ColNum=1 then Value end) as Col1
,max(case when ColNum=2 then Value end) as Col2
,max(case when ColNum=3 then Value end) as Col3
,max(case when ColNum=4 then Value end) as Col4
from #tmpData
group by RowNum;
/*
Create our matrix mapping, showing how the matrix cells will move
*/
Create table #tmpMatrixShift
(ColNum1 integer,RowNum1 integer,ColNum2 integer,RowNum2 integer);
go
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,1,2,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,1,3,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,1,4,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,1,4,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,2,4,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,3,4,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(4,4,3,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,4,2,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,4,1,4);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,4,1,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,3,1,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(1,2,1,1);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,2,3,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,2,3,3);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(2,3,2,2);
insert into #tmpMatrixShift (ColNum1,RowNum1,ColNum2,RowNum2) values
(3,3,2,3);
go
/*
Join our data table to the matrix mapping table in order to get the new cell
locations
Display data in the matrix format
*/
select
max(case when b.ColNum2=1 then a.Value end) as Col1
,max(case when b.ColNum2=2 then a.Value end) as Col2
,max(case when b.ColNum2=3 then a.Value end) as Col3
,max(case when b.ColNum2=4 then a.Value end) as Col4
from #tmpData a
inner join #tmpMatrixShift b
on a.colnum = b.colnum1
and a.rownum = b.rownum1
group by b.RowNum2;
DROP TABLE #tmpData;
DROP TABLE #tmpMatrixShift;
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148328455.125472.120050@.j55g2000cwa.googlegroups.com...
> the DB has not been created by now, the table structure can be anyone,
> 4 tables, 1 table/ 1 columns, 1 table 16 columns, etc.
> Not class assignment.
>|||I have a program in the office (very very old) don't have objects of
this program, but this take a sentence select from .ini to execute,
this program transform a values returned (contability) and calculate
some, I can modify that select to --select transformed tables...
=BF?...
Well I do this
CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
COL2 VARCHAR(10) NOT NULL ,
COL3 VARCHAR(10) NOT NULL ,
COL4 VARCHAR(10) NOT NULL );
insert into matrix values ('1','2','3','4');
insert into matrix values ('5','6','7','8');
insert into matrix values ('9','10','11','12');
insert into matrix values ('13','14','15','16');
now?|||Have you tried the approach I posted? As long as you are fixed at 4 columns
and 4 rows in the matrix, I believe it should do what you want. However,
some of the more math-intensive folks may be able to come up with an
algorithm that is more effective.
"Alejandro" <jalejandro0211@.gmail.com> wrote in message
news:1148333584.761203.102270@.j73g2000cwa.googlegroups.com...
I have a program in the office (very very old) don't have objects of
this program, but this take a sentence select from .ini to execute,
this program transform a values returned (contability) and calculate
some, I can modify that select to --select transformed tables...
?...
Well I do this
CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
COL2 VARCHAR(10) NOT NULL ,
COL3 VARCHAR(10) NOT NULL ,
COL4 VARCHAR(10) NOT NULL );
insert into matrix values ('1','2','3','4');
insert into matrix values ('5','6','7','8');
insert into matrix values ('9','10','11','12');
insert into matrix values ('13','14','15','16');
now?|||The link I posted shows a pretty interesting method as well... But it looks
like the OP just wants the answer handed to him without investing any of his
own thought.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%232JeHYpfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Have you tried the approach I posted? As long as you are fixed at 4
> columns
> and 4 rows in the matrix, I believe it should do what you want. However,
> some of the more math-intensive folks may be able to come up with an
> algorithm that is more effective.
> "Alejandro" <jalejandro0211@.gmail.com> wrote in message
> news:1148333584.761203.102270@.j73g2000cwa.googlegroups.com...
> I have a program in the office (very very old) don't have objects of
> this program, but this take a sentence select from .ini to execute,
> this program transform a values returned (contability) and calculate
> some, I can modify that select to --select transformed tables...
> ?...
> Well I do this
> CREATE TABLE "MATRIX" ( COL1 VARCHAR(10) NOT NULL ,
> COL2 VARCHAR(10) NOT NULL ,
> COL3 VARCHAR(10) NOT NULL ,
> COL4 VARCHAR(10) NOT NULL );
> insert into matrix values ('1','2','3','4');
> insert into matrix values ('5','6','7','8');
> insert into matrix values ('9','10','11','12');
> insert into matrix values ('13','14','15','16');
> now?
>|||Your link looked more like a cross tab solution, flipping the columns and
rows. I tried to apply it to this situation, but couldn't think of how to
do it, since the cells are being rotated rather than flipped. I am still
trying to think of a valid application for this sort of thing...
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23$qSMGqfGHA.1456@.TK2MSFTNGP04.phx.gbl...
> The link I posted shows a pretty interesting method as well... But it
looks
> like the OP just wants the answer handed to him without investing any of
his
> own thought.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%232JeHYpfGHA.4864@.TK2MSFTNGP05.phx.gbl...
However,
>