oh, no it's just brief explanation so you can see detail below.

Alter Proc rsp_IC_PackingList_CrossTab (@po_no nvarchar(32))

AS

BEGIN

DECLARE cShips CURSOR FOR

SELECT ShipmentDate,Id From tab_ic_packing_list_shipment

WHERE PackingListNo=@po_no

ORDER BY 1

Declare @sql as varchar( 8000)

Set @sql='SELECTdbo.tab_IC_Packing_List_Detail.ItemCode,

(SELECT Description

FROM dbo.tab_ic_itemcode

WHERE dbo.tab_Ic_itemcode.itemcode = dbo.tab_IC_Packing_List_Detail.ItemCode) AS Description,

dbo.tab_IC_Packing_List_Detail.SizeGroupID,

dbo.tab_IC_Packing_List_Detail.Cost,

dbo.tab_IC_Packing_List_Detail.Qty,

dbo.tab_IC_Packing_List_Detail.Discount,

dbo.tab_IC_Packing_List_Detail.Qty * Cost * (1-Discount) as GrantTotal,

dbo.tab_IC_Packing_List_Detail.FOC,

dbo.tab_IC_Packing_List_Detail.Qty + dbo.tab_IC_Packing_List_Detail.FOC as PoFOC,

dbo.tab_IC_Packing_List_Detail.CreateDate,' + char(13)

--WHERE state = ORDER BY 1

DECLARE @ShipmentDate DateTime

Declare @id as varchar(5000)

OPEN cShips

FETCH NEXT FROM cShips

INTO @ShipmentDate,@id

Declare @i as int

Set @i=0

WHILE @@FETCH_STATUS = 0

BEGIN

set @i=@i+1

Set @sql=@sql + ' SUM(CASE ShipmentId WHEN '+ char(39)+@id+char(39)+' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty'+Cast(@i as varchar) +','+char(13)

Set @sql=@sql+' SUM(CASE ShipmentId WHEN '+ char(39)+@id+char(39)+' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc'+Cast(@i as varchar) +','+char(13)

Set @sql=@sql+' SUM(CASE ShipmentId WHEN '+ char(39)+@id+char(39)+' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt'+Cast(@i as varchar) +','+char(13)

FETCH NEXT FROM cShips

INTO @ShipmentDate,@id

END

Close cShips

Deallocate cShips

Set @sql=@sql+ '(select Sum(QtyInMainUnit)

FROM tab_IC_Item_In_Location

WHERE

tab_IC_Item_In_Location.ItemCode=dbo.tab_IC_Packing_List_Detail.ItemCode AND

tab_IC_Item_In_Location.SizeGroupID=dbo.tab_IC_Packing_List_Detail.SizeGroupID) As StockBalance,

(Select sum(QtyInMainUnit)/9

From tab_SO_Invoice_Summary

WHERE

tab_SO_Invoice_Summary.Item=tab_IC_Packing_List_Detail.ItemCode And

tab_SO_Invoice_Summary.SizeGroupId=tab_IC_Packing_List_Detail.SizeGroupId And

tab_SO_Invoice_Summary.CreateDate Between DateAdd(m,-9,tab_IC_Packing_List_Detail.CreateDate)AND tab_IC_Packing_List_Detail.CreateDate) As Avg9Month,

tab_IC_Packing_List_Detail.Remark ' + char(13)

Set @sql=@sql+' FROMdbo.tab_IC_Packing_List_Detail INNER JOIN dbo.tab_IC_Packing_List_Shipment_Detail

ON dbo.tab_IC_Packing_List_Detail.TransID = dbo.tab_IC_Packing_List_Shipment_Detail.PODetailID' + char(13)

Set @sql=@sql+' WHERE dbo.tab_IC_Packing_List_Detail.PackingListNo='+ char(39)+ @po_no + char(39) + char(13)

Set @sql=@sql+'GROUP BY

dbo.tab_IC_Packing_List_Detail.ItemCode,

dbo.tab_IC_Packing_List_Detail.SizeGroupID,

dbo.tab_IC_Packing_List_Detail.Cost,

dbo.tab_IC_Packing_List_Detail.Qty,

dbo.tab_IC_Packing_List_Detail.Discount,

dbo.tab_IC_Packing_List_Detail.FOC,

dbo.tab_IC_Packing_List_Detail.CreateDate,

dbo.tab_IC_Packing_List_Detail.Remark'

print(@sql)

exec (@sql)

END

GO

OUTPUT:

SELECTdbo.tab_IC_Packing_List_Detail.ItemCode,

(SELECT Description

FROM dbo.tab_ic_itemcode

WHERE dbo.tab_Ic_itemcode.itemcode = dbo.tab_IC_Packing_List_Detail.ItemCode) AS Description,

dbo.tab_IC_Packing_List_Detail.SizeGroupID,

