0

I have two tables. Table 1 (tbl_transaction) holds data pertaining to a transaction. Table 2 (tbl_optional) holds arbitrary data that may or may not accompany a transactions.

example

SELECT a.name, a.type, a.amount 
FROM Transaction AS a

Table 2 can hold 0 to n items associated with that transaction.

SELECT a.id, a.name, a.type, a.amount, b.value 
FROM tbl_transaction AS a 
   JOIN tbl_optional AS b ON a.id = b.trans_id 
WHERE "some condition here"

This will obviously return a list of result 1 to n depending on how many optional items there are.

Is there a way to create temporary columns so that only 1 result is returned.

i.e.

id, name, type, amount, value(1), value(2), value(n)
Michaela Ervin
  • 702
  • 8
  • 25
  • 1
    You can use `PIVOT` to turn rows into additional columns, but you have to know how many columns you want ahead of time. – David Apr 28 '16 at 19:18
  • And you certainly can't arbitrarily vary the number of columns per record. – HLGEM Apr 28 '16 at 19:21
  • Are you OK, with a single column containing multiple values (e.g. comma separated) –  Apr 28 '16 at 19:23
  • This is one of the reasons why EAV tables are to be avoided in relational databases. They are not designed to handled unstructured data well. – HLGEM Apr 28 '16 at 19:23
  • What is the structure of tblOptional? – HLGEM Apr 28 '16 at 19:24

0 Answers0