Convert column oriented table to row oriented one

Question

I have a table structure as below on Greenplum database: enter image description here

Wish to change it to the following structure so as to support pie charts on Tableau.

enter image description here

Could some one help me out ? Thanks!

This is called a pivot, or crosstab. No idea how to do it in Greenplum; in PostgreSQL you'd use the crosstab function in the tablefunc extension. — Craig Ringer, Feb 19 '14 at 14:46
You can also use **unpivot**, but not giving you the exact result you want. — Krishnraj Rana, Feb 19 '14 at 14:48
@KrishnrajRana . there seems to be no unpivot function in postgres . Alternative was unnest but I cant use unnest as my version of postgres is 8.2 . — user2569524, Feb 19 '14 at 15:12

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

Export the table to a CSV file
Install the Tableau Excel add-in
Open CSV file in Excel and use the add-in to reshape the data

edited May 23 '17 at 11:57

Community

1
1

answered Feb 19 '14 at 16:03

Talvalin

7,789
2
30
40

I am actually connecting this data to tableau. Are there any specialized tools on tableau to achieve the same task completed – user2569524 Feb 19 '14 at 16:21
1

Built-in ETL capability is an oft-requested feature for Tableau Desktop and may well appear in a future version of the product, but for now it's just the Excel add-in. – Talvalin Feb 19 '14 at 16:53

score 1 · Answer 2 · edited May 23 '17 at 11:49

1

SQL Server 2008 : Convert column value to row

http://blog.devart.com/is-unpivot-the-best-way-for-converting-columns-into-rows.html

edited May 23 '17 at 11:49

Community

1
1

answered Feb 19 '14 at 16:08

Revan

1,104
12
27

score 0 · Answer 3 · answered Feb 20 '14 at 17:56

Just to make sure you know about this Tableau feature:

Once you have devised the SQL select statement that will unpivot the data the way you'd like, then you can tell Tableau to use that instead of a select * by editing the data connection and selecting the Custom SQL option.

The generic way to unpivot in your situation is to union together several select statements, unless your database offers a more efficient alternative as described in the blog entry that Revanayya cited.

Andriy M · Answer 4 · 2014-02-25T11:56:34.987

The following would work for a static, known beforehand, set of metrics:

SELECT
  t.Date,
  x.Metric,

  CASE x.Metric
    WHEN 'metric1' THEN metric1_week
    WHEN 'metric2' THEN metric2_week
  END AS week_val,

  CASE x.Metric
    WHEN 'metric1' THEN metric1_13week
    WHEN 'metric2' THEN metric2_13week
  END AS "13week_val"

FROM
  atable AS t
CROSS JOIN
  (VALUES ('metric1'), ('metric2')) AS x (Metric)
;

You could build a dynamic query off the above to account for an unknown number of metrics. For that, you would need to read the metadata (probably the INFORMATION_SCHEMA.COLUMNS system view) to build the dynamic bits, which are the VALUES list and the two CASE expressions, before embedding them into the query.

Convert column oriented table to row oriented one

4 Answers4