CODEWITHSUNDEEP
sqlsqlite
Andre Martins
Andre Martins

Reputation: 1

SQLite: Query to list

I have two tables with:

Table1:

ID_Number | Description|
a         | abc
b         | cde
c         | fgh

Table2:

ID | Title         | value
a  | phone number  | 555-1234
a  | address       | somewhere USA
a  | mobile_number | 867-5309
b  | phone number  | ...
b  | address       | ...
b  | mobile_number | ...

I want to create a new table with all data that belong to each ID_number that exists in Table 2.

with the query I'm able to list all.

select Table1.ID_Number, Table2.ID from Table1, Table2 
where Table1_ID_number = Table2.ID))
order by ID_Number

But I would like to create a new table with:

Table3
    |ID | Description| ID_data
    a , abc, "Address_A_PhoneNumber_A_Mobile_Number_A"
    b , cde, "Address_B_PhoneNumber_B_Mobile_Number_B"

Is this possible with SQL???

Upvotes: 0

Views: 73

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

I needed to edit your question and add a third column to the second table containing values for the various types of information. Without something like this, I'm afraid your question makes no sense.

We can aggregate by ID and pivot out the address and phone numbers into a single concatenated string.

SELECT
    t1.ID_Number,
    t1.Description
    MAX(CASE WHEN t2.title = 'address'       THEN t2.value END) || '_' ||
    MAX(CASE WHEN t2.title = 'phone number'  THEN t2.value END) || '_' ||
    MAX(CASE WHEN t2.title = 'mobile_number' THEN t2.value END) AS ID_data
FROM Table1 t1
INNER JOIN Table2 t2
    ON t1.ID_Number = t2.ID
GROUP BY
    t1.ID_Number,
    t1.Description;

Upvotes: 1

Related Questions