SQL / Knime - transpose table with "Group By"

Question

The name is terrible, but the best I could do. I have something like this:

Country  Tag    Weight
-----------------------
1        1      20
1        2      30
1        3      77
2        1      10
2        2      11
2        3      100

Or in a readable form:

Country  Tag    Weight
-----------------------
USA      Rock   20
USA      Pop    30
USA      Metal  77
Spain    Rock   10
Spain    Pop    11
Spain    Metal  100

Using SQL (to create the view) or Data Manipulation Tools in Knime, I need to manipulate the data in this form:

Country  Rock   Pop   Metal
----------------------------
USA      20     30    77
Spain    10     11    100

Essentially, tags (unique) become columns, and countries (unique) become an identifier for a row with weight values ​​attached to their country / tag.

, Knime, SQL- . Knime , (Country x Tag), , , . , CSV , , . klunky , . ?

Answer 1

pivot cross table. , , . Knime , , CSV- MS Excel .

Answer 2

ANSI SQL, :

  SELECT t.country,
         MAX(CASE WHEN t.tag = 'Rock' THEN t.weight END) AS Rock,
         MAX(CASE WHEN t.tag = 'Pop' THEN t.weight END) AS Pop,
         MAX(CASE WHEN t.tag = 'Metal' THEN t.weight END) AS Metal
    FROM YOUR_TABLE t
GROUP BY t.country

LesterDove - , .

PIVOT ( UNPIVOT) - ANSI, - SQL Server 2005+, Oracle 11g + - , . , ANSI/.

Answer 3

node KNIME . "" "Pivot", "" "", "" - "" . , Tobias