34,576
社区成员
发帖
与我相关
我的任务
分享
SELECT C.ADMIN_UNIT_CODE,C.ADMIN_UNIT_NAME AS [部门],CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120) AS [记账日期],
CONVERT(decimal(18,4),SUM(B.FUNCTION_CURRENCY_AMT)) AS [本币金额]
FROM dbo.VOUCHER A JOIN dbo.VOUCHER_D B ON B.VOUCHER_ID = A.VOUCHER_ID
JOIN dbo.ADMIN_UNIT C ON C.ADMIN_UNIT_ID = B.ADMIN_UNIT_ID
WHERE A.BOOKKEEPING_DATE BETWEEN '2018-12-01' AND '2018-12-31'
GROUP BY C.ADMIN_UNIT_NAME, A.BOOKKEEPING_DATE, C.ADMIN_UNIT_CODE
ORDER BY C.ADMIN_UNIT_CODE
DECLARE @JZRQ VARCHAR(4000),
@SQL1 VARCHAR(4000)
SELECT @JZRQ = ISNULL(@JZRQ+',','')+ QUOTENAME(CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120)) FROM dbo.VOUCHER A GROUP BY A.BOOKKEEPING_DATE
SET @SQL1 = 'SELECT *
FROM dbo.VOUCHER A JOIN dbo.VOUCHER_D B ON B.VOUCHER_ID = A.VOUCHER_ID
JOIN dbo.ADMIN_UNIT C ON C.ADMIN_UNIT_ID = B.ADMIN_UNIT_ID
PIVOT(SUM(SUM(B.FUNCTION_CURRENCY_AMT)) FOR CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120) IN( '+ @JZRQ + '))X'
EXEC(@SQL1)
declare @JZRQ nvarchar(max),
@SQL1 nvarchar(max)
select @JZRQ=isnull(@JZRQ+N',',N'')
+quotename(convert(nvarchar,dateadd(d,number,(select min(BOOKKEEPING_DATE) from dbo.VOUCHER)),23))
from master.dbo.spt_values
where type=N'P'
and number<=datediff(d,(select min(BOOKKEEPING_DATE) from dbo.VOUCHER),
(select max(BOOKKEEPING_DATE) from dbo.VOUCHER))
set @SQL1=N'select admin_unit_name,VOUCHER_ID,'+@JZRQ
+N'from (select C.admin_unit_name,A.VOUCHER_ID,A.BOOKKEEPING_DATE,B.FUNCTION_CURRENCY_AMT
from dbo.VOUCHER A
inner join dbo.VOUCHER_D B on A.VOUCHER_ID=B.VOUCHER_ID
inner join dbo.ADMIN_UNIT C on B.ADMIN_UNIT_ID=C.ADMIN_UNIT_ID) t
pivot(sum(t.FUNCTION_CURRENCY_AMT) for t.BOOKKEEPING_DATE in('+@JZRQ+N')) X'
exec(@SQL1)
declare @JZRQ nvarchar(max),
@SQL1 nvarchar(max)
select @JZRQ=isnull(@JZRQ+N',',N'')
+quotename(convert(nvarchar,dateadd(d,number,(select min(BOOKKEEPING_DATE) from dbo.VOUCHER)),23))
from master.dbo.spt_values
where type=N'P'
and number<=datediff(d,(select min(BOOKKEEPING_DATE) from dbo.VOUCHER),
(select max(BOOKKEEPING_DATE) from dbo.VOUCHER))
set @SQL1=N'select VOUCHER_ID,'+@JZRQ
+N'from (select A.VOUCHER_ID,A.BOOKKEEPING_DATE,B.FUNCTION_CURRENCY_AMT
from dbo.VOUCHER A
inner join dbo.VOUCHER_D B on A.VOUCHER_ID=B.VOUCHER_ID
inner join dbo.ADMIN_UNIT C on B.ADMIN_UNIT_ID=C.ADMIN_UNIT_ID) t
pivot(sum(t.FUNCTION_CURRENCY_AMT) for t.BOOKKEEPING_DATE in('+@JZRQ+N')) X'
exec(@SQL1)
declare @JZRQ nvarchar(max),
@SQL1 nvarchar(max)
select @JZRQ=isnull(@JZRQ+N',',N'')
+quotename(convert(nvarchar,BOOKKEEPING_DATE,23))
from dbo.VOUCHER
group BY BOOKKEEPING_DATE
set @SQL1=N'select VOUCHER_ID,'+@JZRQ
+N'from (select A.VOUCHER_ID,A.BOOKKEEPING_DATE,B.FUNCTION_CURRENCY_AMT
from dbo.VOUCHER A
inner join dbo.VOUCHER_D B on A.VOUCHER_ID=B.VOUCHER_ID
inner join dbo.ADMIN_UNIT C on B.ADMIN_UNIT_ID=C.ADMIN_UNIT_ID) t
pivot(sum(t.FUNCTION_CURRENCY_AMT) for t.BOOKKEEPING_DATE in('+@JZRQ+N')) X'
exec(@SQL1)
DECLARE @SQL3 nVARCHAR(4000)
SELECT @sql3 = ISNULL(@sql3 + '],[' , '') + CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120) FROM VOUCHER A JOIN dbo.VOUCHER_D B ON B.VOUCHER_ID = A.VOUCHER_ID GROUP BY A.BOOKKEEPING_DATE
SET @sql3 = '[' + @sql3 + ']'
EXEC ('select * from
SELECT * FROM (
SELECT C.ADMIN_UNIT_CODE,C.ADMIN_UNIT_NAME AS [部门],CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120) AS [记账日期],
CONVERT(decimal(18,4),SUM(B.FUNCTION_CURRENCY_AMT)) AS [本币金额]
FROM dbo.VOUCHER A JOIN dbo.VOUCHER_D B ON B.VOUCHER_ID = A.VOUCHER_ID
JOIN dbo.ADMIN_UNIT C ON C.ADMIN_UNIT_ID = B.ADMIN_UNIT_ID
WHERE A.BOOKKEEPING_DATE BETWEEN ''2018-12-01'' AND ''2018-12-31''
GROUP BY C.ADMIN_UNIT_NAME, A.BOOKKEEPING_DATE, C.ADMIN_UNIT_CODE
--ORDER BY C.ADMIN_UNIT_CODE
) X
pivot (SUM(B.FUNCTION_CURRENCY_AMT) for A.BOOKKEEPING_DATE in (' + @sql3 + ')) b')
DECLARE @JZRQ VARCHAR(4000),
@SQL1 VARCHAR(4000)
SELECT @JZRQ = ISNULL(@JZRQ+',','')+ QUOTENAME(CONVERT(VARCHAR(10),A.BOOKKEEPING_DATE,120)) FROM dbo.VOUCHER A GROUP BY A.BOOKKEEPING_DATE
SET @SQL1 = 'SELECT *
FROM dbo.VOUCHER A JOIN dbo.VOUCHER_D B ON B.VOUCHER_ID = A.VOUCHER_ID
JOIN dbo.ADMIN_UNIT C ON C.ADMIN_UNIT_ID = B.ADMIN_UNIT_ID
PIVOT(SUM(B.FUNCTION_CURRENCY_AMT) FOR A.BOOKKEEPING_DATE IN( '+ @JZRQ + '))X'
EXEC(@SQL1)