variabel 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

SQL Server 2000 Forums

Transact-SQL (2000)

variabel table

Author

Topic

Mckay
Starting Member

8 Posts

Posted - 2006-06-20 : 06:25:15

hi,
i want to insert data into variabel table for each field,
like this below, but it won't work, can u help me pls?

declare @date int
declare @tmpProduction
table(
[1] decimal(20,2),
[2] decimal(20,2),
[3] decimal(20,2),
[4] decimal(20,2),
[5] decimal(20,2),
[6] decimal(20,2),
[7] decimal(20,2),

decimal(20,2),
[9] decimal(20,2),
[10] decimal(20,2),
[11] decimal(20,2),
[12] decimal(20,2),
[13] decimal(20,2),
[14] decimal(20,2),
[15] decimal(20,2),
[16] decimal(20,2),
[17] decimal(20,2),
[18] decimal(20,2),
[19] decimal(20,2),
[20] decimal(20,2),
[21] decimal(20,2),
[22] decimal(20,2),
[23] decimal(20,2),
[24] decimal(20,2),
[25] decimal(20,2),
[26] decimal(20,2),
[27] decimal(20,2),
[28] decimal(20,2),
[29] decimal(20,2),
[30] decimal(20,2),
[31] decimal(20,2)
)

set @date = 1

while @date <= 31
begin
insert into @tmpProduction(@date)
select sum(quantity) as TtlQty from tbStock where .....
set @date = @date + 1
end

THX

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-20 : 06:33:38

This is not normalised. Read about Normalisation
http://www.datamodel.org/NormalizationRules.html

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-20 : 08:06:34

Mckay,

Maybe you can explain what are you trying to do

Mckay
Starting Member

8 Posts

Posted - 2006-06-21 : 02:21:47

example i want to make report like this :

Period : 01/06/2006 - 30/06/2006
-------------------------------------------
Date | 01 | 02 | 03 | ... | 30 |
-------------------------------------------
Prod1 | 100 | 0 | 0 | ... | 70 |
Prod2 | 40 | 10 | 0 | ... | 0 |
Prod3 | 50 | 0 | 0 | ... | 50 |
Prod4 | 30 | 0 | 0 | ... | 90 |
Prod5 | 0 | 60 | 0 | ... | 0 |

from this table :

*Header Table
-----------------------
| TransID | TransDate |
-----------------------
| A | 01-06-2006|
| B | 01-06-2006|
| C | 30-06-2006|
| D | 02-06-2006|

*Detail Table
----------------------------------
| TransID | ProductID | Quantity |
----------------------------------
| A | Prod1 | 10 |
| A | Prod2 | 20 |
| B | Prod3 | 50 |
| B | Prod1 | 90 |
| B | Prod2 | 20 |
| B | Prod4 | 30 |
| C | Prod1 | 70 |
| C | Prod4 | 90 |
| C | Prod3 | 50 |
| D | Prod5 | 60 |
| D | Prod2 | 10 |

can u help me with sql query pls?

THX

Mckay

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-21 : 02:39:21

If you are using any reporting tool, it can be easily done with cross-tab

declare @Header table
(
	TransID		varchar(10),
	TransDate	datetime
)

declare	@Detail table
(
	TransID		varchar(10),
	ProductID	varchar(10),
	Quantity	int
)

insert into @Header
select	'A', '2006-06-01'	union all
select	'B', '2006-06-01'	union all
select	'C', '2006-06-30'	union all
select	'D', '2006-06-02'

insert into @Detail

select 'A' , 'Prod1' , 10  union all
select 'A' , 'Prod2' , 20  union all
select 'B' , 'Prod3' , 50  union all
select 'B' , 'Prod1' , 90  union all
select 'B' , 'Prod2' , 20  union all
select 'B' , 'Prod4' , 30  union all
select 'C' , 'Prod1' , 70  union all
select 'C' , 'Prod4' , 90  union all
select 'C' , 'Prod3' , 50  union all
select 'D' , 'Prod5' , 60  union all
select 'D' , 'Prod2' , 10 

select	ProductID,
	sum(case when h.TransDate = '2006-06-01' then Quantity else 0 end) as [01],
	sum(case when h.TransDate = '2006-06-02' then Quantity else 0 end) as [02],
	sum(case when h.TransDate = '2006-06-03' then Quantity else 0 end) as [03],
--      . . . 
	sum(case when h.TransDate = '2006-06-30' then Quantity else 0 end) as [30]
from	@Header h inner join @Detail d
	on	h.TransID	= d.TransID
group by ProductID

/* RESULT :
ProductID  01          02          03          30          
---------- ----------- ----------- ----------- ----------- 
Prod1      100         0           0           70
Prod2      40          10          0           0
Prod3      50          0           0           50
Prod4      30          0           0           90
Prod5      0           60          0           0
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources