1列记录(按条件)合并成1行

最新推荐文章于 2023-06-18 21:33:34 发布

威威当爸了

最新推荐文章于 2023-06-18 21:33:34 发布

阅读量503

点赞数

分类专栏： SQL 文章标签：数据库 sql

本文链接：https://blog.csdn.net/lw8014/article/details/126976795

版权

SQL 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

MSSQL—列记录合并成一行 - 走看看

自己写过的sql

SELECT 
td.JobNumber AS 'ScanJobName' ,
tb.Sn2 AS 'PcbLot_PCBAlot',
ta.扫描Sn AS 'Sensor_Serial',
tb.CreateDate AS 'CreateDate_CollectSensorSN',
ta.PairJobName,
ta.扫描时间 AS 'Scan_time',
ta.彩盒Sn AS 'Kit_SN',
ta.包装箱号 AS 'master_carton_No' ,
ta.包装时间 AS 'Packing_time',
(SELECT Sn1 + ',' FROM ScanLog where BoxId = ta.BoxID FOR XML PATH(''))
--关键在这里
FROM   
(SELECT 
       tt1.包装箱号,
       tt1.包装时间,
       tt5.JobNumber AS 'PairJobName',
       tt3.Sn2 AS Lot ,
       tt1.彩盒Sn,
       tt3.sn1 AS '扫描Sn' ,
       tt3.CreateDate AS '扫描时间',  
       tt2.Id AS 'BoxID'
FROM   
(SELECT t1.FullBoxSn AS '包装箱号', t4.CreateDate AS '包装时间' ,t4.Sn1 AS '彩盒Sn'
       FROM dbo.BoxLog AS t1,
              dbo.PositionInfo AS t2,
              dbo.Job AS t3,
              dbo.ScanLog AS t4
       WHERE t1.PositionId = t2.SortNum
                AND t1.JobId = t3.Id
                AND t1.Id = t4.BoxId
                AND t2.PostionName='Pack'
                AND t3.JobNumber='LE33914-01'
                AND t1.IsComplete=1
                AND t3.DisabledDate IS NULL
                AND t4.DisabledDate IS NULL) AS tt1,
         dbo.BoxLog AS tt2,
         dbo.ScanLog AS tt3,
         dbo.PositionInfo AS tt4,
         dbo.Job AS tt5
         
         WHERE tt1.彩盒Sn=tt2.FullBoxSn
         AND  tt2.Id=tt3.BoxId
         AND tt3.PostionId=tt4.SortNum
         AND tt5.Id=tt3.JobId
         AND tt4.PostionName='Pair'
         AND tt2.IsComplete=1
         AND tt3.DisabledDate IS NULL) AS ta,
         dbo.ScanLog AS tb,
         dbo.PositionInfo AS tc,
         dbo.Job AS td
         WHERE ta.扫描Sn=tb.Sn1
         AND tb.PostionId=tc.SortNum
         AND tb.JobId=td.Id
         AND tc.PostionName='Scan'
         AND tb.DisabledDate IS NULL
         AND tb.IsComplete=1
       ORDER BY ta.包装箱号 ,ta.彩盒Sn