优化代码批量查询得到结果，请专家答疑解惑

Question

优化代码批量查询得到结果，请专家答疑解惑
查询#test 表里的n1~~n40相同的数值，想得到的结果

 
DROP TABLE IF EXISTS  #test --  select * from #test

create table #test([MID] [int] IDENTITY(1,1) NOT NULL primary key,val INT,ID INT,ColName nvarchar(10),seq INT,n1 INT,n2 INT,n3 INT,n4 INT,n5 INT,n6 INT,n7 INT,n8 INT,n9 INT,n10 INT,n11 INT,n12 INT,n13 INT,n14 INT,n15 INT,n16 INT,n17 INT,n18 INT,n19 INT,n20 INT,n21 INT,n22 INT,n23 INT,n24 INT,n25 INT,n26 INT,n27 INT,n28 INT,n29 INT,n30 INT,n31 INT,n32 INT,n33 INT,n34 INT,n35 INT,n36 INT,n37 INT,n38 INT,n39 INT,n40 INT)  
insert into #test 
SELECT '12'Val,'43'ID,'DQ_RpNr43'ColName,'39'seq,'3'n1,'9'n2,'11'n3,'17'n4,'19'n5,'29'n6,'30'n7,'32'n8,'38'n9,'40'n10,'42'n11,'44'n12,'48'n13,'52'n14,'61'n15,'66'n16,'67'n17,'76'n18,'77'n19,'78'n20,'1'n21,'3'n22,'7'n23,'9'n24,'11'n25,'17'n26,'19'n27,'23'n28,'32'n29,'34'n30,'38'n31,'40'n32,'42'n33,'43'n34,'52'n35,'61'n36,'68'n37,'70'n38,'77'n39,'80'n40
UNION ALL SELECT '11','24','DQ_RpNr24','146','8','9','13','17','21','23','27','29','36','44','46','49','51','52','56','59','63','67','68','69','2','3','8','9','13','21','27','29','33','36','42','46','49','54','57','60','63','66','69','77'
UNION ALL SELECT '11','2','DQ_RpNr2','12','1','5','6','11','15','25','37','42','44','47','51','54','59','64','65','70','71','74','75','76','2','4','5','7','10','11','15','19','20','44','46','47','54','59','64','66','71','74','75','77'
UNION ALL SELECT '11','23','DQ_RpNr23','284','1','2','3','7','11','24','27','29','30','35','36','38','40','42','46','48','57','66','77','78','1','3','11','16','30','32','33','35','36','38','46','48','50','53','59','60','66','70','71','77'
UNION ALL SELECT '10','21','DQ_RpNr21','443','1','9','11','12','13','16','17','37','44','47','52','55','57','61','62','64','67','69','79','80','5','9','12','15','16','17','31','32','43','44','47','51','52','53','62','67','69','70','73','75'
UNION ALL SELECT '10','11','DQ_RpNr11','419','1','3','4','7','8','11','14','24','31','42','43','44','51','52','59','60','63','70','74','77','4','5','8','11','14','16','18','23','27','28','31','41','44','49','52','60','61','63','70','78'
UNION ALL SELECT '10','17','DQ_RpNr17','1','7','11','15','16','25','27','30','33','36','37','38','43','48','52','60','70','74','75','77','78','6','8','15','27','28','30','37','38','39','44','46','49','51','52','59','70','75','77','78','80'

 
DROP TABLE IF EXISTS #tt3 --  select * from #tt3

SELECT p.MID,p.val,p.ID,p.colname,p.seq, QTY
into  #tt3
from #test
unpivot (QTY for type in(n1,n2,n3,n4,n5,n6,n7,n8,n9,n10,n11,n12,n13,n14,n15,n16,n17,n18,n19,n20
,n21,n22,n23,n24,n25,n26,n27,n28,n29,n30,n31,n32,n33,n34,n35,n36,n37,n38,n39,n40 )) as p

-- 问题：如何批量一次性得到想要的结果  （以下是分别取MID=1,2,3,4,5的结果再合并得到结果）
;WITH t1 AS (
select MID,VAL,ID,ColName,seq,QTY,count(QTY)AS RPNR,'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID from #tt3  WHERE MID=1
GROUP BY  MID,VAL,ID,ColName,seq,QTY
HAVING count(QTY)>1
)
SELECT  * INTO #T1
FROM    t1 
PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;
;WITH t2 AS (
select MID,VAL,ID,ColName,seq,QTY,count(QTY)AS RPNR,'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID from #tt3  WHERE MID=2
GROUP BY  MID,VAL,ID,ColName,seq,QTY
HAVING count(QTY)>1
)
SELECT  * INTO #T2
FROM    t2 
PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;
;WITH t3 AS (
select MID,VAL,ID,ColName,seq,QTY,count(QTY)AS RPNR,'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID  from #tt3  WHERE MID=3
GROUP BY  MID,VAL,ID,ColName,seq,QTY
HAVING count(QTY)>1
)
SELECT  * INTO #T3
FROM    t3 
PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;
;WITH t4 AS (
select MID,VAL,ID,ColName,seq,QTY,count(QTY)AS RPNR,'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID  from #tt3  WHERE MID=4
GROUP BY  MID,VAL,ID,ColName,seq,QTY
HAVING count(QTY)>1
)
SELECT  * INTO #T4
FROM    t4 
PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;
;WITH t5 AS (
select MID,VAL,ID,ColName,seq,QTY,count(QTY)AS RPNR,'N' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID  from #tt3  WHERE MID=5
GROUP BY  MID,VAL,ID,ColName,seq,QTY
HAVING count(QTY)>1
)
SELECT  * INTO #T5
FROM    t5
PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;

SELECT * FROM #T1
UNION ALL
SELECT * FROM #T2
UNION ALL
SELECT * FROM #T3
UNION ALL
SELECT * FROM #T4
UNION ALL
SELECT * FROM #T5

DROP TABLE  #T1
DROP TABLE  #T2
DROP TABLE  #T3
DROP TABLE  #T4
DROP TABLE  #T5

Answer 1

 with tt as (
 select mid,val,id,colname,seq,qty,'N' +CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY val) AS VARCHAR(2)) AS RID from (
select distinct MID,VAL,ID,ColName,seq,QTY,count(1) over(partition by id,QTY) AS RPNR from #tt3
) as t where RPNR>1)
select * from tt PIVOT (   MAX(QTY)
          FOR RID IN (n1, n2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12)) p;