I have the following table (tbTest):

I'm trying to build a query to get the following result:

id_main|operation|id_cli|name  |dueDate   |value|debtShare|address      |id_parcel|d1        |d2        |d3        |d4        |d5|Type|
    253|       66|  9876|Johnny|2018-11-01|  1.2|        1|abc street 12|     7197|2018-11-01|          |          |          |  |N   |
    253|       67|  9876|Johnny|2018-11-01|  3.7|        4|abc street 12|     7198|2018-11-01|          |          |          |  |N   |
    254|       68|  9876|Johnny|2015-11-20|  9.3|        1|abc street 12|     4670|2015-11-20|2016-09-20|2017-11-20|2020-12-22|  |Y   |
    254|       68|  5432|David |2015-11-20|  9.3|        2|axe avenue 46|     4670|2015-11-20|2016-09-20|2017-11-20|2020-12-22|  |Y   |

In this table, when Type=Y, the dueDate value goes to columns d1, d2 etc, ordered by date; and when Type=N, there is only one dueDate value, which goes to column d1.

I've searched and found a (not so) similar problem. Based on it, I've tried to do what I need, but have no success.

http://sqlfiddle.com/#!18/2144b6/5

Can someone help me, please?

Thanks in advance.

CodePudding user response：

Some messed up data you have there!

SELECT 
  id_main, 
  id_operation, 
  id_cli, 
  MAX(name), 
  MIN([duedate]), 
  MAX(value), 
  MAX(debtshare), 
  MAX(address),
  MAX(CASE WHEN rnvd = 1 THEN id_parcel END) as id_parcel,
  MAX(CASE WHEN rnd = 1 THEN [duedate] END) as d1,
  MAX(CASE WHEN rnd = 2 THEN [duedate] END) as d2,
  MAX(CASE WHEN rnd = 3 THEN [duedate] END) as d3,
  MAX(CASE WHEN rnd = 4 THEN [duedate] END) as d4,
  MAX(CASE WHEN rnd = 5 THEN [duedate] END) as d5
  -- MAX([type])
FROM
  (SELECT *, ROW_NUMBER() OVER(PARTITION BY id_main, id_operation, id_cli ORDER BY value DESC) as rnvd, ROW_NUMBER() OVER(PARTITION BY id_main, id_operation, id_cli ORDER BY [duedate]) as rnd 
   FROM tbTest
  ) x
GROUP BY id_main, id_operation, id_cli
ORDER BY 1,2,3 DESC

Your fiddle didn't have [type] in, but I'm sure you can add it/adjust this to what you have

http://sqlfiddle.com/#!18/2144b6/17

CodePudding user response：

This seems to work, though there's probably a more efficient way:

with baseRows as (
    select distinct id_operation, id_cli FROM tbTest
), dueDates as (
    select 
        row_number() over (partition by id_operation, id_cli order by dueDate) as rn,
        id_operation, id_cli, dueDate
    from tbTest x 
)
SELECT f.*, d1.dueDate, d2.dueDate, d3.dueDate, d4.dueDate, d5.dueDate
FROM baseRows b
CROSS APPLY (SELECT TOP 1 * FROM tbTest WHERE id_operation = b.id_operation and id_cli = b.id_cli) f
INNER JOIN dueDates d1 on d1.id_operation = b.id_operation and d1.id_cli = b.id_cli
     and d1.rn = 1
LEFT JOIN dueDates d2 on d2.id_operation = b.id_operation and d2.id_cli = b.id_cli
     and d2.rn = 2
LEFT JOIN dueDates d3 on d3.id_operation = b.id_operation and d3.id_cli = b.id_cli
     and d3.rn = 3
LEFT JOIN dueDates d4 on d4.id_operation = b.id_operation and d4.id_cli = b.id_cli
     and d4.rn = 4
LEFT JOIN dueDates d5 on d5.id_operation = b.id_operation and d5.id_cli = b.id_cli
     and d5.rn = 5 
ORDER BY id_operation, id_cli

http://sqlfiddle.com/#!18/2144b6/13

Note I left id_main out of the partitions and joins, because it was the same in every record of the sample data. You may need to put that back in.