Представления SQL не рекомендуются, однако в этом случае вам может потребоваться их создание. Приведенный ниже оператор select предоставит вам одну строку, которая вам нужна. Затем вы должны создать ЦАП на основе представления. Оттуда вы сможете добавить представление в свой отчет.
-- ------------------------------------------------------------
-- View: usrSOSplit
-- ------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usrSOSplit]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].usrSOSplit
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create View [usrSOSplit] as
SELECT companyid, shipmentnbr,
isnull(cast([1] as varchar(50)),'')
+' '+ isnull(cast([2] as varchar(50)),'')
+' '+ isnull(cast([3] as varchar(50)),'')
+' '+ isnull(cast([4] as varchar(50)),'')
+' '+ isnull(cast([5] as varchar(50)),'')
+' '+ isnull(cast([6] as varchar(50)),'')
+' '+ isnull(cast([7] as varchar(50)),'')
+' '+ isnull(cast([8] as varchar(50)),'')
+' '+ isnull(cast([9] as varchar(50)),'')
+' '+ isnull(cast([10] as varchar(50)),'')
as ListOfQty
FROM
( SELECT shipmentnbr,companyid, splitlinenbr as splitLine , qty FROM SOShipLineSplit ) p
PIVOT ( max(qty) FOR [splitline] IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10] ) ) AS pvt
ЦАП:
using System;
using PX.Data;
namespace SoShipSplit
{
[Serializable]
public class usrSOSplit : IBqlTable
{
#region Shipmentnbr
[PXDBString(15, IsUnicode = true, InputMask = "")]
[PXUIField(DisplayName = "Shipmentnbr")]
public virtual string Shipmentnbr { get; set; }
public abstract class shipmentnbr : IBqlField { }
#endregion
#region ListOfQty
[PXDBString(509, InputMask = "")]
[PXUIField(DisplayName = "List Of Qty")]
public virtual string ListOfQty { get; set; }
public abstract class listOfQty : IBqlField { }
#endregion
}
}
14.11.2018
