Question 89639 in PostgreSQL Question posted in
The official documentation can be found here.

postgresql query about little troubled

Can someone help me to achieve below mentioned requirement in postgresql.

Input table:

Col1 Col2 Col3 Col4
Jan   XXX  YYY  1234
Jan   XXX  YYY  5264
Jan   XXX  YYY  4736
Feb   ZZZ  WWW 123
Feb   ZZZ  WWW 456

Output text file: ( Delimited)

Col1 Col2 Col3 Col4
Jan   XXX  YYY  1234
NULL NULL NULL  5264
NULL NULL NULL  4736
Feb   ZZZ  WWW 123
NULL NULL NULL 456

I want like this column

Tags:

2

Answers


  1. 0

    You can do:

    select
  case when rn = 1 then col1 end as col1,
  case when rn = 1 then col2 end as col2,
  case when rn = 1 then col3 end as col3,
  col4
from (
  select t.*, 
    row_number() over(partition by col1, col2, col3 order by col4) as rn
  from t
) x

    However, formatting should be done at the UI level, not at the query level. I would strongly suggest you do it in the UI.

    Login or Signup to reply.
  2. 0

    To achieve the desired output in PostgreSQL, you can use the LAG function to compare the current row with the previous row and check if the values of Col1, Col2 and Col3 are the same. If they are different, then you can set the values to NULL for Col2 and Col3.

    Here’s an example query that should achieve the desired output:

    SELECT
  CASE
    WHEN Col1 = LAG(Col1) OVER (ORDER BY Col1, Col4)
      AND Col2 = LAG(Col2) OVER (ORDER BY Col1, Col4)
      AND Col3 = LAG(Col3) OVER (ORDER BY Col1, Col4)
    THEN NULL
    ELSE Col1
  END AS Col1,
  CASE
    WHEN Col1 = LAG(Col1) OVER (ORDER BY Col1, Col4)
      AND Col2 = LAG(Col2) OVER (ORDER BY Col1, Col4)
      AND Col3 = LAG(Col3) OVER (ORDER BY Col1, Col4)
    THEN NULL
    ELSE Col2
  END AS Col2,
  CASE
    WHEN Col1 = LAG(Col1) OVER (ORDER BY Col1, Col4)
      AND Col2 = LAG(Col2) OVER (ORDER BY Col1, Col4)
      AND Col3 = LAG(Col3) OVER (ORDER BY Col1, Col4)
    THEN NULL
    ELSE Col3
  END AS Col3,
  Col4
FROM InputTable
ORDER BY Col1, Col4;

    In this query, the LAG function is used to compare the current row with the previous row in the order of Col1 and Col4. If the values of Col1, Col2, and Col3 are the same as the previous row, then NULL values are assigned to Col2 and Col3. Otherwise, the values of Col1, Col2, Col3, and Col4 are returned as is.

    This should give you the desired output in the format you specified.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search