Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I want to get the data from the database and put it back with the new format

I have a lot of datasheet like follow

ID a b c  d
1  1 5 9  13
2  2 6 10 14
3  3 7 11 15
4  4 8 12 16

and I want to put it in a new datasheet like follow

ID a b c 
1  1 5 9
2  1 5 9
3  1 5 9

and the second sheet's data's column C can be the column D like

ID a b c d
1 1 5 9 10
2 1 5 9 10
3 1 5 9 10

how to make the SQL?

share|improve this question
Which database server are you using? Do you want a solution in SQL or are you using external tools/code? Also, are the values in the second two data sets correct? It looks like you have cut and pasted the same data for every row? – Tony Jun 23 '11 at 9:45
@Tony I use VB6 to call ACCESS database It's just like cut the first row and paste in new datasheet and every datasheet's Data are not the same what I want to do is cut the first row paste at the first row the second row's Column C paste at the Column D in the new datasheet thank for your concern! – blueman010112 Jun 23 '11 at 10:54
1  
In order to build some SQL we will need more definition as to how this transformation should be done. For example, how does the first result set know to be three rows? Will it always be three or always one fewer than the source table? How does it select the row that should be used to fill the values? How does the second result set know to select the ID=1 row for the first three columns and the ID=2 row for the last column? etc. – Leigh Riffel Jun 23 '11 at 17:23
@leigh the first datasheet is the data I already have but it's just a sample for real data I have is 20 columns 366 rows and the result datasheet is not only three of it,and it will be 8X rows in the new datasheet because my source sheet have lots of it, it select ID=1 and put a b c's value in the first row and select ID=2's c's data in the new datasheet's d ,because the data format in the third datasheet is the format i need for my tool so I need the format – blueman010112 Jun 24 '11 at 2:23

1 Answer

up vote 1 down vote accepted

This returns the results you you specified, but I'm not sure it returns the results you want. It was built on Oracle, so you may need to modify the syntax slightly and of course the table names.

CREATE TABLE T1 (ID Number(3), A Number(3), B Number(3), C Number(3), D Number(3));
CREATE TABLE T2 (ID Number(3), A Number(3), B Number(3), C Number(3));
CREATE TABLE T3 (ID Number(3), A Number(3), B Number(3), C Number(3), D Number(3));

INSERT INTO T1 values (1, 1, 5,  9, 13);
INSERT INTO T1 values (2, 2, 6, 10, 14);
INSERT INTO T1 values (3, 3, 7, 11, 15);
INSERT INTO T1 values (4, 4, 8, 12, 16);

INSERT INTO T2 (
   SELECT T1B.ID, T1A.A, T1A.B, T1A.C FROM T1 T1A
   CROSS JOIN T1 T1B
   WHERE T1A.ID=1 
);

INSERT INTO T3 (
   SELECT T1B.ID, T1A.A, T1A.B, T1A.C, (SELECT T1C.C FROM T1 T1C WHERE T1C.ID=2) 
   FROM T1 T1A
   CROSS JOIN T1 T1B
   WHERE T1A.ID=1 
);

SELECT * FROM T2;
SELECT * FROM T3;
share|improve this answer
Thank for your answer I will try it right now!! – blueman010112 Jun 24 '11 at 13:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.