[SQL] Van rij naar kolom

[ThaStealth]

Hoi,

Ik heb nu een tabel genaamd Bestek, deze tabel heeft de volgende kolommen:
-BestekID (integer) (primary key)
-BestekType (integer, 0=vork,1=mes, etc) (primary key)
-BestekWaarde (float, 5 euro, 10euro etc)

Het is mogelijk dat er een set (mes,vork,lepel,etc) onstaat (zelfde BestekID).
Bijvoorbeeld dit:
BestekID - BestekType - BestekWaarde
0 - 1 - 5,25
0 - 0 - 10,50
0 - 2 - 15,20
1 - 2 - 1,01
...

Nu wil ik een uitvoer gelijk aan het volgende genereren:
BestekID - Mes - Vork - Lepel
0 - 5,25 - 10,50 - 15,20
1 - ......

Hoe kan ik dit het beste aanpakken, de enige oplossing die ik tot nu toe kan verzinnen voor dit probleem is het gebruik van een tussentabel, maar ik zou het liefste dit via een query willen doen. Is dit mogelijk?

[Thomasje]

Vind het niet de meest mooie oplossing maar je zou het kunnen doen met een subquery.

Pseudo:

Select (Select BestekWaarde from Bestek as mes where mes.bestekId = Bestek.BestekId ) From Bestek GroupBy Bestek.BestekId


Het is maar even snel bedacht.

Edit:
Primary key moet unique zijn

[ Voor 7% gewijzigd door Thomasje op 05-01-2010 10:30 ]

[ACM]

Je kan vast wel wat bereiken met joins of met group by:

SQL:

SELECT id,
  MAX(case type when 0 then waarde else 0 end) as vork,
 MAX(case type when 1 then waarde else 0 end) as mes, ...
FROM bestek
GROUP BY id

Of dus een join-variant:

SQL:

SELECT idlist.id,
  vork.waarde as vork,
  mes.waarde as mes, ...
FROM (SELECT DISTINCT id FROM bestek) as idlist
 LEFT JOIN bestek vork ON idlist.id = vork.id AND vork.type = 0
 LEFT JOIN bestek mes ON idlist.id = mes.id AND mes.type = 1
 ...

[Thomasje]

Kleine aanpassing omdat Primary unique moet zijn:

SQL:

