Oracle multi-line merged into a line, and inline new columns of different value. Display on the same-CodePudding

As shown in figure if there are four rows of data in the table, will be merged into two rows of data, and the same field of different values of the new column to display the data (as shown in figure, after the 5 different values of column) how to implement,

CodePudding user response:

What is the expected results?

CodePudding user response:

reference 1st floor wmxcn2000 response:

expected result is what kind of?

Station_id, device_id, box_type_name box_id tick_num1, ticknum2, oper_type_name1 oper_type_name2...
In this way, it is hard to understand my question

CodePudding user response:

Line 4 as a benchmark to merge the same data, before the five columns respectively the different value of add a column to represent data, the last effect is got 14 column values,
That is to say, in the same place twice on the same equipment operation, write the two operations in a row,

CodePudding user response:

reference w1016373004 reply: 3/f

4 as a benchmark to merge the same data line before, five columns of different value behind each add a column to represent data, the last effect is got 14 column values,
That is to say, in the same place twice on the same equipment operation, write the two operations in a row,

The first 4 columns must be two of the same, there is only one, there are N
?After five columns, and of the two: column 1 (1) the column 1 (2),,, 5 (1) row 5 (2), which in the first order requirements

CodePudding user response:

reference 4 floor Diza1986 response:

Quote: reference w1016373004 reply: 3/f

Line 4 as a benchmark to merge the same data, before the five columns respectively the different value of add a column to represent data, the last effect is got 14 column values,
That is to say, in the same place twice on the same equipment operation, write the two operations in a row,

The first 4 columns must be two of the same, there is only one, there are N
?After five columns, and of the two: column 1 (1) the column 1 (2),,, 5 (1) row 5 (2), which in the first order request

Now suppose there are two duplicate data in the 4 column value before, at the back of the type of column according to unload and install successively, according to
Column 1 (1) column is 1 (2),,, 5 (1) row 5 (2)
Column 1 (1) the number of columns 1 (2) the number of,,, column 5 installation time (1), (2) over a period of five to unload

CodePudding user response:

 
SELECT R1 station_id, R1. Device_id, R1. Box_type_name, R1. Box_id. R1, R2. Tick_num1 tick_num1... 
(SELECT * FROM (
SELECT a T1. *, ROW_NUMBER () OVER (PATITION BY station_id, device_id, box_type_name, box_id) RN FROM T1) WHERE RN R1=1) JOIN 
SELECT * FROM (
SELECT a T1. *, ROW_NUMBER () OVER (PATITION BY station_id, device_id, box_type_name, box_id) RN FROM T1) WHERE RN R2=2) 
ON......

CodePudding user response:

SQL here, which master a try,
SQL file address http://w698-1251478258.costj.myqcloud.com/%E5%A4%9A%E8%A1%8C%E5%90%88%E5%B9%B6%E9%87%8D%E5%A4%8D%E5%80%BC%E8%BD%AC%E5%88%97.sql

Excel file address http://w698-1251478258.costj.myqcloud.com/12.xlsx

CodePudding user response:

 
- this is too long. You wrote a simple example 
WITH a AS 
C1 (SELECT 1, 'a' c2 and c3 FROM 1 dual 
UNION ALL 
SELECT 1 c1 and c2, 'b' 2 c3 FROM dual 
UNION ALL 
SELECT 2 c1, c2, 'c' 1 c3 FROM dual 
UNION ALL 
SELECT 2 c1 and c2, 'd' 3 c3 FROM dual) 
SELECT 
C1, 
Max (CASE WHEN rnum=1 THEN c2 END) c2_1, 
Max (CASE WHEN rnum=2 THEN c2 END) c2_2, 
Max (CASE WHEN rnum=1 THEN c3 END) c3_1, 
Max (CASE WHEN rnum=2 THEN c3 END) c3_2 FROM 
(
SELECT a. *, row_number () OVER (PARTITION BY c1 ORDER BY c2) rnum FROM a 
A1) 
GROUP BY c1; 

C1 C2_1 C2_2 C3_1 C3_2 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 a, 1 b, 2 
2 c 1 3 d

[/code]

CodePudding user response:

refer to the eighth floor u012557814 response:

 
- this is too long. You wrote a simple example 
WITH a AS 
C1 (SELECT 1, 'a' c2 and c3 FROM 1 dual 
UNION ALL 
SELECT 1 c1 and c2, 'b' 2 c3 FROM dual 
UNION ALL 
SELECT 2 c1, c2, 'c' 1 c3 FROM dual 
UNION ALL 
SELECT 2 c1 and c2, 'd' 3 c3 FROM dual) 
SELECT 
C1, 
Max (CASE WHEN rnum=1 THEN c2 END) c2_1, 
Max (CASE WHEN rnum=2 THEN c2 END) c2_2, 
Max (CASE WHEN rnum=1 THEN c3 END) c3_1, 
Max (CASE WHEN rnum=2 THEN c3 END) c3_2 FROM 
(
SELECT a. *, row_number () OVER (PARTITION BY c1 ORDER BY c2) rnum FROM a 
A1) 
GROUP BY c1; 

C1 C2_1 C2_2 C3_1 C3_2 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 a, 1 b, 2 
2 c 1 3 d

[/code]

Do you like this life,