مشکل در نوشتن pivot query در sql 2000

[tempali]

سلام.
من می خواستم در sql 2000 یک view یا query درست کنم که بشه مقدار موجودی هر مشتری از یک کالا را رو بدست بیاره .
ساختار تیبلهای من به شکل زیر هست:



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_IN_]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_IN_]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_customer_]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_customer_]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[_out_]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[_out_]
GO

CREATE TABLE [dbo].[_IN_] (
[receipt no] [int] NULL ,
[receipt date] [char] (10) COLLATE Arabic_CI_AS NULL ,
[customer id] [int] NULL ,
[part no] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[part qty] [int] NULL ,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[_customer_] (
[customer id] [int] NOT NULL ,
[customer name] [varchar] (100) COLLATE Arabic_CI_AS NOT NULL ,
[customer address] [varchar] (255) COLLATE Arabic_CI_AS NULL ,
[customer phone] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[customer cell] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
[customer fax] [varchar] (50) COLLATE Arabic_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[_out_] (
[order No] [bigint] NOT NULL ,
[order Date] [varchar] (10) COLLATE Arabic_CI_AS NOT NULL ,
[customer ID] [int] NOT NULL ,
[part no] [varchar] (100) COLLATE Arabic_CI_AS NOT NULL ,
[part qty] [int] NOT NULL ,
[driver name] [varchar] (50) COLLATE Arabic_CI_AS NULL ,
) ON [PRIMARY]
GO
INSERT INTO [_IN_]([receipt no], [receipt date], [customer id], [part no], [part qty])
VALUES(1, "90/02/01", 1, "LG_TV_32", 100)
GO
INSERT INTO [_IN_]([receipt no], [receipt date], [customer id], [part no], [part qty])
VALUES(1, "90/02/01", 2, "LG_TV_42", 100)
GO
INSERT INTO [_IN_]([receipt no], [receipt date], [customer id], [part no], [part qty])
VALUES(1, "90/02/01", 3, "SAMSUNG_TV_32", 100)
GO
INSERT INTO [_IN_]([receipt no], [receipt date], [customer id], [part no], [part qty])
VALUES(1, "90/03/11", 3, "LG_TV_42", 50)
GO
INSERT INTO [_IN_]([receipt no], [receipt date], [customer id], [part no], [part qty])
VALUES(1, "90/02/01", 2, "LG_TV_42", 100)
GO
INSERT INTO [_IN_]([receipt no], [receipt date], [customer id], [part no], [part qty])
VALUES(1, "90/04/05", 1, "LG_TV_42", 100)
GO
INSERT INTO [_customer_]([customer id],[customer name])
VALUES(1, "Customer A")
GO
INSERT INTO [_customer_]([customer id],[customer name])
VALUES(2, "Customer B")
GO
INSERT INTO [_customer_]([customer id],[customer name])
VALUES(3, "Customer C")
GO
INSERT INTO [_out_]([order No], [order Date], [customer ID], [part no],[part qty], [driver name], [car no])
VALUES(10,"90/05/01",1,"LG_TV_42",5,"DRIVER 1","TEH 3310")
GO
INSERT INTO [_out_]([order No], [order Date], [customer ID], [part no],[part qty], [driver name], [car no])
VALUES(10,"90/05/01",1,"LG_TV_42",5,"DRIVER A","TEH 3310")
GO
INSERT INTO [_out_]([order No], [order Date], [customer ID], [part no],[part qty], [driver name], [car no])
VALUES(10,"90/05/11",2,"LG_TV_32",5,"DRIVER C","SHE 1110")
GO
INSERT INTO [_out_]([order No], [order Date], [customer ID], [part no],[part qty], [driver name], [car no])
VALUES(10,"90/05/13",1,"LG_TV_42",10,"DRIVER A","TEH 3310")
GO
INSERT INTO [_out_]([order No], [order Date], [customer ID], [part no],[part qty], [driver name], [car no])
VALUES(10,"90/06/01",3,"LG_TV_42",50,"DRIVER D","ASF 6598")
GO


حالا اگر من بخوام این کا رو بدون استفاده از حلقه های تو در تو داخل vb انجام بدم راهی هست یانه؟
مثلا بگه مقدار موجودی customer a , customer c , customer b از LG_TV_42 باتوجه به میزان ورود و خروج کالا چقدر است.

[محمد سلیم آبادی]

چرا هیچ کدام از جداول کلید اصلی ندارند؟!
چرا در دو جدول IN و OUT برای ستون customer_id کلید خارجی در نظر گرفته نشده؟
چرا در جدول IN تمام ستون ها Nullable هستند؟ و یک سطر با تمام مقادیر Null امکان درج داره.
نام جداول همچینن ستون ها میتونستند استاندارد نوشته بشن. مثلا نام ستون ها نباید از دو کلمه تشکیل بشن کافی بود کاراکتر _ بجای Space استفاده می شد اون وقت در نوشتن کوئری مجبور نبودین براکت بذارین.


مقدار موجودی هر مشتری از یک کالا را رو بدست بیارهمن متوجه نشدم که چه خروجی مد نظر شماست. البته ممکن است دیگران بتونند حدسهایی بزنند ولی هیچ کدام تا زمانی که خروجی شما مشاهده نشده بی فایده و غیر دقیق هست.
اگه امکان داره خروجی مورد نظر با همون داده هایی که پست کردین رو برامون به نمایش دربارین تا بتونیم کمک کنیم.

[tempali]

باعرض شرمندگی از اشکالاتی که در طراحی این دیتابیس گوش زد کردید.
این دیتا بیس قبلا در اکسس توسط شخص دیگری به طور غیر اصولی طراحی شده بود و بنده بخاطر از بین نرفتن اطلاعات موجود مجبور به استفاده از آن تا زمان حل شده مشکل شدم. انشا ا... بعد از حل موضوع دیتابیس رو نرمالایز خواهم کرد..
توضیح تکمیلی این مشکل به این صورت است که : شما فرض کنید یک انبار کالا دارید. در این انبار چند مدل کالا دارید که دارای صاحبان مختلف هستند ولی در یکجا دپو شده حالا می خواهید طی یک گزارش بدست بیاورید که مثلا کالایLG_TV_42 که تعداد 100 دستگاه از آن در انبار شما موجود است چه مقداری مطعلق به مشتری شماره 1 و چه تعداد مطعلق به مشتری شماره 2 و .... است به ای صورت که ستون اول نام کالا و در ستونهای بعدی شماره و یا نام مشتری دیده شود.
71823

71826
امید وارم تونسته باشم منظورم را رسانده باشم

[یوسف زالی]

شما باید از Pivot استفاده کنید اما از اونجا که 2000 همچین دستوری نداره باید از داینامیک کوئری استفاده کنید.

ساخت جداول:


create table TIN(CID int, GoodName varchar(20), Qty int)
create table TOUT(CID int, GoodName varchar(20), Qty int)
insert into TIN
values
(1,'LG_TV_32', 100),
(1,'LG_TV_42', 100),
(2,'LG_TV_42', 200),
(3,'LG_TV_42', 50),
(3,'SAMSUNG_TV_32', 100)
insert into TOUT
values
(2,'LG_TV_32', 5),
(1,'LG_TV_42', 200),
(3,'LG_TV_42', 50),
(2,'LG_TV_32', 5)


باید جدولهاتون رو یکی کنید و برای خروج منفی ثبت کنید:


insert into TIN
select CID, GoodName,-Qty
from TOUT


حالا بریم سراغ گزارش:


declare @x varchar(4000)
set @x ='create table #T(GName varchar(20)'
select @x = @x +', ['+cast(max(CID)as varchar(10))+'] int default(0)'
from TIN
group by CID
set @x = @x +') insert into #T(GName) select distinct GoodName from TIN update #T set GName = GName'
select @x = @x +', ['+cast(max(CID)as varchar(10))+'] = isnull((select sum(Qty) from TIN where CID = '+cast(max(CID)as varchar(10))+' and GoodName = GName), 0)'
from TIN
group by CID
set @x = @x +' select * from #T drop table #T'
exec(@x)


این هم خروجی :


LG_TV_32 100 -10 0
LG_TV_42 -100 200 0
SAMSUNG_TV_32 0 0 100


شاید راههای کوتاهتری هم وجود داشته باشه.
برای درک بهتر موضوع جای دستور Exec را با Print عوض کنید تا ببینید چه اتفاقی می افتد.
موفق باشید.

[tempali]

باتشکر از راهنمایی شما
من بخشهایی که شما آماده کرده بودید رو با توجه به جداول خودم تغییر دادم ولی در بخش گزارش این error رو میده :
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

(0 row(s) affected)


(0 row(s) affected)
البته تا بخش ساخت فیلدها مشکل نیست.
من به شکل زیر متن رو تغییر دادم:

declare @x varchar(5447)
set @x ='create table #T(GName varchar(20)'
select @x = @x +', ['+cast(max([customer id])as varchar(10))+'] int default(0)'
from TIN
group by [customer id]
order by [customer id]
set @x = @x +') insert into #T(GName) select distinct [part no] from TIN update #T set GName = GName'
select @x = @x +', ['+ cast(max([customer id])as varchar(10))+'] = isnull((select sum([part qty]) from TIN where [customer id] = '+cast(max([customer id])as varchar(10))+' and [part no] = GName), 0)'
from TIN
group by [customer id]
set @x = @x +' select * from #T drop table #T'
print(@x)
exec(@x)

من در TIN بعد از منفی کردن مقادیر tout حدود 8600 رکورد دارم. ممکن اشکال از این باشه.
من دیتابیسم رو بهمراه رکورد هاش اگر خواستید براتون میل می کنم.

باتشکر

[behrouzlo]

طول داده x را به max تغییر دهید این خطا اکثرا موقعی رخ می دهد که بخواهیم یک رشته طولانی را نسبت به طول متغییر در آن متغییر جا بدهیم

[یوسف زالی]

البته تا جایی که من می دونم max در SQL 2000 وجود نداره.
اگر بفرستید و اگر تونستم retore کنم چشم.
بهتره برای تشکر از دکمه استفاده کنید.
راحت تره که! :چشمک:

اما مشکل truncate مربوط هست به طولانی شدن بیش از حد رشته.
باید جای یک رشته از چند رشته استفاده کنید و مجموع اونها رو Exec کنید.

[tempali]

لطفا به آدرس من tempali59@yahoo.com یک ایمیل بفرستید تا فایل رو براتون attach کنم.