In the figure above is a table that I have, I need to make a table that is below. That is, it is necessary that if in the column "Symbol" i "Nazwa" the same values ​​were combined into one line, the column "Numer Docymenty" was separated through a comma, since there are always different values, and accordingly the other columns were summed up if a connection occurred, Count " , " Wart.Netto " and so on.

Tabls

    1 answer 1

    Judging by your picture, you have several tasks.

    1. Group by Symbol, Nazwa . Actually GROUP BY Symbol, Nazwa
    2. Renumber the id field. If gaps are not important to you, then just MIN(id) , otherwise ROW_NUMBER( )
    3. Combine strings of Number Docymenty separated by commas. In sql server there are no built-in aggregate functions for this. Here either use a subquery with FOR XML PATH('') , or write your own aggregate function using CLR .
    4. For the Jm field, it is not clear what to do if there are different values ​​in the group. If it doesn't matter, take any ( max or min from the group)
    5. Sum the remaining numeric fields. This is the usual SUM() .

    What exactly you can not do yourself?

    UPD: I looked through your query, you do not need to make your own subquery for each of the columns Stan, Rezerwacja, Dostepne . You can count everything in one OUTER APPLY :

     USE E_M_KOPIA SELECT --tw_Rodzaj as Rodzaj max(tw_Symbol) As Symbol , max(tw_PodstKodKresk) As EAN , max(tw_Nazwa) as Nazwa , max(tw_Pole3) As Localizacja , (select dok_NrPelny + ', ' FROM dok__Dokument AS dd LEFT JOIN dok_Pozycja AS dp ON dp.ob_DokHanId = dd.dok_Id LEFT JOIN tw__Towar AS tt ON tt.tw_Id = dp.ob_TowId WHERE dd.dok_Id = [dbo].dok__Dokument.dok_Id For xml path ('') )as NymerDokymentu -- Roma Vav 16 июл в 14:23 upvote тревога , SUM (ob_Ilosc* ob_znak) AS Ilosc , OA.Stan, OA.Rezerwacja, OA.Dostepne --, adr_Nazwa [Kontrahent] FROM [dbo].dok__Dokument -- Roma Vav 16 июл в 14:24 INNER JOIN [dbo].dok_Pozycja ON dok_Id = ob_DokHanId INNER JOIN [dbo].tw__Towar ON ob_TowId = tw_Id LEFT JOIN kh__Kontrahent ON dok__Dokument.dok_PlatnikId=kh__Kontrahent.kh_Id LEFT JOIN adr__Ewid ON kh_Id=adr_IdObiektu AND adr_TypAdresu=1 OUTER APPLY( SELECT SUM(st_Stan) AS Stan, SUM(st_StanRez) AS Rezerwacja, SUM(st_Stan - st_StanRez) AS Dostepne FROM [dbo].tw_stan WHERE st_TowId = ob_TowId GROUP BY st_TowId )OA WHERE ( (dok_Status in (7)) and (dok_typ in (16)) ) GROUP BY tw_Symbol,tw_Id,dok_Id,ob_TowId,ob_Id ORDER BY Symbol -- Roma Vav 16 июл в 14:24
    • USE E_M_KOPIA SELECT - tw_Rodzaj as Rodzaj max (tw_Symbol) As Symbol, max (tw_PodstKodKresk) As EAN, max (tw_Nazwa) as Nazwa, max (tw_Pole3) As Localizacja, (select dok_NrPelny + ',' FROM dok do T ment ment ment ment ment; AS dp ON dp.ob_DokHanId = dd.dok_Id LEFT JOIN tw__Towar AS
    • , SUM (ob_Ilosc * ob_znak) AS Ilosc, (SELECT SUM (st_Tan) FROM [dbo] .tw_stan WHERE BY st_TowId) AS Rezerwacja, (SELECT SUM)
    • INNER JOIN [dbo] .dok_Pozycja ON dok_Id = ob_DokHanId INNER JOIN [dbo] .tw__Towar ON ob_TowId = tw_Id LEFT JOIN kh__Kontrahent ON dok__Dokument.dok_PlatnikId = kh__Kontrahent.kh_Id LEFT JOIN adr__Ewid ON kh_Id = adr_IdObiektu AND adr_TypAdresu = 1 WHERE ((dok_Status in ( 7)) and (dok_typ in (16))) GROUP BY tw_Symbol, tw_Id, dok_Id, ob_TowId, ob_Id ORDER BY Symbol - Roma Vav
    • The column "NymerDokymentu" does not work out and does not want to connect. This is what happens here. And it should have connected the fields with the same name and symbol - Roma Vav
    • @RomaVav, add your query to the question - pegoopik