CREATE TABLE `Bestek` (
  `Id` int(11) NOT NULL auto_increment,
  `BestekID` int(11) NOT NULL default '0',
  `BestekType` int(11) default NULL,
  `BestekWaarde` decimal(10,2) default NULL,
  PRIMARY KEY  (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `Bestek` VALUES ('1', '1', '1', '5.00');
INSERT INTO `Bestek` VALUES ('2', '1', '0', '11.00');
INSERT INTO `Bestek` VALUES ('3', '1', '2', '2.00');
INSERT INTO `Bestek` VALUES ('4', '2', '0', '12.00');

Query die werkt:

SQL:

SELECT 
    (SELECT vork.BestekWaarde FROM Bestek as vork Where vork.BestekID = Bestek.BestekID AND vork.BestekType = 0) as VorkWaarde,
    (SELECT mes.BestekWaarde FROM Bestek as mes Where mes.BestekID = Bestek.BestekID AND mes.BestekType = 1) as MesWaarde,
    (SELECT lepel.BestekWaarde FROM Bestek as lepel Where lepel.BestekID = Bestek.BestekID AND lepel.BestekType = 2) as LepelWaarde
FROM `Bestek`
GROUP BY
Bestek.BestekID

[P.O. Box]

als je geen MySQL gebruikt zou ik een googlen op pivot tables.

[ACM]

Thomasje schreef op dinsdag 05 januari 2010 @ 10:40:
Kleine aanpassing omdat Primary unique moet zijn:

Hij heeft toch een unieke primary key?

Ik zie niet in waarom je daar zo nodig die extra surrogate key aan toe moet voegen.

P.O. Box schreef op dinsdag 05 januari 2010 @ 10:41:
als je geen MySQL gebruikt zou ik een googlen op pivot tables.

De kans is sowieso groot dat voor in een applicatie dat het handiger is om gewoon de originele resultset pas in de applicatie-laag te transponeren.

[Thomasje]

ACM schreef op dinsdag 05 januari 2010 @ 10:44:
[...]

Hij heeft toch een unieke primary key?

Kijk eens naar zijn schema, en de eerste resultaten die hij neer zet. Zijn 3 maal 0 op een rij. Is niet echt unique toch?

[ACM]

Thomasje schreef op dinsdag 05 januari 2010 @ 10:46:
Kijk eens naar zijn schema, en de eerste resultaten die hij neer zet. Zijn 3 maal 0 op een rij. Is niet echt unique toch?

Als je niet goed leest en over het hoofd ziet dat zijn PK uit 2 kolommen bestaat heb je gelijk ja

[Thomasje]

ACM schreef op dinsdag 05 januari 2010 @ 10:50:
[...]

Als je niet goed leest en over het hoofd ziet dat zijn PK uit 2 kolommen bestaat heb je gelijk ja

Je hebt gelijk!

[ThaStealth]

ACM schreef op dinsdag 05 januari 2010 @ 10:50:
[...]

Als je niet goed leest en over het hoofd ziet dat zijn PK uit 2 kolommen bestaat heb je gelijk ja

Idd had het misschien wat duidelijker moeten neerzetten. Ik vind het super dat er al zo snel zoveel antwoorden zijn gekomen . Had echt verwacht dat het een stuk moeilijker zou zijn dan dit . Ik ben gelukkig geholpen nu en kan verder met de borden en pannen

[Thomasje]

Welke oplossing heb je nu gebruikt?

[ThaStealth]

Thomasje schreef op dinsdag 05 januari 2010 @ 11:08:
Welke oplossing heb je nu gebruikt?

Ik heb de oplossing van ACM gebruikt (de 2e)

SQL:

SELECT idlist.id,
  vork.waarde as vork,
  mes.waarde as mes, ...
FROM (SELECT DISTINCT id FROM bestek) as idlist
 LEFT JOIN bestek vork ON idlist.id = vork.id AND vork.type = 0
 LEFT JOIN bestek mes ON idlist.id = mes.id AND mes.type = 1
 ...

[djluc]

Als je structuur niet geheel vast staat zou ik eerder iets met een koppeltabel doen:

SQL:

SELECT bestek.id, types.name, bestek_items.value
FROM bestek 
INNER JOIN bestek_items ON bestek.id=bestek_items.bestek_id
INNER JOIN types ON types.id = bestek_items.type_id
ORDER BY bestek.id

SQL:

-- --------------------------------------------------------

--
-- Table structure for table `bestek`
--

CREATE TABLE IF NOT EXISTS `bestek` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `bestek`
--

INSERT INTO `bestek` (`id`) VALUES
(1),
(2);

-- --------------------------------------------------------

--
-- Table structure for table `bestek_items`
--

CREATE TABLE IF NOT EXISTS `bestek_items` (
  `id` int(11) NOT NULL auto_increment,
  `bestek_id` int(11) NOT NULL,
  `value` varchar(255) NOT NULL,
  `type_id` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `bestek_items`
--

INSERT INTO `bestek_items` (`id`, `bestek_id`, `value`, `type_id`) VALUES
(1, 1, '12', 1),
(2, 1, '22', 2);

-- --------------------------------------------------------

--
-- Table structure for table `types`
--

CREATE TABLE IF NOT EXISTS `types` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `types`
--

INSERT INTO `types` (`id`, `name`) VALUES
(1, 'Mes'),
(2, 'Vork');

Je hebt dan gewoon 2 losse entities: type en bestek welke je koppelt. Of je de waarde in de type of koppeltabel zit hangt af van je business needs. In de koppeltabel kan je de waarde laten wisselen.

In je business layer kan je de tabel laten draaien, in je database zou ik dat niet doen. Ik zou daar gewoon structureren op basis van standaard record outputs.

[ Voor 4% gewijzigd door djluc op 05-01-2010 14:06 ]