Search code examples
sqlsql-serverfor-xml-path

separating a column with '-' (for xml path)

I want to separate the 'KaliteKodu' column in my query with '-' but it doesn't work. In the result of the query, the Quality Code will look like this for UrunID = 19241

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

Query :

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

RESULT

Solution

  • There is a missed join like

    FROM TE t2 WHERE t.ProsesID = t2.ProsesID

    The used script

        --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 is equal to your code

    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

    Result :

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