select multiple column into a single row

Question

Suppose I've got two tables:

Table A:

Item1Name  Item1Code  Item2Name   Item2Code
ABC         001         DEF          002

Table B:

ItemCode     ItemType
  001        Cookware
  002         Drinks

What should the select statement be to generate following result?

Item1Name  Item1Code  Item2Name   Item2Code   Item1Type    Item2Type
ABC         001         DEF          002       Cookware      Drinks

Which dbms are you using? Add a few more rows of sample data, and adjust the expected result accordingly. — jarlh, Apr 09 '18 at 08:41
You are looking for a join. Google might be helpful: https://www.w3schools.com/sql/sql_join.asp — Micha Wiedenmann, Apr 09 '18 at 08:43
try `inner join` both tables, it would be worth of you making an attempt — Ven, Apr 09 '18 at 08:48
It is generally expected that you show "some research effort" (cf. "Search, and research" on https://stackoverflow.com/help/how-to-ask). This is also an upvote criteria, you will get better results on this page. — Micha Wiedenmann, Apr 09 '18 at 08:50
How many columns in Table A? Do you have a set number of "Items"? If not then you have a bad table design. What happens when Item3Code becomes available? — luisdev, Apr 09 '18 at 12:29

score 0 · Accepted Answer · answered Apr 09 '18 at 08:48

Seems that you have an item detail table (Table B) that holds the item characteristics and another table that relates 2 items (Table A). You should start with Table A and join twice with Table B (once for each item), to get their characteristics.

SELECT
    A.Item1Name,
    A.Item1Code,
    B1.ItemType,
    A.Item2Name,
    A.Item2Code,
    B2.ItemType
FROM
    TableA A
    LEFT JOIN TableB B1 ON A.Item1Code = B1.ItemCode
    LEFT JOIN TableB B2 ON A.Item2Code = B2.ItemCode

You should check if you need an INNER or LEFT join (left will return NULL if no matching record is found).

Have you searched for duplicates on this site? – Micha Wiedenmann Apr 09 '18 at 08:51 — Micha Wiedenmann, Apr 09 '18 at 08:51

select multiple column into a single row

1 Answers1