2

I have a table with column headers like this:

p_id|s_id|value

So the table is like:

1|1|39
1|2|97
1|3|42

the first column is p_id, second is s_id, third is value.

There are 673 or so distinct values for p_id, there are about six possible values for s_id, and value is an integer. Each (p_id, s_id) pair maps to a value, and there are six of these values for each p_id (because there are only six possible s_id's). Here are some examples:

(p_id, s_id, value)
(1, 1, 238), (1, 2, 489), ... (1, 6, 391),
(2, 1, 380), (2, 2, 112), ... (2, 6, 402),
. . .
(673, 1, 371), (673, 2, 239), ... (673, 6, 230)

So here's what I want as column headers for what I'm trying to get as output:

p_id 1 2 3 4 5 6

And then the table would be populated with the "value"s.

How do I do this with SQL statements? Do I need to make a new table?

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
user1854885
  • 99
  • 1
  • 7
  • 1
    What you are looking for is called `PIVOT`, but AFAIK it's not supported by SQLLite. The closest you could do is described [here](http://stackoverflow.com/questions/1237068/pivot-in-sqlite) – ShyJ Nov 26 '12 at 23:19
  • that looks really painful! I'm just a beginner... I suppose I'll just generate a table for each s_id, and then use those. I'm trying to query a database to generate matrices to use in gnu octave. – user1854885 Nov 26 '12 at 23:21

1 Answers1

0

A simple Case statement will work. Try this:

SELECT    
    p_id, 
    MIN(CASE WHEN s_id = 1 THEN value END) AS [1], 
    MIN(CASE WHEN s_id = 2 THEN value END) AS [2], 
    MIN(CASE WHEN s_id = 3 THEN value END) AS [3], 
    MIN(CASE WHEN s_id = 4 THEN value END) AS [4],
    MIN(CASE WHEN s_id = 5 THEN value END) AS [5], 
    MIN(CASE WHEN s_id = 6 THEN value END) AS [6]
FROM        
    [TableName]
GROUP BY 
    p_id
Jon Wilmer
  • 23
  • 4