Multiple row data transformed into single

Question

I'm using SQLite and I"m really green. Essentially I have a table (example below)

ID  Date    QuestNum    Value
1   1/1/1990    1   0
1   1/1/1990    2   3
1   1/1/1990    3   2
1   1/2/1990    1   5
1   1/2/1990    2   2
1   1/2/1990    3   6
2   1/1/1990    1   6
2   1/1/1990    2   3
2   1/1/1990    3   6
2   1/2/1990    1   2
2   1/2/1990    2   8
2   1/2/1990    3   2

And I would the result of my query to look like this

ID  Date    Quest1  Quest2  Quest3
1   1/1/1990    0   3   2
1   1/2/1990    5   2   6
2   1/1/1990    6   3   6
2   1/2/1990    2   8   2

The closest I got was

SELECT 
    ID, Date,
    MAX(CASE WHEN QuestNum = "1" THEN Value END) AS Q1,
    MAX(CASE WHEN QuestNum = "2" THEN Value END) AS Q2,
    MAX(CASE WHEN QuestNum = "3" THEN Value END) AS Q3,
FROM table
GROUP BY subjID, Date

Although this does provide the structure I'm looking for it applies the MAX aggregate function and I'm worried that the resulting query holds data that was different from the original set; such that the values returned in columns Quest 1, Quest 2, Quest 3 are the maximum value from that selected column and not the corresponding data point from the value column.

I'm not sure if it is important to note but the QuestNum can skip at times, so that not everyone receives all questions.

Thanks, qwerty

Please excuse any misnomers in my request, believe me they are all unintentional.

Your question isnt clear. You should explain what you want with your desire output. We are good but still cant read minds — Juan Carlos Oropeza, Oct 01 '15 at 22:50
If the only thing you are worry is alter your data, `group by ` or `MAX()` alone wont do that. you need have `update` to change your data. That is just creating a result for you to display — Juan Carlos Oropeza, Oct 01 '15 at 22:52
I apologize for any confusion I'm still learning the lingo also thank you all for your quick responses. I misspoke when I stated that I was worried about MAX() altering the data. What I meant was that I was worried that the query result held data that was different from the original set; such that the values returned in columns Quest 1, Quest 2, Quest 3 are the maximum value from that selected column and not the corresponding data point from the value function. — qwerty, Oct 01 '15 at 23:31
Possible duplicate of [Pivot in SQLite](http://stackoverflow.com/questions/1237068/pivot-in-sqlite) — Juan Carlos Oropeza, Oct 01 '15 at 23:53

score 1 · Answer 1 · answered Oct 01 '15 at 22:59

1

Use GROUP BY ID, Date as group expression.

And SELECT never alters your data, except some functions in some databases (like nextval which modifies a sequence), but these changes are explicitly documented. simple aggregate functions like this are totally safe.

But what you see is not a table, it's only a result. If you need a new table containing the result, you need a statement like

SELECT ID,Date,
MAX(CASE WHEN QuestNum = "1" THEN Value END) AS Quest1,
MAX(CASE WHEN QuestNum = "2" THEN Value END) AS Quest2,
MAX(CASE WHEN QuestNum = "3" THEN Value END) AS Quest3,
FROM table
INTO table2
GROUP BY GROUP BY ID, Date

(Note the INTO table2)

answered Oct 01 '15 at 22:59

Daniel Alder

5,031
2
45
55

Hi Daniel, Thank you so much for your quick response. Unfortunately, in my original post I misspoke but that doesn't make your answer any less helpful and I will definitely still use the information you provided! What I was trying to establish was that I was worried that the return from the query provided data in the newly formed columns Quest 1-3 with data which was different from the original corresponding data from the values column. If this is still unclear please let me know and I'll do my best to clarify. I'm still learning and currently I'm on the slower side of the learning curve. – qwerty Oct 01 '15 at 23:58

Parfait · Accepted Answer · 2015-10-02T16:08:37.163

1

Essentially, you need to transpose your rows to columns or reshape from long to wide format. In SQL, this can be done with a derived table (type of subquery):

SELECT 
      [ID],
      [Date],
      Max(CASE WHEN QuestNum = "1" THEN [Value] END) As Q1,
      Max(CASE WHEN QuestNum = "2" THEN [Value] END) As Q2, 
      Max(CASE WHEN QuestNum = "3" THEN [Value] END) As Q3
FROM (SELECT 
            [ID], 
            [Date], 
            QuestNum,
            [Value]
      FROM TableName) 
AS dT
GROUP BY [ID], [Date]

edited Oct 02 '15 at 16:08

answered Oct 02 '15 at 00:47

Parfait

104,375
17
94
125

What's the difference to queries without subquery (from the other answers)? The reshaping was already done in the OP. I use subqueries in many situations, but here I can't find an advantage – Daniel Alder Oct 02 '15 at 08:06
@DanielAlder Indeed! In this situation, subqueries are redundant. I did remove the outer derived table. In way, this is a more generalized version of the transpose/pivot SQL in case in-between calculations or aggregate joins/unions are needed. Let's hope the OP can chime in. – Parfait Oct 02 '15 at 16:15

dcieslak · Answer 3 · 2015-10-02T03:32:53.180

The query is very simple (or you missing something in your description):

select 
 id ,  
max(CASE WHEN QuestNum = 1 THEN Value else 0 END ) as Q1 ,
 max(CASE WHEN QuestNum = 2 THEN Value else 0 END ) As Q2, 
 max(CASE WHEN QuestNum = 3 THEN Value else 0 END ) As Q3,
 [date]
from tab
group by id, [date]

The result is exaclty like you wanted:

ID  Q1  Q2  Q3  Date
1   0   3   2   1/1/1990
1   5   2   6   1/2/1990
2   6   3   6   1/1/1990
2   2   8   2   1/2/1990

It can be validated here

SQLFiddle example

Multiple row data transformed into single

3 Answers3