របៀប Select Crosstab Query on SQL Server

How to select data with cross-tab query with MS. Sql Server

របៀបច្រោះយកទិន្ន័យមកបង្ហាញតាមខែនីមួយៗ Cross-Tab Query ជាមួយ Ms. Sql Server

1/ Cross-tab Query

/***** 2- Select data by crosstab *****/

SELECT EmployeeID,SalesmanName,YYYY,MM,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],

                [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],

                [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]

FROM
(SELECT Distinct EmployeeID,SalesmanName, DATEPART(day,InvoiceDate) AS DD, InvoiceNo,
        DATEPART(YEAR,InvoiceDate) AS YYYY, DATEPART(MONTH,InvoiceDate) AS MM
 FROM dbo.tab_FM_SalesData Where DATEDIFF(MM,invoiceDate,@StartDate)=0
) ps
PIVOT
(
    Count(InvoiceNo)
    FOR DD IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],
                           [17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) AS pvt

/***** End Select data by crosstab *****/

2/ FULL Coding

Declare @StartDate date='20170901'

Declare @EndDate date='20170930'
IF OBJECT_ID('tempdb..#tmpSaleFM') IS NOT NULL
 DROP TABLE #tmpSaleFM

Create Table #tmpSaleFM
(
EmpID nvarchar(32),
Salesman nvarchar(50),
YYYY nvarchar(4),
MM nvarchar(2),
D1 int,D2 int,D3 int,D4 Int, D5 Int, D6 Int,D7 Int,D8 Int,D9 Int,D10 Int,D11 Int,D12 Int,D13 Int,D14 Int, D15 Int,
D16 int,D17 int,D18 int,D19 Int, D20 Int, D21 Int,D22 Int,D23 Int,D24 Int,D25 Int,D26 Int,D27 Int,D28 Int,D29 Int,
D30 Int,D31 Int
)

/* end tempory table*/

/***** 2- Select data by crosstab *****/
Truncate table #tmpSaleFM
Insert Into #tmpSaleFM

SELECT EmployeeID,SalesmanName,YYYY,MM,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],
[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]
FROM
(SELECT Distinct EmployeeID,SalesmanName, DATEPART(day,InvoiceDate) AS DD, InvoiceNo,
        DATEPART(YEAR,InvoiceDate) AS YYYY, DATEPART(MONTH,InvoiceDate) AS MM
 FROM dbo.tab_FM_SalesData Where DATEDIFF(MM,invoiceDate,@StartDate)=0
) ps
PIVOT
(
    Count(InvoiceNo)
    FOR DD IN ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],
               [17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) AS pvt

/***** End Select data by crosstab *****/

Declare @WD Float
Set @WD=dbo.fn_getWorkingDays(@StartDate,@EndDate)

/***** 3- Select Data to Show *****/
Select s.Channel,s.SalesCode,s.Salesman,s.Type,s.Code,s.Province,
        (s.TotalOutlet * s.VisitPlan) AS 'Total Call',
       
  Round((IsNull(tmp.D1,0)+IsNull(tmp.D2,0)+IsNull(tmp.D3,0)+IsNull(tmp.D4,0)+IsNUll(tmp.D5,0)+
        IsNull(tmp.D6,0)+IsNull(tmp.D7,0)+IsNull(tmp.D8,0)+IsNull(tmp.D9,0)+IsNUll(tmp.D10,0)+
        IsNull(tmp.D11,0)+IsNull(tmp.D12,0)+IsNull(tmp.D13,0)+IsNull(tmp.D14,0)+IsNUll(tmp.D15,0)+
        IsNull(tmp.D16,0)+IsNull(tmp.D17,0)+IsNull(tmp.D18,0)+IsNull(tmp.D19,0)+IsNUll(tmp.D20,0)+
        IsNull(tmp.D21,0)+IsNull(tmp.D22,0)+IsNull(tmp.D23,0)+IsNull(tmp.D24,0)+IsNUll(tmp.D25,0)+
        IsNull(tmp.D26,0)+IsNull(tmp.D27,0)+IsNull(tmp.D28,0)+IsNull(tmp.D29,0)+IsNUll(tmp.D30,0)+IsNUll(tmp.D31,0))/@WD,0) AS 'Average Call/Day',
       
        (IsNull(tmp.D1,0)+IsNull(tmp.D2,0)+IsNull(tmp.D3,0)+IsNull(tmp.D4,0)+IsNUll(tmp.D5,0)+
        IsNull(tmp.D6,0)+IsNull(tmp.D7,0)+IsNull(tmp.D8,0)+IsNull(tmp.D9,0)+IsNUll(tmp.D10,0)+
        IsNull(tmp.D11,0)+IsNull(tmp.D12,0)+IsNull(tmp.D13,0)+IsNull(tmp.D14,0)+IsNUll(tmp.D15,0)+
        IsNull(tmp.D16,0)+IsNull(tmp.D17,0)+IsNull(tmp.D18,0)+IsNull(tmp.D19,0)+IsNUll(tmp.D20,0)+
        IsNull(tmp.D21,0)+IsNull(tmp.D22,0)+IsNull(tmp.D23,0)+IsNull(tmp.D24,0)+IsNUll(tmp.D25,0)+
        IsNull(tmp.D26,0)+IsNull(tmp.D27,0)+IsNull(tmp.D28,0)+IsNull(tmp.D29,0)+IsNUll(tmp.D30,0)+IsNUll(tmp.D31,0)) AS 'Effective Call',
       
 CONVERT(DECIMAL(16,0),((CONVERT(DECIMAL(16,2),(IsNull(tmp.D1,0)+IsNull(tmp.D2,0)+IsNull(tmp.D3,0)+IsNull(tmp.D4,0)+IsNUll(tmp.D5,0)+
        IsNull(tmp.D6,0)+IsNull(tmp.D7,0)+IsNull(tmp.D8,0)+IsNull(tmp.D9,0)+IsNUll(tmp.D10,0)+
        IsNull(tmp.D11,0)+IsNull(tmp.D12,0)+IsNull(tmp.D13,0)+IsNull(tmp.D14,0)+IsNUll(tmp.D15,0)+
        IsNull(tmp.D16,0)+IsNull(tmp.D17,0)+IsNull(tmp.D18,0)+IsNull(tmp.D19,0)+IsNUll(tmp.D20,0)+
        IsNull(tmp.D21,0)+IsNull(tmp.D22,0)+IsNull(tmp.D23,0)+IsNull(tmp.D24,0)+IsNUll(tmp.D25,0)+
        IsNull(tmp.D26,0)+IsNull(tmp.D27,0)+IsNull(tmp.D28,0)+IsNull(tmp.D29,0)+IsNUll(tmp.D30,0)+IsNUll(tmp.D31,0))) /CONVERT(DECIMAL(16,2),(s.TotalOutlet * s.VisitPlan)))*100)) As '% Effective',
       
       IsNull(tmp.D1,0) AS '1',IsNull(tmp.D2,0) AS '2',IsNull(tmp.D3,0) AS '3',IsNull(tmp.D4,0) AS '4',IsNUll(tmp.D5,0) AS '5',
       ISNull(tmp.D6,0) AS '6',IsNull(tmp.D7,0) AS '7',IsNUll(tmp.D8,0) AS '8',IsNUll(tmp.D9,0) AS '9',ISNull(tmp.D10,0) AS '10',
       IsNull(tmp.D11,0) AS '11',IsNull(tmp.D12,0) AS '12',IsNull(tmp.D13,0) AS '13',IsNUll(tmp.D14,0) AS '14',IsNull(tmp.D15,0) AS '15',
       IsNull(tmp.D16,0) AS '16',IsNull(tmp.D17,0) AS '17',IsNull(tmp.D18,0) AS '18',IsNUll(tmp.D19,0) AS '19',IsNull(tmp.D20,0) AS '20',
       IsNull(tmp.D21,0) AS '21',IsNull(tmp.D22,0) AS '22',IsNull(tmp.D23,0) AS '23',IsNUll(tmp.D24,0) AS '24',IsNull(tmp.D25,0) AS '25',
       ISNull(tmp.D26,0) AS '26',IsNull(tmp.D27,0) AS '27',IsNUll(tmp.D28,0) AS '28',ISNull(tmp.D29,0) AS '29',IsNull(tmp.D30,0) AS '30',
       ISNull(tmp.D31,0) AS '31'
     
From dbo.tab_FM_Salesman As s
Left Outer Join #tmpSaleFM As tmp ON s.SalesCode=tmp.EmpID

END Full Coding

Thanks