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

Postgresql – Grouping row by previous row

I have Postgres data imported from a spreadsheet, where section headers have been imported along with the data. Is there a way to move the section headers to their own column? I have looked at window functions and I have tried a self join.

I am certain that the category names will always have a null "responsibility". It would have to rely on the order that they were inserted, there is no sort column. Is that reasonable?

I need to transform this:

  need                                 |responsibility            |
  -------------------------------------+--------------------------+
  Grounds Keeping                      |                          |
  Tree/shrub maintenance               |Parks and Recreation      |
  Tree Removal                         |Public Works              |
  Mowing                               |Parks and Recreation      |
  Fire Station Mowing                  |Fire                      |
  Irrigation Systems                   |Parks and Recreation      |
  Retaining Walls/Slope Maint.         |Capital Projects          |
  Building Maintenance                 |                          |
  Cleaning/Resupply                    |Building Occupant         |
  Electrical                           |Facility Maintenance      |
  Carpet Maintenance                   |Facility Operations       |
  Generators                           |Facility Maintenance      |
  Appliances                           |Facility Maintenance      |

into:


  category               |need                           |responsibility            |
  -----------------------+-------------------------------+--------------------------+
  Grounds Keeping        |Tree/shrub maintenance         |Parks and Recreation      |
  Grounds Keeping        |Tree Removal                   |Public Works              |
  Grounds Keeping        |Mowing                         |Parks and Recreation      |
  Grounds Keeping        |Fire Station Mowing            |Fire                      |
  Grounds Keeping        |Irrigation Systems             |Parks and Recreation      |
  Grounds Keeping        |Retaining Walls/Slope Maint.   |Capital Projects          |
  Building Maintenance   |Cleaning/Resupply              |Building Occupant         |
  Building Maintenance   |Electrical                     |Facility Maintenance      |
  Building Maintenance   |Carpet Maintenance             |Facility Operations       |
  Building Maintenance   |Generators                     |Facility Maintenance      |
  Building Maintenance   |Appliances                     |Facility Maintenance      |
Tags:

2

Answers


  1. 0

    put it here since it won’t fit in comments section :

    as it’s been mentioned here:

    If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on.

    And to add a new column for insert date , you have to alter the table and add the column and fill it with proper values whether you insert or manually when you want to update them:

    alter table [table_name] add column InsertDate timestamp

    see documentation above for more information.

    Keep in mind this is only one approach, not necessary the best approach.

    Login or Signup to reply.
  2. 0

    seems like this was an example of the last non-null puzzle. the strategy is to derive a row_number() column to group the values you want to ‘fill’ down so that you create a derived last-non-null column for your category

    SELECT *
FROM (
    WITH cte AS (
            SELECT *
                ,MAX(CASE 
                        WHEN category IS NOT NULL
                            THEN rn
                        END) OVER (
                    ORDER BY rn ROWS UNBOUNDED PRECEDING
                    ) AS grp
            FROM (
                SELECT row_number() OVER (
                        ORDER BY NULL
                        ) AS rn
                    ,responsibility
                    ,CASE 
                        WHEN responsibility IS NULL
                            THEN NULL
                        ELSE need
                        END AS _need
                    ,CASE 
                        WHEN responsibility IS NULL
                            THEN need
                        END AS category
                FROM test
                ) AS t2
            )
    SELECT MAX(category) OVER (
            PARTITION BY grp ORDER BY rn ROWS UNBOUNDED PRECEDING
            ) AS _category
        ,_need
        ,responsibility
    FROM cte
    ) AS t3
WHERE responsibility IS NOT NULL;

    the demo is here

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

Back To Top
Search