[MSSQL 2005] SP > meerdere rijen in een colom samenvoegen

[skabouter]

Beste Tweakers,

Ik heb de volgende Stored Procedure

SQL:

CREATE PROCEDURE [dbo].[get_projecten_kort]
    @id int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT P.start, P.eind, K.naam AS klant, F.naam AS functie FROM project AS P
        INNER JOIN projectfunctie AS PF
            ON (P.id = PF.project)
        INNER JOIN functie AS F
            ON (F.id = PF.functie)
        INNER JOIN klant AS K
            ON (P.klant = K.id)
        WHERE
            P.medewerker = @id

END

Welke de volgende output geeft

SQL:

execute get_projecten_kort 2

2006-07-01 00:00:00 NULL klant1 Software ontwikkelaar
2006-07-01 00:00:00 NULL klant1 Vervangend projectleider

Zoals jullie zien is dit 2x hetzelfde project echter met verschillende functies, het is echter de bedoeling dat deze functies worden samengevoegd, zodat de output van deze SP word;

2006-07-01 00:00:00 NULL klant1 Software ontwikkelaar,Vervangend projectleider

Ik heb een aantal boeken & websites geraadpleegd, maar voor zover ik kan vinden is dit niet mogelijk. Iemand die weet of dit wel mogelijk is en zo ja hoe?

[RobIII]

Heb je al in onze eigen FAQ's gekeken?
Programming FAQ - SQL >> Group By

Voor het aan elkaar plakken van de functies zul je toch rare stunts moeten gaan uithalen die je waarschijnlijk niet wil; wat ik graag zou zien is wat je zelf al hebt geprobeerd en wat er niet aan werkt(e)

En anders zul je aan de cursors moeten (check de Books Online).

[ Voor 108% gewijzigd door RobIII op 01-11-2006 10:56 ]

[Verwijderd]

De door jou gewenste manier van weergeven is niet schaalbaar.

Bij 1-op-veel relaties kun je je de resultaten aan de 'veel' kant beter niet met komma's scheiden. Als je pech hebt en het zijn 100 entries, krijg je een moeilijk leesbare lap tekst op je scherm. Ook als je misschien meerdere velden van de 'veel' kant weer wilt geven, zal dit moeilijk worden.

Wat gebruikelijk is, is om het als volgt weer te geven:

datum tijd klant1 bedrijsnaam contactpersoon adresgegevens etc.
Software ontwikkelaar voornaam achternaam kamernummer etc.
Vervangend projectleider voornaam achternaam kamernummer etc.
datum tijd klant2 bedrijsnaam contactpersoon adresgegevens etc.

Deze manier van grouperen wordt vaak met het GROUP BY statement verzorgt, waarbij je de sortering breakdown met GROUP BY velden regelt.

Het loont de moeite om zeer uitvoerig na te denken over de tabelstructuur en de relaties tussen de velden (one-to-one, one-to-many, many-to-many).
Op nummer twee in de top 10 zou een goede naamsconventie voor je velden ,formulieren en code en zo meer moeten staan.

[skabouter]

Ja ik had inderdaad in de FAQ's gekeken, group by werkt in dit geval helaas niet aangezien de functies verschillend zijn.

Ik wil dus een group by waarbij de verschillende velden worden gegroepeerd (zie ook TS)
Ik heb (op een paar klungelige pogingen na) nog geen concrete pogingen gedaan om dit werkend te krijgen, dat wil zeggen ik heb ook nog geen (mogelijke) oplossing gevonden die überhaupt het proberen waard is

Ik heb de relevante hoofdstukken in de boeken 'Het SQL leerboek' & 'Professional SQL server 2000 programming' doorgenomen (heb helaas geen boek over 2005) maar ook daar kan ik helaas niets uithalen.

De link die je geeft is precies datgene dat ik wil! Ik ga daar nu even mee aan de slag.

[skabouter]

Verwijderd schreef op woensdag 01 november 2006 @ 11:01:
De door jou gewenste manier van weergeven is niet schaalbaar.

Bij 1-op-veel relaties kun je je de resultaten aan de 'veel' kant beter niet met komma's scheiden. Als je pech hebt en het zijn 100 entries, krijg je een moeilijk leesbare lap tekst op je scherm. Ook als je misschien meerdere velden van de 'veel' kant weer wilt geven, zal dit moeilijk worden.

Wat gebruikelijk is, is om het als volgt weer te geven:

datum tijd klant1 bedrijsnaam contactpersoon adresgegevens etc.
Software ontwikkelaar voornaam achternaam kamernummer etc.
Vervangend projectleider voornaam achternaam kamernummer etc.
datum tijd klant2 bedrijsnaam contactpersoon adresgegevens etc.

Deze manier van grouperen wordt vaak met het GROUP BY statement verzorgt, waarbij je de sortering breakdown met GROUP BY velden regelt.

Het loont de moeite om zeer uitvoerig na te denken over de tabelstructuur en de relaties tussen de velden (one-to-one, one-to-many, many-to-many).
Op nummer twee in de top 10 zou een goede naamsconventie voor je velden ,formulieren en code en zo meer moeten staan.

