[sql server] variabel aantal kolommen ophalen

[sig69]

Ik ben voor mijn stage bezig om het productmodel in een SQL Server database weer te geven. In princiepe hebben ze een aantal standaardproducten, die uitbreidbaar zijn met een aantal modules. Op het moment heb ik deze drie tabellen:

Product id naam 1 product1 2 product2 3 product3

Module id naam 1 module1 2 module2 3 module3 4 module4

xProductModule productID moduleID 1 1 1 2 1 4 2 2 2 4 3 3

Nu is het de bedoeling dat ik in een tabel de producten als rijen weergeef en de mogelijke modules als kolommen, zo dus:
Edit: onderstaande moet dus uit een query komen voor gebruik op het intranet (sorry voor de eerste onduidelijke uitleg...)

ProductModuleCombinaties
Product	Module1	Module2	Module3	Module4
Product1	1	1	0	1
Product2	0	1	0	1
Product3	0	0	1	0

Waarbij een 1 betekent dat de module bij het product past, en een 0 niet. Dit lukt me op zich wel, maar het moet zo worden dat de modulekolommen variabel zijn (als er in de toekomst een module bij komt moet het gewoon werken). Ik heb alleen geen idee hoe ik dit aan zou moeten pakken. Kan iemand me een duwtje in de goede richting geven? Googlen en de search leverde weinig bruikbaars op.

[ Voor 4% gewijzigd door sig69 op 06-10-2003 15:02 . Reden: spelfout... ]

[Jaspertje]

Waarom zou je dat anders willen

Het eerste model ik 10000 keer beter dan jou model..
waarom: Stel er komt een module bij.. dat betekend jij de hele database moet aanpassen. Stel er komty een product bij (die is misschien wel simpel) dan moet er een extra recod in de db gezet worden (okee die kan gewoon)

Maar als jij/(je baas) het wel wilt zou je moeten zoeken naar alter table add column..
Dan kan je 'oneindig' kolommen toevoegen.. en blijven de oude ook werken

* Jaspertje blijft van mening dat dit geen oplossing is//.

Edit: of begrijp ik het helemaal verkeerd?????

[ Voor 7% gewijzigd door Jaspertje op 06-10-2003 14:58 ]

[sig69]

Hmmm, beetje onduidelijk van mij: de ProductModuleCombinaties tabel moet het resultaat van een Query zijn voor gebruik op het intranet.

/me sig moet voortaan beter uitleggen..

[Verwijderd]

wat voegt die laatste tabel precies voor je toe?
alleen een andere presentatie van informatie die je al in de eerste drie tabellen hebt staan toch?

edit:
te laat

[ Voor 9% gewijzigd door Verwijderd op 06-10-2003 15:04 ]

[sig69]

Klopt, maar afhankelijk van deze tabel moeten op het intranet product/modulecombinaties aangevinkt kunnen worden (een 0 staat dus voor niet beschikbaar -> checkbox disabled)

[Annie]

Zoek eens op "pivot table" zo wordt dit in excel of access genoemd en er zijn sp's te vinden die dit gedrag na-apen (overigens wel met ranzige code).
En mssql heeft "cubes" (OLAP), maar ik weet niet of je daar aan wil beginnen.

[ Voor 12% gewijzigd door Annie op 06-10-2003 19:52 ]

[sig69]

Zoeken op "pivot table" levert inderdaad veel interessante resultaten op. Ik zal me er eens in verdiepen. Bedankt!

[sig69]

ok ik heb nu dit:

code:

SELECT ProductID, 
    SUM(CASE ModuleID WHEN 1 THEN StandaardModule ELSE 0 END) AS Mod1,
    SUM(CASE ModuleID WHEN 2 THEN StandaardModule ELSE 0 END) AS Mod2,
    SUM(CASE ModuleID WHEN 3 THEN StandaardModule ELSE 0 END) AS Mod3,
    SUM(CASE ModuleID WHEN 4 THEN StandaardModule ELSE 0 END) AS Mod4,
    SUM(CASE ModuleID WHEN 5 THEN StandaardModule ELSE 0 END) AS Mod5,
    SUM(CASE ModuleID WHEN 6 THEN StandaardModule ELSE 0 END) AS Mod6,
    SUM(CASE ModuleID WHEN 7 THEN StandaardModule ELSE 0 END) AS Mod7,
    SUM(CASE ModuleID WHEN 8 THEN StandaardModule ELSE 0 END) AS Mod8,
    SUM(CASE ModuleID WHEN 9 THEN StandaardModule ELSE 0 END) AS Mod9,
    SUM(CASE ModuleID WHEN 10 THEN StandaardModule ELSE 0 END) AS Mod10,
    SUM(CASE ModuleID WHEN 11 THEN StandaardModule ELSE 0 END) AS Mod11
FROM
    xProductModule
GROUP BY
    ProductID
GO

Wat het volgende resultaat oplevert:

code:

ProdID  Mod1    Mod2    Mod3    Mod4    Mod5    Mod6    Mod7    Mod8    Mod9    Mod10   Mod11
1   1   1   0   0   0   0   1   0   0   0   0
2   0   0   1   0   1   0   0   1   0   0   0
3   1   1   1   0   0   0   0   0   0   1   0

Enige probleem is nu dus dat als ze hier een nieuwe module ontwikkelen de query niet meer klopt. Ik zat al te denken om de SUM(CASE... in een FOR-NEXT loop te doen, maar dat lijkt me niet echt een mooie oplossing...

[Annie]

sig69 schreef op 07 October 2003 @ 11:03:
Ik zat al te denken om de SUM(CASE... in een FOR-NEXT loop te doen, maar dat lijkt me niet echt een mooie oplossing...

Inderdaad niet altijd de schoonheidsprijs.
Overigens zijn er ook custom sp's die dat voor je doen.
* Annie zoekt even in het archief.... hebbes: sp_query_pivot

[ Voor 5% gewijzigd door Annie op 07-10-2003 12:59 ]

[sig69]

Thnx, die werkt perfect:


Nu probeer ik alleen een manier te verzinnen om een soort dynamisch datagrid te bouwen. Oftewel: het aantal kolommen dat opgehaald wordt moet niet uitmaken. Nu zijn het er toevallig 11, maar als het er meer of minder zijn moet het datagrid eigenlijk automatisch meeveranderen.
Nu gebruik ik een TemplateColumn om de waarde uit de database te evalueren naar checkbox aan/uit, maar hoe kan ik zoiets onafhankelijk maken van het aantal kolommen dat wordt opgehaald?

[ Voor 136% gewijzigd door sig69 op 17-10-2003 09:32 ]

[sig69]

*kick*

[Verwijderd]

bekijk dit component eens: http://metabuilders.com/Tools/BoundBooleanColumn.aspx het is een afgeleide van BoundColumn maar dan om booleaanse waarden te tonen, daarmee kan je op deze manier één of meerdere kolommen toevoegen:

Visual Basic .NET:

      
  Dim Kol As New BoundBooleanColumn
           
   Kol.DataField = "blabla"
   Kol.HeaderText = "blablabla"
   Kol.FooterText = "blablablablabla"
   Kol.SelectionMode = BooleanSelectionMode.CheckBox
   Kol.AllowNull = False
   Kol.NullDefault = False
        
    DataGrid1.Columns.Add(Kol)

[ Voor 18% gewijzigd door Verwijderd op 17-10-2003 15:56 ]