CODEWITHSUNDEEP
sql-servergroup-concat
Manu Kast
Manu Kast

Reputation: 3

Group multiple data in result on ms sql

I'm trying to group multiple results queries in one line but didn't find an other way than the following. But I'm not truly satisfied with this solution. Is there a more elegant solution for that?

The situation a classic n:m mapping

Table 1 - Basis

Table 1 similar to this one

| ID_Basis(PK) | Name |
|--------------|------|
|  1           | One  |
|  2           | Two  |
|  3           | Three|

Table 2 - Mapping

| ID_Basis(FK) | ID_AddOn (FK) | note    |
|--------------|---------------|---------|
|  1           | 1             | some    |
|  1           | 2             | example |
|  5           | 1             | more    |
|  5           | 3             | random  |
|  5           | 4             | data    |

Table 3 - AddOn

This one is actually not used for retrieving data in this specific case but shown for completeness

| ID_AddOn(PK) | AddOn Name|
|--------------|-----------|
|  1           | something | 
|  2           | extra     |
|  3           | ...       |
|  4           | ...       |

The goal

Now I'd like to retrieve all data from table 1 (connected with all given addons from table 3) with the use of table 2 and concatenate all notes in one field in the result.

| ID_Basis     | Note               |
|--------------|--------------------|
|  1           | some, example      | 
|  3           | more, random, data |

I came to the following solution:

 SELECT b.ID_Basis,
        b.Name,
        m1.note + m2.note + m3.note + m4.note

 FROM Basis AS b
    LEFT OUTER JOIN Mapping AS m1 ON b.ID_Basis = m1.ID_Basis AND m1.ID_Addon = 1
    LEFT OUTER JOIN Mapping AS m2 ON b.ID_Basis = m2.ID_Basis AND m2.ID_Addon = 2
    LEFT OUTER JOIN Mapping AS m3 ON b.ID_Basis = m3.ID_Basis AND m3.ID_Addon = 3
    LEFT OUTER JOIN Mapping AS m4 ON b.ID_Basis = m4.ID_Basis AND m4.ID_Addon = 4

Whenever there would be a new AddOn this solution will fail. Wouldn't something like group_concat work better here? However MS SQL does not support that.

Attachment - Full example

  SELECT
    dbo.Kalkulation.KalkulationsID_PK,
    dbo.Kalkulationseingaben.Format_Breite,
    dbo.Kalkulationseingaben.Format_Hoehe,
    dbo.Kalkulationseingaben.Auflage,
    dbo.Kalkulation.MaschinenID_FK,
    dbo.Kalkulation.Druckverfahrensbezeichnung_FK,
    dbo.Kalkulationsergebnis.Einkaufspreis, 
    dbo.Kalkulation.Ersteller_MitarbeiterID_FK,
    dbo.Kalkulation.Bearbeiter_MitarbeiterID_FK,
    Ersteller.Nutzername AS Ersteller_Nutzername,
    Bearbeiter.Nutzername AS Bearbeiter_Nutzername,
    dbo.Kalkulation.Erstellungsdatum,
    dbo.Kalkulation.Bearbeitungsdatum,
    dbo.Kalkulationseingaben.Freitexthinweis,
    dbo.Kalkulation.Kunden_KontaktID_FK,
    dbo.Kalkulation.Kunden_Kurzbezeichnung,
    dbo.Kalkulation.MaterialID_FK, 
    dbo.Kalkulationseingaben.Version,
    dbo.Maschine.Maschinenbezeichnung + '  ' + dbo.Maschine.Firmenbezeichnung_FK AS Maschine, 
    
    CAST(CASE WHEN zm1.ZuschlagsID_FK = 1 THEN 'Farbe ' + '(' + zm1.Auswahl + ') ' ELSE '' END AS nvarchar)
    + CAST(CASE WHEN zm2.ZuschlagsID_FK = 2 THEN 'Lack ' ELSE '' END AS nvarchar)
    + CAST(CASE WHEN zm8.ZuschlagsID_FK = 8 THEN 'Rückseitendruck ' ELSE '' END AS nvarchar)
    + CAST(CASE WHEN zm4.ZuschlagsID_FK = 4 THEN 'Kaltprägung ' ELSE '' END AS nvarchar)
    + CAST(CASE WHEN zm9.ZuschlagsID_FK = 9 THEN 'Kleberabtötung' ELSE '' END AS nvarchar) AS Druckeinstellung

FROM 
dbo.Kalkulation 
    INNER JOIN
        dbo.Kalkulationseingaben ON dbo.Kalkulation.KalkulationsID_PK = dbo.Kalkulationseingaben.KalkulationsID_FK
    INNER JOIN
        dbo.Kalkulationsergebnis ON dbo.Kalkulation.KalkulationsID_PK = dbo.Kalkulationsergebnis.KalkulationsID_FK 
    INNER JOIN
        dbo.Maschine ON dbo.Kalkulation.MaschinenID_FK = dbo.Maschine.MaschinenID_PK
    LEFT OUTER JOIN
        dbo.Mitarbeiter AS Ersteller ON dbo.Kalkulation.Ersteller_MitarbeiterID_FK = Ersteller.MitarbeiterID_PK
    LEFT OUTER JOIN
        dbo.Mitarbeiter AS Bearbeiter ON dbo.Kalkulation.Bearbeiter_MitarbeiterID_FK = Bearbeiter.MitarbeiterID_PK
    LEFT OUTER JOIN
        dbo.Kalkulation_Zuschlag_Map AS zm1 ON dbo.Kalkulation.KalkulationsID_PK = zm1.KalkulationsID_FK AND zm1.ZuschlagsID_FK = 1 
    LEFT OUTER JOIN
        dbo.Kalkulation_Zuschlag_Map AS zm2 ON dbo.Kalkulation.KalkulationsID_PK = zm2.KalkulationsID_FK AND zm2.ZuschlagsID_FK = 2
    LEFT OUTER JOIN
        dbo.Kalkulation_Zuschlag_Map AS zm8 ON dbo.Kalkulation.KalkulationsID_PK = zm8.KalkulationsID_FK AND zm8.ZuschlagsID_FK = 8
    LEFT OUTER JOIN
        dbo.Kalkulation_Zuschlag_Map AS zm4 ON dbo.Kalkulation.KalkulationsID_PK = zm4.KalkulationsID_FK AND zm4.ZuschlagsID_FK = 4
    LEFT OUTER JOIN
        dbo.Kalkulation_Zuschlag_Map AS zm9 ON dbo.Kalkulation.KalkulationsID_PK = zm9.KalkulationsID_FK AND zm9.ZuschlagsID_FK = 9

Upvotes: 0

Views: 55

Answers (1)

Rhys Jones
Rhys Jones

Reputation: 5508

There's a technique that uses FOR XML to concatenate values, for you simple example it would be something like this;

SELECT b.ID_Basis,
       b.Name,
       ( SELECT case when m.FK = 1 then 'TEXT ' + m.note else m.note end + ','
         FROM Mapping m
         WHERE m.ID_Basis = b.ID_Basis
         ORDER BY m.note
         FOR XML PATH('') ) AS Notes
 FROM Basis AS b

Upvotes: 1

Related Questions