Two row values as One row with two columns?

Transact-SQL
1

Hi friends,

I have a table which looks like this

CustomerId CustomerName CustomerNumber
1 james 121
2 John 131
1 James 141
2 John 151

There will be only two CustomerNumber values per customerName or CustomerID

Now I need to the results as this in a select query

CustomerId CustomerNumner CustomerNumber1 CustomerNumber2
1 James 121 141
2 John 131 151

2

select t1.CustomerId, t1.CustomerName, t1.CustomerNumber, t2.CustomerNumber
from #t t1
join #t t2 on t1.CustomerId = t2.CustomerId and t1.CustomerNumber < t2.CustomerNumber;

3

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. :cry:

4

You don't need to join the table to itself for that output:

SELECT CustomerId, CustomerName,
     MIN(CustomerNumber) AS CustomerNumber1,
     CASE WHEN MIN(CustomerNumber) = MAX(CustomerNumber) --no second cust# found
          THEN NULL 
          ELSE MAX(CustomerNumber) END AS CustomerNumber2
FROM table_name
GROUP BY CustomerId, CustomerName