0

I have the data returned from from one my sql like

node_no  code  value1  order
100       AB     001     1
100       AB     007     2
101       AB     010     3

I have to further process this data using sql, to get output like 

node_no   code  value1  value2
100        AB     001     007
101        AB     010     null

The requirement being to display a column value from two rows(or rows with same node_no) into a single row as two different columns. The order has to be maintained.

Note: This may appear like duplicate of SQL Query to concatenate column values from multiple rows in Oracle SQL Query to concatenate column values from multiple rows in Oracle

But there we are displaying values as a single aggregated column.

Community
  • 1
  • 1
user1933888
  • 2,897
  • 3
  • 27
  • 36
  • 1
    You need to define the business logic needed more clearly. For example, how do you determine which column `001` goes in to? It it because it is smaller than `007` or because it's `order` values is the lowest? What should happen is a node has more than two values? etc, etc, etc... – MatBailie Jul 21 '14 at 08:21
  • It is based on `order' field, sequential. node will not have more than two values, only 1 or max 2 Thx for the follow-up questions. – user1933888 Jul 21 '14 at 08:42

2 Answers2

0

Try this:

with cte as 
(select node_no, code, value1,
row_number() over (partition by node_no order by order) rn
from tbl)

select a.node_no, a.code, a.value1 x, b.value1 value2 
from
cte a
left join cte b on a.node_no = b.node_no
and b.rn = 2
and a.rn = 1
where a.rn = 1

SQLFiddle

Note that this assumes that your value1 field is varchar2, so if you want to do some custom sorting other than the standard lexicographical one, you need to implement it yourself in the order by clause.

Another solution, suggested below in comments by @MatBailie removes the need for joins (the CTE is still needed):

SELECT node_no, 
MAX(code) as code, 
MAX(CASE WHEN rn=1 THEN value1 END) AS value1, 
MAX(CASE WHEN rn=2 THEN value1 END) AS value2 
FROM cte 
GROUP BY node_no
shree.pat18
  • 21,449
  • 3
  • 43
  • 63
  • 2
    You don't need to use a join... `SELECT node_no, MAX(code) as code, MAX(CASE WHEN rn=1 THEN value1 END) AS value1, MAX(CASE WHEN rn=2 THEN value2 END) AS value2 FROM cte GROUP BY node_no` This will reduce both the number of reads and number of cpu cycles by not needing to process the data twice. *(Also, by having `b.rn=2` in the WHERE clause, you've broken your LEFT JOIN and turned it in to an INNER JOIN; because if it's not present in the join the value will be NULL which is never equal to 2.)* – MatBailie Jul 21 '14 at 08:24
  • @MatBailie You are right, the `WHERE` does indeed change the meaning. Should I edit my answer with your suggestions, or would that be like stealing your credit? – shree.pat18 Jul 21 '14 at 08:31
  • @MatBailie My understanding is that since a `node_no` can have 2 rows, a row with `rn` = 1 must always exist in the result if it exists in input. So, the join specifies `a.rn = 1 and b.rn = 2` and an additional `where` gets only the first row from each `node_no`. Does that make sense? – shree.pat18 Jul 21 '14 at 08:35
  • If you `PARTITION BY` a column, the `ORDER BY` clause doesn't have to include that column. – Clockwork-Muse Jul 21 '14 at 08:49
  • 1
    Always feel free to "steal credit" from people that haven't written their own answer :) – MatBailie Jul 21 '14 at 09:09
0

Recent versions of Oracle also support the PIVOT clause. You still have to get a constant column-identifier, though:

SELECT node_no, code, value1, 
         ROW_NUMBER() OVER(PARTITION BY node_no ORDER BY ordering) rn
 FROM <table_name_here>)

(I renamed order to ordering, as the former is a reserved word, and needs to be escaped/should be avoided if possible)
...will, as in @Shree's answer, number all rows based on the node. This means we have a constant value per-section:

node_no   code   value1  order  rn
100       AB     001     1      1
100       AB     007     2      2
101       AB     010     3      1

At this point, it turns into a standard PIVOT query:

SELECT node_no, code, value1, value2
FROM (SELECT node_no, code, value1, 
             ROW_NUMBER() OVER(PARTITION BY node_no ORDER BY ordering) rn
      FROM Pivot_Example) Indexed
PIVOT (MAX(value1) FOR (rn) IN (1 AS value1, 2 AS value2))
ORDER BY node_no

(PIVOT requires an aggregate function, but in this case we don't have anything to aggregate, so...)

SQL Fiddle Example

I'm not sure if this will be more efficient than the existing answer, though.

Clockwork-Muse
  • 12,806
  • 6
  • 31
  • 45