Dus wat je eigelijk wilt zeggen is dat ik beter twee apparte SP's kan gebruiken?

[skabouter]

Ok het is gelukt!

Het bleek uiteindelijk mee te vallen, voor de compleetheid van dit topic, de uiteindelijke query is nu:

SQL:

USE [FES]
CREATE PROCEDURE [dbo].[get_projecten_kort]
    @id int
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @functies VARCHAR(1024) 
     
    SELECT 
        @functies = COALESCE(@functies + ',', '') + F.naam 
    FROM 
        functie AS F
    INNER JOIN projectfunctie AS PF
        ON (PF.functie = F.id)
    INNER JOIN project AS P
        ON (P.id = PF.project)
    WHERE
        P.medewerker = @id;
     
    SELECT P.start, P.eind, K.naam AS klant, functie = @functies FROM project AS P
        INNER JOIN klant AS K
            ON (P.klant = K.id)
        WHERE
            P.medewerker = @id;
END

Op / aanmerkingen zijn uiteraard altijd welkom!

[P_de_B]

Je kunt wel een User Defiend Functie gebruiken die de lijst met taken ophaalt.

SQL:

CREATE FUNCTION fnHaalFunctiesOpEnRetourneerEenLelijkeStringMetFuncties

(@MedewerkerId INT)

RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @Functie VARCHAR(1000)
    SELECT @Functie = COALESCE(@Functie + ',','') + F.Functie
    FROM [...]
    WHERE p.medewerker = MedewerkerId
RETURN (@Functie)
END

Deze kun je dan aanroepen in de oorspronkelijke proc:

SQL:

 SELECT P.start, P.eind, K.naam AS klant,fnHaalFunctiesOpEnRetourneerEenLelijkeStringMetFuncties(p.medewerkerId) AS functie
        FROM project AS P
        INNER JOIN projectfunctie AS PF
            ON (P.id = PF.project) [..]

/edit: ah, je was zelf ook net op de coalesce truc gekomen

[ Voor 4% gewijzigd door P_de_B op 01-11-2006 11:22 ]

[__fred__]

skabouter schreef op woensdag 01 november 2006 @ 11:18:

Op / aanmerkingen zijn uiteraard altijd welkom!

Je maximale lengte van een varchar is 8000 karakters in SQL server 2000, van een nvarchar (unicode) 4000. Je horizontale projectie kent dus wel hele harde beperkingen op 2000. In 2005 is er gelukking (n)varchar(max)

[whoami]

Indien je het echt zo wil oplossen, zou ik ook voor een User Defined Functie gaan, zoals P_de_B al zegt.
Echter; ik vind nog altijd: SQL is voor het ophalen / manipuleren van gegevens, en niet voor de presentatie ervan. Ik zou gewoon de gegevens ophalen, en m'n 'client-code' verantwoordelijk laten zijn over hoe die gegevens uiteindelijk getoond worden.

[skabouter]

__fred__ schreef op woensdag 01 november 2006 @ 11:39:
[...]


Je maximale lengte van een varchar is 8000 karakters in SQL server 2000, van een nvarchar (unicode) 4000. Je horizontale projectie kent dus wel hele harde beperkingen op 2000. In 2005 is er gelukking (n)varchar(max)

Thnx, had dit van het voorbeeld overgenomen, nvarchar is idd beter om hier te gebruiken.

En verder, ik ben ook van mening dat SQL niet bedoeld is om data op te maken en dat deze manier van gebruiken wel erg smerig is. Het is echter wel een oplossing die (naar mijn mening) snel en makkelijk te beheren is.

Als ik dit probleem met een tweede query zou oplossen en ik zou bijvoorbeeld 1000 projecten willen selecteren dan zou ik dus 1001 query's moeten uitvoeren (1 voor het selecteren van het project en 1000 voor het selecteren van de functies per project) of ik krijg een enorme overhead omdat per row ook alle projectdata wordt verzonden

[P_de_B]

skabouter schreef op woensdag 01 november 2006 @ 12:10:

of ik krijg een enorme overhead omdat per row ook alle projectdata wordt verzonden

Toch is dat wel de beste manier. Zo groot is die overhead niet hoor.

[whoami]

skabouter schreef op woensdag 01 november 2006 @ 12:10:
[...]

Als ik dit probleem met een tweede query zou oplossen en ik zou bijvoorbeeld 1000 projecten willen selecteren dan zou ik dus 1001 query's moeten uitvoeren (1 voor het selecteren van het project en 1000 voor het selecteren van de functies per project) of ik krijg een enorme overhead omdat per row ook alle projectdata wordt verzonden

Dat kan je met één query oplossen:

code:

SELECT project.Naam, functie.naam
FROM project
INNER JOIN functie ON project.project_id = functie.project_id
ORDER BY project.Naam, functie.naam

In je client-code kan je dan gewoon de recordset die je dan terugkrijgt, gaan verwerken. Aangezien er gesorteerd is op projectnaam, kan je op deze manier makkelijk nagaan wanneer je aan de functies van een ander project begint.