dbo.tab_IC_Packing_List_Detail.Cost,

dbo.tab_IC_Packing_List_Detail.Qty,

dbo.tab_IC_Packing_List_Detail.Discount,

dbo.tab_IC_Packing_List_Detail.Qty * Cost * (1-Discount) as GrantTotal,

dbo.tab_IC_Packing_List_Detail.FOC,

dbo.tab_IC_Packing_List_Detail.Qty + dbo.tab_IC_Packing_List_Detail.FOC as PoFOC,

dbo.tab_IC_Packing_List_Detail.CreateDate,

SUM(CASE ShipmentId WHEN '301CB3A5-382E-486D-9074-A35304A1A99B' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty1,

SUM(CASE ShipmentId WHEN '301CB3A5-382E-486D-9074-A35304A1A99B' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc1,

SUM(CASE ShipmentId WHEN '301CB3A5-382E-486D-9074-A35304A1A99B' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt1,

SUM(CASE ShipmentId WHEN 'F66634BC-9709-4F0E-961B-2B01C7A97943' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty2,

SUM(CASE ShipmentId WHEN 'F66634BC-9709-4F0E-961B-2B01C7A97943' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc2,

SUM(CASE ShipmentId WHEN 'F66634BC-9709-4F0E-961B-2B01C7A97943' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt2,

SUM(CASE ShipmentId WHEN '31796E84-5C81-450E-AB1F-1CA6BCB75473' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty3,

SUM(CASE ShipmentId WHEN '31796E84-5C81-450E-AB1F-1CA6BCB75473' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc3,

SUM(CASE ShipmentId WHEN '31796E84-5C81-450E-AB1F-1CA6BCB75473' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt3,

SUM(CASE ShipmentId WHEN '25F67ED1-0151-4AA9-833B-E2C749852BE0' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty4,

SUM(CASE ShipmentId WHEN '25F67ED1-0151-4AA9-833B-E2C749852BE0' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc4,

SUM(CASE ShipmentId WHEN '25F67ED1-0151-4AA9-833B-E2C749852BE0' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt4,

SUM(CASE ShipmentId WHEN '61CE059D-04CF-455E-A091-D09961DBA8C3' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty5,

SUM(CASE ShipmentId WHEN '61CE059D-04CF-455E-A091-D09961DBA8C3' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc5,

SUM(CASE ShipmentId WHEN '61CE059D-04CF-455E-A091-D09961DBA8C3' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt5,

SUM(CASE ShipmentId WHEN 'AAC92855-89B2-41A5-B9D8-D84D47499D1D' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty ELSE 0 END) AS ShipQty6,

SUM(CASE ShipmentId WHEN 'AAC92855-89B2-41A5-B9D8-D84D47499D1D' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Foc ELSE 0 END) AS ShipFoc6,

SUM(CASE ShipmentId WHEN 'AAC92855-89B2-41A5-B9D8-D84D47499D1D' THEN dbo.tab_IC_Packing_List_Shipment_Detail.Qty*Cost*(1-Discount) ELSE 0 END) AS ShipAmt6,

(select Sum(QtyInMainUnit)

FROM tab_IC_Item_In_Location

WHERE

tab_IC_Item_In_Location.ItemCode=dbo.tab_IC_Packing_List_Detail.ItemCode AND

tab_IC_Item_In_Location.SizeGroupID=dbo.tab_IC_Packing_List_Detail.SizeGroupID) As StockBalance,

(Select sum(QtyInMainUnit)/9

From tab_SO_Invoice_Summary

WHERE

tab_SO_Invoice_Summary.Item=tab_IC_Packing_List_Detail.ItemCode And

tab_SO_Invoice_Summary.SizeGroupId=tab_IC_Packing_List_Detail.SizeGroupId And

tab_SO_Invoice_Summary.CreateDate Between DateAdd(m,-9,tab_IC_Packing_List_Detail.CreateDate)AND tab_IC_Packing_List_Detail.CreateDate) As Avg9Month,

tab_IC_Packing_List_Detail.Remark

FROMdbo.tab_IC_Packing_List_Detail INNER JOIN

dbGROUP BY

dbo.tab_IC_Packing_List_Detail.ItemCode,

dbo.tab_IC_Packing_List_Detail.SizeGroupID,

dbo.tab_IC_Packing_List_Detail.Cost,

dbo.tab_IC_Packing_List_Detail.Qty,

dbo.tab_IC_Packing_List_Detail.Discount,

dbo.tab_IC_Packing_List_Detail.FOC,

dbo.tab_IC_Packing_List_Detail.CreateDate,

dbo.tab_IC_Packing_List_Detail.Remark

Server: Msg 156, Level 15, State 1, Line 23

Incorrect syntax near the keyword 'BY'.