PostgreSQL select method like excel

Question

when i handle BI data，I found the same data is not i need，so，i want use postgresql handle data， I want get the reuslt like this style,the postgesql select how write ?

    -- ----------------------------
-- Table structure for data_test
-- ----------------------------
DROP TABLE IF EXISTS "public"."data_test";
CREATE TABLE "public"."data_test" (
  "c_id" int4 NOT NULL,
  "n_id" int4,
  "b_id" int4
)
;

-- ----------------------------
-- Records of data_test
-- ----------------------------
INSERT INTO "public"."data_test" VALUES (58748, 587, 55);
INSERT INTO "public"."data_test" VALUES (5895, 587, 55);
INSERT INTO "public"."data_test" VALUES (12131, 567, 53);
INSERT INTO "public"."data_test" VALUES (33412, 568, 54);
INSERT INTO "public"."data_test" VALUES (5841, 569, 52);
INSERT INTO "public"."data_test" VALUES (587, 500, 51);
INSERT INTO "public"."data_test" VALUES (5871, 500, 51);

Gordon Linoff · Accepted Answer · 2021-08-28 16:44:01Z

1

You can use window functions:

select c_id, n_id,
       (case when row_number() over (partition by n_id order by c_id) = 1
             then b_id
        end) as b_id
from data_test
order by n_id, c_id;

Note that this returns NULL rather then - -- which is what I assume you actually intend by - given that - is not compatible with int4.

Here is a db<>fiddle.

answered Aug 28, 2021 at 16:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL select method like excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related