34,838
社区成员
发帖
与我相关
我的任务
分享行转列问题,写不清楚,求助
原始sql如下:
我写成如下内容:
提示语法错误?
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)
提示语法错误?
...全文
请发表友善的回复…
发表回复
唐诗三百首 2019-01-28
- 打赏
- 举报
Try this,
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)
钛元素 2019-01-28
- 打赏
- 举报
可是,已经里面已经关联了啊,ADMIN_UNIT 取 ADMIN_UNIT.admin_unit_name
我还是改不了
有点不懂,请教
我还是改不了
有点不懂,请教
唐诗三百首 2019-01-28
- 打赏
- 举报
需在动态SQL里关联部门表.
钛元素 2019-01-28
- 打赏
- 举报
版主高手!版主大大!
可是这样,一来无法筛选日期,二来无法列出部门名称,是需要再次关联吗?
另外如何学习,才能到你这种程度啊?
可是这样,一来无法筛选日期,二来无法列出部门名称,是需要再次关联吗?
另外如何学习,才能到你这种程度啊?
钛元素 2019-01-28
- 打赏
- 举报
非常感谢唐诗版主的帮助,表结构很复杂,虽然我也可以生成sql脚本出来,但是关联太多了,数据insert的方式更是麻烦,还是不麻烦了.谢谢.
我决定生成表后,再处理.
我决定生成表后,再处理.
唐诗三百首 2019-01-28
- 打赏
- 举报
请提供表结构, 测试数据, 处理逻辑和希望结果.
钛元素 2019-01-28
- 打赏
- 举报
这样的话,部门会重复很多, 记录也多条了 无法group by了吗?
或者我们再做一个sql 再关联?
唐诗三百首 2019-01-26
- 打赏
- 举报
每个自然日做列的写法,
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)
唐诗三百首 2019-01-26
- 打赏
- 举报
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)
钛元素 2019-01-25
- 打赏
- 举报
改过了,一样的,我现在这样:
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')
提示语法错误,如果我去除convert,又提示我:
数据类型 nvarchar 和 datetime2 在 add 运算符中不兼容。
吉普赛的歌 版主 2019-01-25
- 打赏
- 举报
在微软官方文档中， ntext 属于淘汰类型， 建议改成 nvarchar(max)
https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-2017
你改类型了再试呢？
钛元素 2019-01-25
- 打赏
- 举报
不过我@JZRQ的内容是这样:[2018-08-31],[2018-09-30],[2018-10-01],[2018-10-19],[2018-10-22],[2018-10-23],[2018-10-24],[2018-10-29],[2018-10-30],[2018-10-31]
要怎么办?我就是想要日期列做列啊
另外,最好每个自然日做列,值为空没关系,这是最好的了.怎么做啊?
要怎么办?我就是想要日期列做列啊
另外,最好每个自然日做列,值为空没关系,这是最好的了.怎么做啊?
钛元素 2019-01-25
- 打赏
- 举报
改成这样
提示:
Pivot grouping 列必须是可比的。列 "Attachments" 的类型是 "ntext"，该类型不可比。
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)
提示:
Pivot grouping 列必须是可比的。列 "Attachments" 的类型是 "ntext"，该类型不可比。
