0

I have a table (table1) in which IDs of transactions, transaction state (there are 10 total states) and their time of entering that state are stored.

I want to feed this data into another table which would have 11 columns: 1 for the Transaction ID and the other 10 for the time that they enter each state.

I want to write this as an SQL query. I looked for it and found

insert into table2(ID, s1, s2, s3, s4 ... )
select ID, time from table1
where [];

but I don't have 10 different columns in table1 to copy from as there are 10 different rows corresponding to each ID and each state.

Please help me out in doing this is 1 query.

I am new to SQL. Thanks in advance.

4
  • You need to "pivot". Inform yourself about that type of query, and perhaps you will realize you can continue quite happily with just one table in your application. Commented May 22, 2018 at 8:13
  • 1
    Please add your table schema and, if possible a sample data.
    – McNets
    Commented May 22, 2018 at 8:17
  • And please tag one DBMS only. Is it SQL-Server or MySQL?
    – McNets
    Commented May 22, 2018 at 8:19
  • Thanks for notifying. I am new to this server. It's mysql that I have to work with. Commented May 22, 2018 at 8:28

4 Answers 4

0

Standard method to "pivot" data assuming the pair (id,state) is unique:

SELECT id,
       MAX(CASE WHEN state = 1 THEN `time` END) s1,
       MAX(CASE WHEN state = 2 THEN `time` END) s2,
       --     ..............................
       MAX(CASE WHEN state = 10 THEN `time` END) s10
FROM table1 
GROUP BY id;

That is solution for MySQL server.

5
  • Hi Akina - I ran your query in my fiddle here (see bottom of fiddle) - the result is wrong! 1 2018-05-22 09:00:00 null null 2 null 2018-05-22 09:00:00 null
    – Vérace
    Commented May 22, 2018 at 8:37
  • @Vérace I use numbers for states. You use strings. And You mix up fieldnames. Modify values in CASE WHEN statements. They are to look like CASE WHEN state_type = 'state_1' THEN transition_time END. dbfiddle.uk/…
    – Akina
    Commented May 22, 2018 at 8:40
  • @Akina - maybe there's something I haven't understood - could you take a look here?
    – Vérace
    Commented May 22, 2018 at 8:50
  • @Vérace Again You use state_id in CASEs instead of state_type.
    – Akina
    Commented May 22, 2018 at 8:57
  • Ah, yes - <Doh... slaps forehead> - stupidly used 1, 2, 3 for both type and id - <note to self - don't do this again!>. Thanks for taking the time to point that out!
    – Vérace
    Commented May 22, 2018 at 9:12
1

Find an example of a pivot table here.

2
  • Pivoting technique was exactly what I was looking for but I didn't know the name. Thank You. Commented May 22, 2018 at 8:47
  • You could mark the answer as helpful if you found it so.
    – Vérace
    Commented May 22, 2018 at 8:53
0

To answer your question, I did the following:

Created the tables:

CREATE TABLE state 
(
  state_id INTEGER, 
  state_type VARCHAR(10), 
  transition_time DATETIME,
  PRIMARY KEY (state_id, state_type)
);

CREATE TABLE summary 
(
  state_id INTEGER,
  s1_type_1 VARCHAR(10),
  s1_t1 DATETIME, 
  s1_type_2 VARCHAR(10),
  s2_t2 DATETIME, 
  s1_type_3 VARCHAR(10),
  s3_t3 DATETIME,
  PRIMARY KEY (state_id)
);

In my summary table, I've kept the state_type - this is not necessary for the final answer (you can just remove them from the query), but I left them in as it's easier to see how the answer was derived!

And then I created some sample data - I'm only using 3 columns, but the same principle applies to 10.

INSERT INTO state VALUES (1, 'state_1', '2018-05-22 07:00:00');
INSERT INTO state VALUES (1, 'state_2', '2018-05-22 08:00:00');
INSERT INTO state VALUES (1, 'state_3', '2018-05-22 09:00:00');
INSERT INTO state VALUES (2, 'state_1', '2018-05-22 07:00:00');
INSERT INTO state VALUES (2, 'state_2', '2018-05-22 08:00:00');
INSERT INTO state VALUES (2, 'state_3', '2018-05-22 09:00:00');

And then ran the following query.

INSERT INTO summary
SELECT s1.state_id, s1.state_type, s1.transition_time, s2.state_type, s2.transition_time, s3.state_type, s3.transition_time
FROM state s1
JOIN state s2 ON s1.state_id = s2.state_id
AND s1.state_type = 'state_1'
AND s2.state_type = 'state_2'
AND s2.transition_time = (SELECT transition_time FROM state WHERE state_id = s2.state_id AND state_type = 'state_2')
JOIN state s3 ON s1.state_id = s3.state_id
AND s1.state_type = 'state_1'
AND s3.state_type = 'state_3'
AND s3.transition_time = (SELECT transition_time FROM state WHERE state_id = s3.state_id AND state_type = 'state_3');

Result:

state_id state_type    transition_time  state_type     transition_time   state_type   transition_time       
       1    state_1 2018-05-22 07:00:00    state_2  2018-05-22 08:00:00   state_3   2018-05-22 09:00:00
       2    state_1 2018-05-22 07:00:00    state_2  2018-05-22 08:00:00   state_3   2018-05-22 09:00:00

This is all available in a fiddle here. p.s.welcome to the forum!

-1

Please try below if something helpful

insert into table2(ID, s1, s2, s3, s4) 
select ID, time as t1, time as t2, time as t3, time as t4 
from table1 where [];
1
  • Now that is a good one, Kuldeep! Commented May 22, 2018 at 8:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.