Transponeren van kolom naar rij (MySQL): verschil tussen versies
Naar navigatie springen
Naar zoeken springen
| (7 tussenliggende versies door dezelfde gebruiker niet weergegeven) | |||
| Regel 1: | Regel 1: | ||
== Het probleem == | == Het probleem == | ||
| − | Ik heb een lege tabel met zo'n | + | Ik heb een lege tabel met zo'n 60 velden: |
<pre> | <pre> | ||
| Regel 25: | Regel 25: | ||
== Mogelijke oplossingen == | == Mogelijke oplossingen == | ||
| − | * '''[[Grouping (MySQL) | Grouping]]''' ligt voor de hand als oplossing, maar daabij wordt één variable gegenereerd met daarin meerdere | + | * '''[[Grouping (MySQL) | Grouping]]''' ligt voor de hand als oplossing, maar daabij wordt één variable gegenereerd met daarin meerdere veldnamen. Alles wordt dus in één veld gestopt |
* '''[[Grouping (MySQL) | Grouping]] + [[Explode (MySQL) | Explode]]:''' Nu worden de cellen gesplitst, maar belanden ze weer op verschillende rijen | * '''[[Grouping (MySQL) | Grouping]] + [[Explode (MySQL) | Explode]]:''' Nu worden de cellen gesplitst, maar belanden ze weer op verschillende rijen | ||
| − | * '''Pivoting''' of '''unpivoting''' zoals in een ''pivot table'' is misschien een oplossing, maar ik wil eigenlijk niets doen met aggregeren van info. | + | * '''Pivoting''' of '''unpivoting''' zoals in een ''pivot table'' is misschien een oplossing, maar ik wil eigenlijk niets doen met aggregeren van info |
| − | * '''Prepared insert | + | * '''Union select:''' [http://stackoverflow.com/questions/16359345/transposing-dynamic-columns-to-rows Deze] vernuftige oplossing gebruikt UNION SELECT-statements om data samen te voegen maarrrr daarmee belandt data nog steeds in verschillende rijen |
| + | * '''Prepared insert-statement:''' Dit gaat zeker lukken: Ik heb de velden immers al in een string. Hoef ik nog maar om te fietsen naar een ''prepared statement'' + uitvoeren. | ||
| + | |||
| + | == Oplossing: Prepared Insert-statement == | ||
| + | |||
| + | Algemene formulering: | ||
| + | |||
| + | <pre> | ||
| + | insert into tbl_tabel values (waarde1, waarde2, ...); | ||
| + | </pre> | ||
| + | |||
| + | Oplossing: | ||
| + | |||
| + | <pre> | ||
| + | CREATE DEFINER=`root`@`localhost` PROCEDURE `VeldnamenEN`() | ||
| + | BEGIN | ||
| + | -- | ||
| + | -- Kopiëer de kolomnamen naar de tweede regel van de tabel | ||
| + | -- | ||
| + | set @ddl=concat | ||
| + | ( | ||
| + | "insert into amazon_sjabloon values('" | ||
| + | , | ||
| + | ( | ||
| + | select group_concat(column_name separator "', '") | ||
| + | from information_schema.columns | ||
| + | where table_name="sjabloon" and TABLE_SCHEMA="webwinkels" | ||
| + | ) | ||
| + | , | ||
| + | "');" | ||
| + | ); | ||
| + | |||
| + | -- select @ddl; | ||
| + | prepare stmt from @ddl; | ||
| + | execute stmt; | ||
| + | </pre> | ||
== Zie ook == | == Zie ook == | ||
Huidige versie van 14 jan 2016 om 15:51
Het probleem
Ik heb een lege tabel met zo'n 60 velden:
+--------+--------+--------+--------+ | Veld01 | Veld02 | ... | Veld20 | +--------+--------+--------+--------+
Ik wil de namen van die velden in het eerste record zetten, zodat je zoiets krijgt als
+--------+--------+--------+--------+ | Veld01 | Veld02 | ... | Veld20 | +--------+--------+--------+--------+ | Veld01 | Veld02 | ... | Veld20 | +--------+--------+--------+--------+
Achterhalen van de veldnamen is niet moeilijk. Bv.: create table tmp select column_name from information_schema.columns where table_name="tabelnaam";.
Maar hoe krijg ik die namen nu op één rij?
Mogelijke oplossingen
- Grouping ligt voor de hand als oplossing, maar daabij wordt één variable gegenereerd met daarin meerdere veldnamen. Alles wordt dus in één veld gestopt
- Grouping + Explode: Nu worden de cellen gesplitst, maar belanden ze weer op verschillende rijen
- Pivoting of unpivoting zoals in een pivot table is misschien een oplossing, maar ik wil eigenlijk niets doen met aggregeren van info
- Union select: Deze vernuftige oplossing gebruikt UNION SELECT-statements om data samen te voegen maarrrr daarmee belandt data nog steeds in verschillende rijen
- Prepared insert-statement: Dit gaat zeker lukken: Ik heb de velden immers al in een string. Hoef ik nog maar om te fietsen naar een prepared statement + uitvoeren.
Oplossing: Prepared Insert-statement
Algemene formulering:
insert into tbl_tabel values (waarde1, waarde2, ...);
Oplossing:
CREATE DEFINER=`root`@`localhost` PROCEDURE `VeldnamenEN`()
BEGIN
--
-- Kopiëer de kolomnamen naar de tweede regel van de tabel
--
set @ddl=concat
(
"insert into amazon_sjabloon values('"
,
(
select group_concat(column_name separator "', '")
from information_schema.columns
where table_name="sjabloon" and TABLE_SCHEMA="webwinkels"
)
,
"');"
);
-- select @ddl;
prepare stmt from @ddl;
execute stmt;