1

I have table of data that is sorted as follows:

Item    |   Sample  |   Value   |
---------------------------------
Part A  |   Top     |   1.0     |
Part A  |   Bottom  |   4.0     |
Part A  |   Left    |   3.0     |
Part A  |   Right   |   2.0     |
Part A  |   Center  |   5.0     |
Part B  |   Top     |   6.0     |
Part B  |   Bottom  |   1.0     |
Part B  |   Left    |   1.0     |
Part B  |   Right   |   5.0     |
Part B  |   Center  |   9.0     |
Part C  |   Top     |   7.0     |
Part C  |   Bottom  |   5.0     |
Part C  |   Left    |   1.0     |
Part C  |   Right   |   2.0     |
Part C  |   Center  |   1.0     |

That I'm trying to perform further statistical analysis on. For those calculations, it would be ideal for the data to be outputted like this:

Item    |   Top     |   Bottom  |   Left    |   Right   |   Center  |
---------------------------------------------------------------------
Part A  |   1.0     |   4.0     |   3.0     |   2.0     |   5.0     |
Part B  |   3.0     |   1.0     |   1.0     |   5.0     |   9.0     |
Part C  |   7.0     |   5.0     |   1.0     |   2.0     |   1.0     |

I believe in MSSQL you can use the PIVOT or UNPIVOT functions to accomplish this, but I'm unsure in Postgresql. All the similar solutions I found seemed vague. Any help or guidance would be appreciated!

Eric Alan Hill
  • 165
  • 1
  • 8

1 Answers1

0

Use the crosstab() function of the tablefunc extension for that.

I have posted multiple examples with links and detailed explanation on SO recently. Try a search.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks Erwin, didn't find your articles when I was looking. For anyone else, here is an almost identical example: http://stackoverflow.com/questions/8490478/transposing-an-sql-result-so-that-one-column-goes-onto-multiple-columns/8492477#8492477 – Eric Alan Hill Jun 20 '12 at 18:47