CODEWITHSUNDEEP
sqlpostgresqltableau-apigreenplum
user2569524
user2569524

Reputation: 1751

Convert column oriented table to row oriented one

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!

Upvotes: 1

Views: 2079

Answers (4)

Andriy M
Andriy M

Reputation: 77737

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.

Upvotes: 0

Alex Blakemore
Alex Blakemore

Reputation: 11921

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.

Upvotes: 0

Revan
Revan

Reputation: 1144

SQL Server 2008 : Convert column value to row

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

Upvotes: 1

Talvalin
Talvalin

Reputation: 7889

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

Upvotes: 1

Related Questions