I have a table which looks like this <<
Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. Now we have to do your typing and guess at data types, keys, constraints and specs for you!
What you want to do is violate First Normal Form (1NF). Please look that up; each row should be a unique fact, relationship or entity in a data model.
From your picture and vague narrative, what you are calling a “customer_id” is not an identifier at all, but a sequence in a set of customers.
CREATE TABLE Customers
(customer_nbr CHAR(3) NOT NULL PRIMARY KEY,
customer_id INTEGER DEFAULT 1 NOT NULL –- wrong name!
CHECK (customer_seq IN (1,2)),
customer_name VARCHAR(15) NOT NULL);
INSERT INTO Customers
VALUES
('121', 1, 'James'),
('131', 2, 'John'),
('141', 1, 'James'),
('151', 2, 'John');
There will be only two customer_nbr values per customer_name or customer_id <<
See how that is enforced in the DDL I had to do for you? But this means that customer_name and customer_id are redundant. The goal of any data base is to remove redundancy in data. Do you know what normalization means?
CREATE TABLE Customers
(customer_id INTEGER DEFAULT 1 NOT NULL PRIMARY KEY
CHECK (customer_seq IN (1,2)),
customer_name VARCHAR(15) NOT NULL);
INSERT INTO
VALUES (1, 'James'), (2, 'John');
CREATE TABLE Customer_Accounts
(customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
customer_nbr CHAR(3) NOT NULL PRIMARY KEY);
INSERT INTO Customer_Accounts
VALUES
(1, '121'),
(1, '141'),
(2, '151'),
(2, '131');
Now I need to the results as this in a select query
customer_id customer_nbr customer_nbr1 customer_nbr2 <<
SQL is based on a tiered architecture. The database tier handles all of the database retrieval and data integrity. But nothing else. The data display and formatting is done in presentation layers that get data from the database layer.
If you really do not care about doing good SQL, we can kludge it with a self-join. But if you start programming like this, just looking for a kludge without understanding RDBMS, you will probably stay that way.