Create a group of data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create a group of data

Author

Topic

yccyccycc
Starting Member

4 Posts

Posted - 2009-06-30 : 13:23:30

Hi all,

I have a question about create a group of data based on the existing data.

To illustrate, I will bring up one example.

Category Rank Max
1 1 100
1 2 200
1 3 300
2 1 110
2 2 220
2 3 330

I would like to create few rows, in which the categories are 3 & 4 and the Rank and Max are the same as Category 1. Category 1 will remain the same.

As a result the result of the data would be:
Category Rank Max
1 1 100
1 2 200
1 3 300
2 1 110
2 2 220
2 3 330
3 1 100
3 2 200
3 3 300
4 1 100
4 2 200
4 3 300

Originally, I created other tables and insert into the old one. I did get the result but was wondering whether there is other way to do so.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-30 : 13:26:21

[code]
INSERT INTO YourTable
SELECT t1.Cat,t.Rank,t.Max
FROM YourTable t
CROSS JOIN (SELECT 3 AS Cat UNION ALL
SELECT 4)t1
WHERE t.Category=1
[/code]

yccyccycc
Starting Member

4 Posts

Posted - 2009-07-01 : 10:15:37

Thank you for your kind reply, but it is not working >_<.

It is my bad sine I should illustrate more.

Here is what I did:
Assume the old table is called "TEST"
1) I created a new table called "Cat3" and copied the category 1 data from TEST.
I then update the data by replacing 1 with 3.(others remain the same)
2) I also created a second new table called "cat4" and copied the category 1 data from TEST.
then update the data by replacing 1 with 4 (others remain the same)
3) insert the tables "cat3" and "cat4" into the table TEST.

I would like to know if there is other way to do so.

Thank you!

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-07-01 : 11:30:27

why is it not working, did you try running just the select statement above?
SELECT t1.Cat,t.Rank,t.Max
FROM YourTable t
CROSS JOIN (SELECT 3 AS Cat UNION ALL
SELECT 4)t1
WHERE t.Category=1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-01 : 11:50:57

quote:
Originally posted by yccyccycc

Thank you for your kind reply, but it is not working >_<.

It is my bad sine I should illustrate more.

Here is what I did:
Assume the old table is called "TEST"
1) I created a new table called "Cat3" and copied the category 1 data from TEST.
I then update the data by replacing 1 with 3.(others remain the same)
2) I also created a second new table called "cat4" and copied the category 1 data from TEST.
then update the data by replacing 1 with 4 (others remain the same)
3) insert the tables "cat3" and "cat4" into the table TEST.

I would like to know if there is other way to do so.

Thank you!

you dont need to create any ntermediate table, just use query i provided replacing yourtabble with actual name of your table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources