SQL for specific pattern of output?

  • Thread starter Thread starter Leslie Charles
  • Start date Start date
L

Leslie Charles

The following example is a simplification to make my
question less complicated.

I have a database of 3 people: Brown, Jones, and
Smith.

Each person in the DB has 2 fields associated with
their name field: Field “A†and “Bâ€. The values in
A and B are either 0 or 1. The following SQL
string extracts a query as shown below.

SELECT tblPeople.LName,tblPeople.A,tblPeople.B FROM tblPeople

Brown 1 0
Jones 1 1
Smith 0 0

But the IT department wants the data as follows:

Brown A 1
Brown B 0
Jones A 1
Jones B 1
Smith A 0
Smith B 0

Is there a way to change the SQL string to provide the needed output?
 
SELECT tblPeople.LName, "A", tblPeople.A
FROM tblPeople
UNION
SELECT tblPeople.LName, "B", tblPeople.B
FROM tblPeople
ORDER BY 1, 2 ;
 
The following example is a simplification to make my
question less complicated.

I have a database of 3 people: Brown, Jones, and
Smith.

Each person in the DB has 2 fields associated with
their name field: Field “A” and “B”. The values in
A and B are either 0 or 1. The following SQL
string extracts a query as shown below.

SELECT tblPeople.LName,tblPeople.A,tblPeople.B FROM tblPeople

Brown 1 0
Jones 1 1
Smith 0 0

But the IT department wants the data as follows:

Brown A 1
Brown B 0
Jones A 1
Jones B 1
Smith A 0
Smith B 0

Is there a way to change the SQL string to provide the needed output?
Click to expand...

A UNION query will do this:

SELECT tblPeople.LName, "A" AS FromWhat, tblPeople.A FROM tblPeople
UNION ALL
SELECT tblPeople.LName, "B", tblPeople.B FROM tblPeople
ORDER BY 1, 2;
 
Back
Top