[SQL] 5x Count(*) als "mooie string" parsen?

[F-Tim]

Langere tijd vraag ik me al af of het mogelijk is om SQL een beetje intelligenter te maken dan het is dmv bepaalde parse-logica in een SQL statement uit te voeren. Misschien dat nu bij sommige mensen de haren rechtop gaan staan omdat ze vinden dat je SQL zo dom mogelijk moet houden en alle parse-acties en business rules in de BL en DL moet afchecken, maar ik vind ook dat het filteren van data wel in SQL mag, en als daar deels intelligentie bij komt kijken.... vind ik dat niet erg. Enfin, om dit niet naar een discussie te laten verlopen in de zin van of het wel of niet mooi/gepast o.i.d. is, sla ik dit gewoon over en ga weer verder met de hoofdzaak.

Ik heb een header tabel, ongeveer als volgt:

ID	Naam
1	Hans
2	Piet

En een analyse tabel die een 1:n relatie heeft met de header tabel:

ID	HeaderID	Analyse
1	1	A
2	1	B1
3	1	B2
4	1	B4
5	1	C
6	1	D
7	1	E
8	2	B1
9	2	B3
10	2	C

Wat ik nu wil is het volgt; voor elke id in de header tabel voor ik wat subqueries uit, welke de count per status retourneren. "1A/3B/1C/1D/1E" als AnalysesForID en "7" als TotalCount voor headerID 1 en "2B/1C" en "1" voor headerID 2.

Nu ben ik aant stoeien geweest met SQL , en nu kom ik in feite al tot dit punt. Hiervoor gebruik ik de volgende code:

SQL:

SELECT
(CAST((SELECT COUNT(*) FROM tAnalyses WHERE (headerID = tHeader.ID) AND (Analyse = 'A')) AS NVARCHAR) + 'A/' + 
 CAST((SELECT COUNT(*) FROM tAnalyses WHERE (headerID = tHeader.ID) AND (Analyse LIKE 'B_')) AS NVARCHAR) + 'B/' +
 CAST((SELECT COUNT(*) FROM tAnalyses WHERE (headerID = tHeader.ID) AND (Analyse = 'C')) AS NVARCHAR) + 'C/' +
 CAST((SELECT COUNT(*) FROM tAnalyses WHERE (headerID = tHeader.ID) AND (Analyse = 'D')) AS NVARCHAR) + 'D/' +
 CAST((SELECT COUNT(*) FROM tAnalyses WHERE (headerID = tHeader.ID) AND (Analyse = 'E')) AS NVARCHAR) + 'E') AS AnalysesForID,
(SELECT COUNT(*) FROM tAnalyses WHERE (headerID = tHeader.ID)) AS TotalCount
FROM tHeader

Probleem is echter als van een bepaalde analyse 0 counts zijn, dat ik dan ipv "0A/2B/1C/0D/0E" en "3" terugkrijg voor headerID 2, maar dat ik dan wil dat hij me de 0 waarden niet laat zien, en ik dan dus alleen de string "2B/1C" terug wil krijgen zoals boven gezegd.

ik kan echter geen variabelen aanmaken met DECLARE, omdat je die buiten de select moet SET-ten, en aangezien ik voor élke ID in tHeader deze data terug wil krijgen, moet dat ook binnen de SELECT blijven staan, en SET binnenin een SELECT mag niet volgens MS-SQL.

Returnvariabelen na een subquery kunnen ook niet want die kunnen niet uitgevoerd worden, hoewel de SQL wél parsebaar is , omdat hij dan de column niet kan vinden.
Mini voorbeeldje:

SQL:

(SELECT COUNT(*) FROM tAnalyses WHERE (headerID = tHeader.ID) AND (Analyse = 'A')) AS aCount,
(CAST(aCount AS NVARCHAR) + 'A') AS aCountStr

De SQL parsed wel zonder problemen, alleen bij executen ervan krijg ik dan de fout "Invalid column name 'aCount'". Eenzelfde melding die ik zou krijgen als ik een IF statement zou gebruiken.

In pseudocode zou ik iig ongeveer de volgende stappen aanhouden:
count(aStatussen)
count(bStatussen)
count(cStatussen)
count(dStatussen)
count(eStatussen)
als aStatussen > 0 dan strStatus = aStatussen + 'A'
als strStatus <> '' dan strStatus += '/'
als bStatussen > 0 dan strStatus += bStatussen + 'B'
als strStatus <> '' dan strStatus += '/'
als cStatussen > 0 dan strStatus += cStatussen + 'C'
als strStatus <> '' dan strStatus += '/'
als dStatussen > 0 dan strStatus += dStatussen + 'D'
als strStatus <> '' dan strStatus += '/'
als eStatussen > 0 dan strStatus += eStatussen + 'E'
als Substring(strStatus, Length(strStatus) -1) == '/' dan strStatus = Substring(strStatus, 0, Length(strStatus) -1)

Ik begin langzaam te twijfelen óf dit wel in SQL mogelijk is, in C# is het achteraf gezien op de DataTable wel mogelijk, maar ik zou het mooi vinden als ik vanuit SQL gewoon in 1x een correcte returnwaarde krijg.

