RaqForum 28 No.
492 View  • 
     ...

Result Set with Dynamic Columns

 
SQL(179)

【Quesiton】

Table1

undefined

Table 2

undefined

I need to make output like this

undefined

So all needed I think is some simple loop with count of items from table2 and add columns with count and item number to result. I’ve tried a lot of solutions but cannot find a suitable one.

 

【Answer】

You can’t generate a result set with dynamic columns automatically in SQL. The query needs to be a dynamic one, with complicated group and aggregate operations. It’s much easier to do it in SPL:

A

1

=myDB1.query("select  t1.ID ID,t1.Prob Prob,t1.Cost   Cost,t1.Visible Visible,t2.Count Cnt,t2.Iteml    Iteml from table2 as t2 left join table1 t1 on t1.ID=t2.ID")

2

=A1.group(ID)

3

=A2.max(~.count()).("Itml"+string(~)+","+"Cnt"+string(~)).concat@c()

4

=create(ID,Prob,Cost,Visible,${A3})

5

>A2.(A4.record([ID,Prob,Cost,Visible]|~.([Iteml,Cnt]).conj()))

A1: Associate the two tables with a join operation;

A2: Group the joined-up table by ID;

A3: Dynamically generate column names: Itml1,Cnt1,Itml2,Cnt2,Itml3,Cnt3,Itml4,Cnt4;

A4: Create a two-dimensional table dynamically;

A5: Insert each group of data in A2 into A2’s table dynamically.

For this type of problem, compared with SQL’s compulsory aggregation, SPL can group data without aggregation for easier use of the detailed data.


SQL(179)
 
 •  492 View  •