时间戳

首页 > 解决方案 > 用“-”分隔列（用于 xml 路径）

sql - 用“-”分隔列（用于 xml 路径）

问题描述

我想用“-”分隔查询中的“KaliteKodu”列，但它不起作用。在查询结果中，对于 UrunID = 19241，质量代码将如下所示

ProsesID  UrunID  Miktar           Tarih            LotNo      KaliteKodu
   1       19241   216     2020-11-20 00:00:00     FHM010901    A-A1-A2

询问 ：

select t.ProsesID,t.UrunID,sum(t.Miktar_Ad) Miktar,t.Tarih,t.LotNo,


KaliteKodu = stuff (
(select '-' + t.KaliteKodu 
    from IT07_GerCikanMalzeme GCM2 
    INNER JOIN IT06_StokKarti SK ON GCM2.UrunID = SK.UrunID
    INNER JOIN IT06_LotM LM ON SK.LotID = LM.LotID
    inner join IT01_KaliteKodlari KK ON GCM2.KaliteID = KK.KaliteID
    where GCM2.ProsesID = 2 and convert(date,GCM2.Tarih) = convert(date,GETDATE()) AND 
    GCM2.KaliteID in (select KaliteID from IT01_KaliteKodlari where KaliteGrupID = 1 and ITActive = 1) and 
    (LM.LotNo not in  (select m.LotNo from ZKF_IT01_FinKonM m where ProsesID = 2 and m.LotNo is not null)) AND GCM2.UrunID = t.UrunID
    FOR XML PATH (''))
         , 1, 1, '')


from
(
select GCM.ProsesID,GCM.UrunID,GCM.Miktar_Ad,GCM.Tarih,LM.LotNo , KK.KaliteKodu



    from IT07_GerCikanMalzeme GCM 
    INNER JOIN IT06_StokKarti SK ON GCM.UrunID = SK.UrunID
    INNER JOIN IT06_LotM LM ON SK.LotID = LM.LotID
    inner join IT01_KaliteKodlari KK ON GCM.KaliteID = KK.KaliteID
    where GCM.ProsesID = 2 and convert(date,GCM.Tarih) = convert(date,GETDATE()) AND 
    GCM.KaliteID in (select KaliteID from IT01_KaliteKodlari where KaliteGrupID = 1 and ITActive = 1) and 
    (LM.LotNo not in  (select m.LotNo from ZKF_IT01_FinKonM m where ProsesID = 2 and m.LotNo is not null))
    --group by ProsesID,GCM.UrunID,lm.LotNo,GCM.Tarih, KK.KaliteKodu

    ) t

    group by t.ProsesID,t.UrunID,t.Tarih,t.LotNo,t.KaliteKodu
        group by t.ProsesID,t.UrunID,t.Tarih,t.LotNo,t.KaliteKodu

结果

标签： sqlsql-serverfor-xml-path

解决方案

有一个错过的加入，比如

FROM TE t2 WHERE t.ProsesID = t2.ProsesID

使用的脚本

    --CREATE TABLE TE (ProsesID int, UrunID int, Miktar int, tarih DateTime, LotNo varchar(20), KaliteKodu varchar(3))
    --INSERT INTO TE VALUES (2, 19241, 72, '2020-11-20', 'fhm010901', 'A')
    --INSERT INTO TE VALUES (2, 19241, 72, '2020-11-20', 'fhm010901', 'A1')
    --INSERT INTO TE VALUES (2, 19241, 72, '2020-11-20', 'fhm010901', 'A2')
    --INSERT INTO TE VALUES (2, 19241, 72, '2020-11-20', 'fhm010901', 'A')


SELECT t.ProsesID, t.UrunID, t.Tarih, t.LotNo, KaliteKodu = stuff((
                SELECT '-' + t2.KaliteKodu
                FROM (select distinct t3.KaliteKodu, t3.ProsesID from TE t3 WHERE t.ProsesID = t3.ProsesID ) t2
                FOR XML PATH('')
                ), 1, 1, '')
    FROM TE t
    GROUP BY t.ProsesID, t.UrunID, t.Tarih, t.LotNo

TE 等于您的代码

from
(
select GCM.ProsesID,GCM.UrunID,GCM.Miktar_Ad,GCM.Tarih,LM.LotNo , KK.KaliteKodu
from IT07_GerCikanMalzeme GCM 
INNER JOIN IT06_StokKarti SK ON GCM.UrunID = SK.UrunID
INNER JOIN IT06_LotM LM ON SK.LotID = LM.LotID
inner join IT01_KaliteKodlari KK ON GCM.KaliteID = KK.KaliteID
where GCM.ProsesID = 2 and convert(date,GCM.Tarih) = convert(date,GETDATE()) AND 
GCM.KaliteID in (select KaliteID from IT01_KaliteKodlari where KaliteGrupID = 1 and ITActive = 1) and 
(LM.LotNo not in  (select m.LotNo from ZKF_IT01_FinKonM m where ProsesID = 2 and m.LotNo is not null))
--group by ProsesID,GCM.UrunID,lm.LotNo,GCM.Tarih, KK.KaliteKodu

) t

结果 ：

+----------+--------+-------------------------+-----------+------------+
| ProsesID | UrunID | Tarih                   | LotNo     | KaliteKodu |
+----------+--------+-------------------------+-----------+------------+
| 2        | 19241  | 2020-11-20 00:00:00.000 | fhm010901 | A-A1-A2    |
+----------+--------+-------------------------+-----------+------------+

推荐阅读