Mijn vraag aan de rest van jullie Tweakers is dan nu ook of hetgene wat ik wil wel mogelijk is met SQL? En of iemand me dan ook kan helpen hiermee? Want zelf zit ik dus gigantisch vast hier.

Ja, het is een lange topicstart, maar ik hoop wel zo alles heel duidelijk te hebben weergegeven

[SPee]

Kun je niet per getal + id het aantal terugvragen met een GROUP BY

Je krijgt dan voor ID1 dus 5 rijen terug met ID, Waarde, Aantal.
Eventueel met een UNION en ORDER BY kun je het in een bepaalde volgorde terugkrijgen.

Je hebt dan niet alle waardes in 1 rij, maar wel wat je wilt.

[edit]
Ik heb een header tabel, ongeveer als volgt:
ID Naam
1 Hans
2 Piet

En een analyse tabel die een 1:n relatie heeft met de header tabel:
ID HeaderID Analyse
1 1 A
2 1 B1
3 1 B2

Voorbeeld:

SQL:

 SELECT headerid, substring(analyse, 1) as analysethis, count(a.id)
 from analyse
 group by headerid, substring(analyse, 1)
 union
 select headerid, '' as analysethis, count(a.id)
 from analyse
 group by headerid
 order by headerid, analysethis

[ Voor 45% gewijzigd door SPee op 03-04-2008 15:22 . Reden: SQL query ]

[Voutloos]

In die query is wellicht het probleem dat bepaalde termen null ipv 0 zijn? Dat is natuurlijk eenvoudig te fixen met COALESCE(COUNT(*), 0) of iets dergelijks.

Wellicht is het mooier om een tabel te hebben met alle analyze waardes waartegen je kan left joinen en aparte kolommen voor analyse letter en nummer zodat je uiteindelijke query flink korter, overzichtelijker en netter wordt.

offtopic:
Het echte probleem is natuurlijk dat je dit allemaal op dit niveau aan het klussen bent. Voor nu moeten wij die discussie negeren, soit, maar verwacht niet dat we alle mogelijke problemen die nog komen (en die zullen gaan komen) cq. je wazige model begrijpen als je over een paar jaar een topic start.

[ Voor 3% gewijzigd door Voutloos op 03-04-2008 15:34 ]

[EfBe]

SQL:

SELECT HeaderID, LEFT(Analyse, 1) AS AnalyseChar, COUNT(*) AS NumberOfAnalyseChars
FROM tAnalyses
WHERE HeaderID IN (SELECT HeaderID FROM tHeader)
GROUP BY HeaderID, LEFT(Analyse, 1) 

Dit levert een lijstje op van alle headerid's met de analyse char (A, B etc.) en het aantal van die analyse chars voor de headerid. dus 2, B, 2

(niet getest)
Je kunt dit al wat aan elkaar plakken met een nieuwe projectie hieromheen (dus deze query is de FROM clause van die query)

Omdat je rijen hebt die je aan elkaar wilt plakken moet je dit OF op de client doen, OF je moet een user defined aggregate function maken in C# en die in de database runnen (SQLSERVER 2005 en hoger), die je dan gebruikt in een query om deze query heen met opnieuw een groupby.

Ben het eens met voutloos: omdat jij niet in wilt zien dat wat je wilt eigenlijk bezopen is betekent het nog niet dat wat je wilt de juiste keuze is. ALs dit hobby is, who cares, maar als dit werk is, dan moet je je echt afvragen wie jou betaalt om wat te doen.

[ Voor 14% gewijzigd door EfBe op 03-04-2008 15:42 ]

[pedorus]

Vreemd genoeg levert Google als 1e hit een oplossing voor bijna hetzelfde "probleem". Mocht er ooit nog iemand anders zo'n oplossing moeten onderhouden, dan wens ik hem/haar veel succes... Erg vreemd om de opmaak door de database-engine te laten doen.

De kolom Analyse lijkt hier niet eens in 1NF te staan.

[EfBe]

pedorus schreef op donderdag 03 april 2008 @ 16:14:
Vreemd genoeg levert Google als 1e hit een oplossing voor bijna hetzelfde "probleem". Mocht er ooit nog iemand anders zo'n oplossing moeten onderhouden, dan wens ik hem/haar veel succes... Erg vreemd om de opmaak door de database-engine te laten doen.

Heh, in die thread maakt CELKO die gast met de grond gelijk, maar wel met argumenten die, wanneer je die tegen hem gebruikt wanneer hij weer eens stored procedures uber alles loopt aan te prijzen, ineens onzin blijken te zijn.

[F-Tim]

Goed, dan zal ik die functie niet in de SQL zetten als er wordt gezegd dat dit uiteindelijk problemen zal opleveren. Vooral als er zonder verder commentaar wordt aangegeven dat het eigenlijk écht not-done is. Al vraag ik me dan wel af waarom persé niet. Maar dat terzijde.

Hetgene wat ik dan nu doe is... 5 verschillende variabelen terugsturen naar de datalaag in de DataSet, waarna ik ze in C# in de BL parse tot wat ik wil hebben. (Iets wat overigens in C# met 4 regels code gepiept was)