Need a help with the query

vinutauro

Could someone help me to write the query to get the below results for every row in the table
Table format:

Column1 Column2 Column3 Column4 Column5 Column6
a b server1 1 name1 value1
a b server2 2 name2 value2
a b server3 2 name3 value2
a b server4 3 name4 value3
a b server5 3 name5 value4
a b server6 3 name6 value5

Result for 1st row
Column1 Column2 Column3 Column4 Column5 Column6
a b server1 1 name1 value1
a b server2 1 name1 Not defined
a b server3 1 name1 Not defined
a b server4 1 name1 Not defined
a b server5 1 name1 Not defined
a b server6 1 name1 Not defined

**Result for 2nd row **
a b server1 2 name2 Not defined
a b server2 2 name2 value2
a b server3 2 name2 Not defined
a b server4 2 name2 Not defined
a b server5 2 name2 Not defined
a b server6 2 name2 Not defined

VValdar

Hi vanutauro,

What is the logic behind?
Is 1st row, 2nd row a parameter or do you want all the results in one go?

VValdar

I've supposed it's a parameter filled at the execution time:

create table vinutauro
( c1 char(1)
, c2 char(1)
, c3 char(7)
, c4 integer
, c5 char(5)
, c6 char(6)
)
order by c1, c2, c3
segmented by hash(c1, c2) all nodes;

insert into vinutauro (c1, c2, c3, c4, c5, c6) values
('a', 'b', 'server1', 1, 'name1', 'value1'),
('a', 'b', 'server2', 2, 'name2', 'value2'),
('a', 'b', 'server3', 2, 'name3', 'value2'),
('a', 'b', 'server4', 3, 'name4', 'value3'),
('a', 'b', 'server5', 3, 'name5', 'value4'),
('a', 'b', 'server6', 3, 'name6', 'value5');

with cte_data (c1, c2, c3, c4, c5, c6, rn) as
(
select c1, c2, c3, c4, c5, c6
     , row_number() over(partition by c1, c2 order by c3)
  from vinutauro
)
select c1, c2, c3
     , max(case :param when rn then c4 end) over(partition by c1, c2) as c4
     , max(case :param when rn then c5 end) over(partition by c1, c2) as c5
     , case :param when rn then c6 end as c6
  from cte_data
order by c1, c2, c3;

-- With param=1
c1     c2     c3        c4    c5     c6
-----  -----  -------  -----  -----  ------
a      b      server1      1  name1  value1
a      b      server2      1  name1  
a      b      server3      1  name1  
a      b      server4      1  name1  
a      b      server5      1  name1  
a      b      server6      1  name1  

-- With param=2
c1     c2     c3        c4    c5     c6
-----  -----  -------  -----  -----  ------
a      b      server1      2  name2  
a      b      server2      2  name2  value2
a      b      server3      2  name2  
a      b      server4      2  name2  
a      b      server5      2  name2  
a      b      server6      2  name2  

-- With param=5
c1     c2     c3        c4    c5     c6
-----  -----  -------  -----  -----  ------
a      b      server1      3  name5  
a      b      server2      3  name5  
a      b      server3      3  name5  
a      b      server4      3  name5  
a      b      server5      3  name5  value4
a      b      server6      3  name5  

vinutauro

@VValdar I need the result in one go. Above results I provided for row 1 and row 2 is to understand the results better

vinutauro

Modified the requirement as server names(column 3) can be repeated. In the result set same column4 and column5 should be repeated for each c1,c2, and c3, and c5 should come as "not defined" for the server other than for which the data exists. Also, I need the results in one go.

Column1 Column2 Column3 Column4 Column5 Column6
a b server1 1 name1 value1
a b server2 2 name2 value2
a b server3 2 name3 value2
a b server4 3 name4 value3
a b server5 3 name5 value4
a b server6 3 name6 value5
a b server1 4 name7 value6
a b server2 4 name8 value7

Result of 1st row
Column1 Column2 Column3 Column4 Column5 Column6
a b server1 1 name1 value1
a b server2 1 name1 Not defined
a b server3 1 name1 Not defined
a b server4 1 name1 Not defined
a b server5 1 name1 Not defined
a b server6 1 name1 Not defined

Result of 2nd row
Column1 Column2 Column3 Column4 Column5 Column6
a b server1 2 name2 Not defined
a b server2 2 name2 value2
a b server3 2 name2 Not defined
a b server4 2 name2 Not defined
a b server5 2 name2 Not defined
a b server6 2 name2 Not defined

Bryan_H

This appears to produce the correct format as a single query using the CREATE TABLE and INSERT written by VValdar:
with l2 as (select distinct c5 from vinutauro) select c1,c2,c3,c4,v.c5,CASE WHEN v.c5 = l.c5 then c6 else 'not defined' end from vinutauro v, l2 l;