SQL arbitrary number of temporary columns

Asked Apr 28 '16 at 19:16

Active Apr 28 '16 at 19:22

Viewed 21 times

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)

edited Apr 28 '16 at 19:22

a_horse_with_no_name

497,550
91
775
843

asked Apr 28 '16 at 19:16

Michaela Ervin

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) – a_horse_with_no_name 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

SQL arbitrary number of temporary columns

0 Answers0