wwwwgou兄，求去重复值
@fieldlist重复，求去重复
CREATE PROC up_yourprocname
@emp_id varchar(100),
@on_date CHAR(6),
@end_date CHAR(6),
@title NVARCHAR(500)
AS
SET NOCOUNT ON
DECLARE @SQL2 VARCHAR(MAX)
declare @fieldlist varchar(8000)
SET @fieldlist=
STUFF(
(
select ',' + QUOTENAME(wage_subject_name) from wage_subject
where wage_subject_id in (select distinct(a.wage_subject_id) from wage_set_val a,wage_set b
where b.voucher_id=a.voucher_id and a.emp_id=@emp_id and a.voucher_id in (select voucher_id from wage_set
where data_month>=@on_date and data_month<=@end_date AND CHARINDEX(','+title+',', ','+@title+',') > 0) ) --and a.voucher_id='4'
group by wage_subject_id,wage_subject_name order by wage_subject_id FOR XML PATH('')
),1,1,'')
--SELECT @fieldlist
--确保你的@sql格式没问题,然后只能动态拼SQL
SET @SQL2 = '
SELECT *
FROM
(
SELECT
b.title 标题,
f.wage_set_name 发放类别 ,
b.data_month 月份,
-- d.dept_id ,
-- a.emp_id ,
-- d.note_info ,
e.wage_subject_name ,
a.val
FROM wage_set_val a ,
wage_set b ,
ebs_v c ,
wage_set_emp d,
wage_subject E,
wage_set_type f
WHERE a.voucher_id = b.voucher_id
and e.wage_subject_id=a.wage_subject_id
AND b.voucher_id = c.voucher_id
AND c.voucher_id = d.voucher_id
and b.wage_set_type=f.wage_set_type
AND a.emp_id = d.emp_id
AND b.data_month BETWEEN '''+@on_date+''' AND '''+@end_date+'''
and a.emp_id='''+@emp_id+'''
and b.title in (''部室基础工资'',''领导效益工资'')
AND d.dept_id IN (
SELECT dept_id
FROM emp_dept
WHERE company_id =''a'' )
) a
PIVOT
(
MAX(val) FOR wage_subject_name IN('+ @fieldlist +')
) b
order by 月份
'
print @sql2
EXEC (@sql2)
GO
--测试
EXEC up_yourprocname
@emp_id = '0001',
@on_date = '201305',
@end_date = '201308',
@title = '部室基础工资,领导效益工资'
[解决办法]
CREATE TABLE #wage_subject (wage_subject_id INT, wage_subject_name VARCHAR(100))
INSERT #wage_subject
SELECT 1, 'A工资' UNION ALL
SELECT 2, 'B工资' UNION ALL
SELECT 3, 'A工资' UNION ALL
SELECT 4, 'A工资'
SELECT ',' + QUOTENAME(wage_subject_name)
FROM #wage_subject
GROUP BY wage_subject_name
ORDER BY MIN(wage_subject_id)--把这儿改成:min(wage_subject_id)
FOR XML PATH('')
--实际SQL参考如下:
DECLARE @SQL2 VARCHAR(MAX)
declare @fieldlist varchar(8000)
SET @fieldlist=
STUFF(
(
select ',' + QUOTENAME(wage_subject_name) from wage_subject
where wage_subject_id in (select distinct(a.wage_subject_id) from wage_set_val a,wage_set b
where b.voucher_id=a.voucher_id and a.emp_id=@emp_id and a.voucher_id in (select voucher_id from wage_set
where data_month>=@on_date and data_month<=@end_date AND CHARINDEX(','+title+',', ','+@title+',') > 0) ) --and a.voucher_id='4'
group by wage_subject_name order by MIN(wage_subject_id) FOR XML PATH('')
),1,1,'')
