sql server 通过建立临时表的方法查询行转列重复字段数据

IF OBJECT_ID(‘tempdb.dbo.##newltable’) IS NOT NULL DROP TABLE ##newltable;
use archivesmgr2
SELECT aaa.* into ##newltable
from ( select id,Cast (题名 as Varchar(max)) as ‘f1’,Cast (页号 as Varchar(max)) as ‘f2’,Cast (文件日期 as Varchar(max)) as ‘f3’,reference
FROM
arc_archives
LEFT JOIN (
SELECT
MAX ( CASE field_id WHEN ‘402881f96715acbe016715d9d25a009a’ THEN VALUE END ) AS ‘题名’,
MAX ( CASE field_id WHEN ‘402881f96715acbe016715d9d27b009c’ THEN VALUE END ) AS ‘页号’,
MAX ( CASE field_id WHEN ‘402881f96715acbe016715d9d269009b’ THEN VALUE END ) AS ‘文件日期’,
a.archives_id
FROM
arc_fieldvalue a
GROUP BY
a.archives_id
) c ON c.archives_id = arc_archives.id
WHERE
isDelete = 0
AND isarchived = ‘1’
AND filetype_id = ‘402881f96715acbe016715cbd5910072’
AND isDelete = 0) aaa
SELECT *
FROM ##newltable T1
WHERE EXISTS(SELECT 1 FROM ##newltable T2 WHERE T2.id!=T1.id AND T2.f1=T1.f1 AND T2.f2=T1.f2 AND T2.f3=T1.f3)