Creating Table - 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

Creating Table

Author

Topic

Lionheart
Starting Member

41 Posts

Posted - 2009-01-08 : 10:26:24

I have the following three queries which pull the top 2000 values from a large set of values. What I would like to do is create a table with 2000 rows, into which is selected the values that would be created from each of the individual queries. I do not need the event columns (although it should be noted that the same events will not create the same top 2000 values), but do need to create a first column that would automatically generate numbers from 1 to 2000.

use Data
select top 2000 sum(NetLoss) as Loss, event
from tbl_Data
group by event
order by sum(NetLoss) desc

use Data
select top 2000 sum(case when lobid in (A,B,C) then NetLoss else null end) as ABC, event
from tbl_Data
group by event
order by ABC desc

use Data
select top 2000 sum(case when lobid in (D,E,F) then NetLoss else null end) as DEF, event
from tbl_Data
group by event
order by DEF desc

Thanks LH

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-08 : 10:42:20

look for identity function.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-01-08 : 10:42:27

Why does it have to be a hard table? Could you use a VIEW instead?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-08 : 10:47:55

use Data
select top 2000 IDENTITY (int,1,1) as recnum, sum(NetLoss) as Loss, event
INTO not_existing_table
from tbl_Data
group by event
order by sum(NetLoss) desc

No, you're never too old to Yak'n'Roll if you're too young to die.

Lionheart
Starting Member

41 Posts

Posted - 2009-01-08 : 11:01:16

Have used the identity to post into temp table and then select, but it sums the 200 values for each and posts one row. What I need is 2000 separate values for each column based on the individual query. How can I change it to create a new row for each 2000, with an integer increase for each row?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 11:12:08

show some sample data and illustrate what you're looking for

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-08 : 11:12:17

Sorry Richard, can't get you.
Could you please explain by using example data?
(not 2000 but maybe 5 or so...)

No, you're never too old to Yak'n'Roll if you're too young to die.

Lionheart
Starting Member

41 Posts

Posted - 2009-01-08 : 11:29:00

I have the following which has a total of all 200 values in one row

Num Loss ABC DEF
1 2,752,005 1,305 1,446

But I need a table with 2000 rows showing the inidividual values that make up the sum of the 2000 values.

Num Loss ABC DEF
1 4,829,751 3,955 1,179
2 4,379,557 3,320 1,118
3 3,570,504 3,293 1,116
4 3,545,569 3,066 1,116
5 3,441,054 2,941 1,092
6 3,412,209 2,925 1,082

Ignore the actual values in there, as I have cut them down to make the table simpler.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 11:38:22

SELECT event,Loss,ABC,DEF
FROM
(
select top 2000 event,sum(NetLoss) as Loss,cast(null as int) as ABC,cast(null as int) as DEF
from tbl_Data
group by event
order by sum(NetLoss) desc
)t1

union all

SELECT event,Loss,ABC,DEF
FROM
(
select top 2000 event,null AS Loss,sum(case when lobid in (A,B,C) then NetLoss else null end) as ABC,null AS DEF
from tbl_Data
group by event
order by ABC desc
)t2

union all

SELECT event,Loss,ABC,DEF
FROM
(
select top 2000 event,null as Loss,null AS ABC,sum(case when lobid in (D,E,F) then NetLoss else null end) as DEF
from tbl_Data
group by event
order by DEF desc
)t3
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources