SQL Server Report Statistics-parameterized dynamic pivot row-to-column

List all table Structures first:

--title report--table collected water fee: hx_t_received--field charge Department Id:hx_fdepartmentid 1, Charge department Name: Hx_fdepartmentname should collect water charges information id:hx_ freceivableid--Charge Category: Hx_ftype (water fee (1): Re-divided [Open account (table) supply Category: Tap water (100000001) = = water, medium (100000002) = = in charge), agency fees (2) = = Sewage Treatment fee)-- Amount paid: Hx_freceivedamount created: CreatedOn payment method: Hx_fpayway (cash: 100,000,000 with check: 100,000,001)--Trading Status: Hx_fstate (recorded 100, 000,000: Cheque Payment 100,000,001 except: void: 100,000,002, Bank Payment: 100,000,003)--Note information: Hx_finvoiceid: Createdbyname creation time: CreatedOn primary key : hx_t_receivedid--Charge department: Hx_fdepartmentid Water meter Id:hx_fmeterid change: ModifiedOn reminder fee Clerk Id:hx_fdunid charge: createdby--table should collect water charges: hx_t_receivable--Field Water Volume Information Id:hx_fusedamountid primary key: Hx_t_receivableid customer Information: Hx_faccountid meter: hx_fmeterid--record Status: Hx_fstate (In Cheque Payment: 100,000,002)--table water: hx_t_waterusedamount--field opening information: Hx_fcustomerandmeterrelaid primary key: Hx_t_ Waterusedamountid Water Meter: Hx_fmeterid sector: Hx_fzone actual consumption: Hx_famount-The reason: Hx_festimateamountreason settlement water: hx_ fpayamount--type Hx_frecordtype (water estimate: 100,000,001 not copied see: 100,000,002 Normal: 100,000,000 Prepaid card Table Assessment water: 100,000,003)--Water status: hx_fstate (Plan: 100,000, 000, entered water volume: 100,000,001, Generated receivable: 100,000,006 the meter reading stop: hx_freading--year: hx_fyear Water month: hx_fmonth--table Customer: account--field Primary key: AccountId Customer Name: Name Customer Code: Accountnumber--table Customer account Transaction Details: hx_t_transactiondetails--field Transaction type: Hx_ftranstype (account Recharge : 100,000,000) Transaction amount: Hx_ftransamount collected water charges: Hx_freceivedid: createdby Sector: hx_fzoneid--Trading Status: Hx_fstate (void: 100, 000,002) Ticket Information: Hx_finvoiceid payment method: Hx_fpayway--table Customer Recharge Account: hx_t_rechargeaccount--primary key; Hx_t_rechargeaccountid Water meter Id:hx_ Fmeterid Customer id:hx_fbuildinaccountid--table Account information: hx_t_customerandmeterrela--section: Hx_fzoneid primary key: Hx_t_ Customerandmeterrelaid meter: Hx_fmeterid Customer: hx_faccountid--******** two ********--table temporary water harvest: hx_t_tempwaterreceived PRIMARY key: hx_t_tempwaterreceivedid--field Temporary water: Hx_ftempwaterid payment Method: Hx_fpayway (Cash: 100,000,000 with cheque: 100,000,001)--Trading Status: Hx_fstate  (except: void: 100,000,002, Bank Payment: 100,000,002)--note information: Hx_finvoiceid Charge Department id:hx_fdepartmentid--table temporary water: hx_t_tempwater--field Primary key: Hx_t_tempwaterid person in charge: ownerID Charge item: Hx_ffeeitems (water fee (re), sewage charge) Category: Hx_fwatertype section: hx_fzoneid--Water: Hx_fusedamoUnt--table User: systemuser--field primary key: Systemuserid business unit: Businessunitid Full Name: Fullname--table Department: businessunit--Field Primary key: Businessunitid Name: name--table Sector information: hx_t_zone--Field primary key: Hx_t_zoneid team information: Hx_fteamid: Hx_frecorder section Number: hx_fzone--Department: HX _fbureau: Hx_fdunname (find) department Name: Hx_fbureauname--table Levy hx_t_levyinstitute--Field primary key: Hx_t_levyinstituteid name: hx_ Fname--table Meter-Reading team attribution hx_t_teamattribution--Field primary key: Hx_t_teamattributionid meter Reading team information: Hx_fteamid Levy information: hx_ Flevyinstituteid--table Clerk: hx_t_staff--field Name: Hx_name primary key: Hx_t_staffid

SQL code:

--title report declare @begin_date datetimedeclare @end_date datetimeselect @begin_date = GETDATE () declare @hx_ Closingaccountinfoid Uniqueidentifierset @hx_ClosingAccountInfoId = ' 08afef9f-e174-46f2-855a-32f26be38f3b '--September 2014 01 issue declare @BusinessunitId uniqueidentifier--Levy SET @ Businessunitid= ' 6a2537dd-b87a-e411-93fe-002590622897 '----the 15th charge declare @TeamId uniqueidentifier--reading the table team set @TeamId = null--' 18acbd81-5b45-e411-9402-6cae8b22702d '--the 15th fee collection (scattered check list group)----------------- SQL body----------------------------------------------------------execution time: Declare @FMonth intdeclare @FYear intdeclare @ Ftimes intselect @FYear =t.hx_fyear, @FMonth =t.hx_fmonth, @FTimes =t.hx_ftimes from Hx_closingaccountinfo t where [email& Nbsp;protected]_closingaccountinfoid--select @FYear, @FMonth, @FTimesbeginif object_id (' tempdb. #t_estimateamountreason ') is not a null drop table #t_estimateamountreasonif object_id (' tempdb. #t_EstimateamountReasonTotal ') is not a null drop table #t_EstimateamountReasonTotalselect * into #t_estimateamouNtreason from (SELECT distinct T1. Label,t2. Value from Metadataschema.localizedlabel t1 inner joins metadataschema.attributepicklistvalue T2 on T1. Objectid=t2. Attributepicklistvalueid INNER join Metadataschema.optionset T3 on T2. Optionsetid=t3. Optionsetid where T3. Name= ' hx_estimateamountreason_values ' and T1. Objectcolumnname= ' DisplayName ' and T1. languageid=2052) Tselect * into #t_EstimateamountReasonTotal from (select W.hx_frecordername fullname,--meter reader W.hx_fzone Hx_fzone,--section No. 1 Mcounts,e.label Label,w.hx_fpayamount Hx_fpayamount,--Water r.hx_freceivablefee hx_freceivablefee,--  Water r.hx_fcollchargesreceivable4 wsf--sewage charges from Hx_t_waterusedamount W--Water inner JOIN team T--team on w.owningteam = T.teamid and IsNull (W.owningteam, ' 00000000-0000-0000-0000-000000000000 ') = IsNull (IsNull (@TeamId, W.owningteam), ' 00000000-0000-0000-0000-000000000000 ') and [email protected] and [email protected] and [email  Protected] and w.hx_frecordtype= ' 100000001 ' INNER JOIN hx_t_teamattribution N on T.teaMid=n.hx_fteamid and IsNull (N.hx_flevyinstituteid, ' 00000000-0000-0000-0000-000000000000 ') = IsNull (IsNull (@ Businessunitid,n.hx_flevyinstituteid), ' 00000000-0000-0000-0000-000000000000 ') inner JOIN #t_estimateamountreason E --water reason on E.value=w.hx_festimateamountreason left join hx_t_receivable R--Receivable on W.hx_t_waterusedamountid=r.hx_fusedamo Untid UNION allselect null fullname,null hx_fzone,1 mcounts,e.label label,0 hx_fpayamount,0 hx_freceivablefee,0 wsf from #t_estimateamountreason e) h--parameterized dynamic pivot row to column DECLARE @sql_str NVARCHAR (max) DECLARE @sql_col NVARCHAR (max) DECLARE @ TableName SYSNAME--row to list declare @orderColumn SYSNAME--Group field declare @row2column SYSNAME--row variable column field declare @row2columnValue SYSNAME--row Variable column value field DECLARE @OtherField NVARCHAR DECLARE @sql_col_out NVARCHAR (MAX) SET @tableName = ' #t_ Estimateamountreasontotal ' Set @orderColumn = ' Pvt.hx_fzone ' Set @row2column = ' Label ' Set @row2columnValue = ' mcounts ' Set @OtherField = ' fullname,hx_fzone,hx_fpayamount,hx_freceivablefee,wsf ' – Gets the possible memory from the row dataIn the column set @sql_str = N ' SELECT @sql_col_out = ISNULL (@sql_col_out + "," "," ") + QUOTENAME ([' [email protected]+ ']) F ROM [' [email protected]+ '] GROUP by [' [email protected]+ '] '--print @sql_strEXEC sp_executesql @sql_str, N ' @ Sql_col_out NVARCHAR (MAX) OUTPUT ', @[email protected]_col output--print @sql_colSET @sql_str = N ' SELECT * FROM (SE Lect ' [email protected]+ ', [' [email protected]+ '],[' [email protected]+ '] ' + ' from [' [email   protected]+ ']) p PIVOT (SUM ([' [email protected]+ ']) for [' [email protected]+ '] in (' + @sql_col + ')) as Pvt Where Pvt.hx_fzone is not null for ORDER by ' [Email protected]--print (@sql_str) EXEC (@sql_str) drop table #t_estimateamo Untreasondrop Table #t_ Estimateamountreasontotalend---------------------------------------------------------------------------SELECT @ end_date = getdate () Select DateDiff (MS, @begin_date, @end_date) as ' spents/msec '

SQL Server Report Statistics-parameterized dynamic pivot row-to-column