Very Computer

We have a situation where we have a master/detail relationship as
follows that we would like to report on:

MasterTable (key1, key2, data1, data2, data3)
DetailTable (key1, key2, data1, data2)

where MasterTable.data3 can be joined to DetailTable.key1 giving us n
number of DetailTable rows per 1 MasterTable row.  Example data is:

Master
S, 1, a, b, 17
S, 2, d, e, 19
S, 3, a, g, 32

and

Detail
17, 1, x
17, 2, y
32, 1, s

Here is the problem we must solve.

1)  We want to allow our users to "personalize" their report output by
deciding how to group and sort the data being reported on.  This is
relatively easy as long as all of the data they are grouping and
ordering by comes from one row of a table.  But when this is not the
case,  we need to be able to report on or select data from MasterTable
and DetailTable, effectively showing the DetailTable rows' data as
columns of MasterDetail.  In the above example, we would like to get
something like:

S, 1, a, b, x, y
S, 2, d, e, null, null
S, 3, a, g, s, null

We want to do this so that we can order by data from each table, and
more specifically, multiple rows from the DetailTable.

For example, we would like to report on MasterTable.key1, key2, data1
and data2 AND DetailTable.data1 (once for every row) and ORDER this
select by MasterTable.data1, DetailTable.data1(where DetailTable.key2 =
1), DetailTable.data1(where DetailTable.key2 = 2).

Example output would then be

S, 3, a, g, s, null
S, 1, a, b, x, y
S, 2, d, e, null, null

I've worked on a similar issue before where we wanted to report on rows
of data as if they were columns.  We wrote a little program to loop
through all of the rows of a detail type table, storing them as columns
of a work table that had one column for each possible row in the table.
In this case, however, we knew how many rows were possible.  In my case
now, we have n rows in the DetailTable.  To make it even worse, each row
has a varchar(255) column in it.  So, even if we were to try the other
method, we would be limited to about 7 varchar(255) columns in the work
table.

There are a lot of details that I have left out, but I wanted to keep it
as simple and generic as possible.  Does anyone have any ideas about how
we can solve this problem?   We would appreciate any insight you may
have.   Our latest option is to limit the users to ordering by the first
20 columns or so, thus allowing us to get more like 70 columns in a work
table.  This is still limited, but not as much so.

Thanks a lot.

Is "DetailTable.data2" used? I didn't find.

------------Query----------

Select  m.key1,
             m.key2,
             m.data1,
             m.data2,
             Max ( Case when d.key2 = 1 then d.data1 else NULL end ),
             Max ( Case when d.key2 = 2 then d.data1 else NULL end ),
             Max ( Case when d.key2 = 3 then d.data1 else NULL end ),
             ...
             Max ( Case when d.key2 = 20 then d.data1 else NULL end ),
             ...
             Max ( Case when d.key2 = 70 then d.data1 else NULL end ),
             ....
             Max( d.key2 )

From   MasterTable    m    left join    DetailTable    d    on  m.data3 =
d.key1

Group by
             m.key1,
             m.key2,
             m.data1,
             m.data2
Order by 3, 5 , 6

---------End of Query

Before Group By:

S, 1, a, b,   X1,     NULL, NULL, NULL, ...., d.key2 = 1
S, 1, a, b,  NULL,    X2,   NULL, NULL, ...., d.key2 = 2
S, 1, a, b,  NULL, NULL,    X3,   NULL, ...., d.key2 = 3

After Group By:

S, 1, a, b,     X1,      X2,      X3,   NULL, ....,                3

Is it what do you want?

Vladimir Tumanov.

--------------------------------

Quote:>We have a situation where we have a master/detail relationship as
>follows that we would like to report on:

>MasterTable (key1, key2, data1, data2, data3)
>DetailTable (key1, key2, data1, data2)

>where MasterTable.data3 can be joined to DetailTable.key1 giving us n
>number of DetailTable rows per 1 MasterTable row.  Example data is:

>Master
>S, 1, a, b, 17
>S, 2, d, e, 19
>S, 3, a, g, 32

>and

>Detail
>17, 1, x
>17, 2, y
>32, 1, s

>Here is the problem we must solve.

>1)  We want to allow our users to "personalize" their report output by
>deciding how to group and sort the data being reported on.  This is
>relatively easy as long as all of the data they are grouping and
>ordering by comes from one row of a table.  But when this is not the
>case,  we need to be able to report on or select data from MasterTable
>and DetailTable, effectively showing the DetailTable rows' data as
>columns of MasterDetail.  In the above example, we would like to get
>something like:

>S, 1, a, b, x, y
>S, 2, d, e, null, null
>S, 3, a, g, s, null

>We want to do this so that we can order by data from each table, and
>more specifically, multiple rows from the DetailTable.

>For example, we would like to report on MasterTable.key1, key2, data1
>and data2 AND DetailTable.data1 (once for every row) and ORDER this
>select by MasterTable.data1, DetailTable.data1(where DetailTable.key2 =
>1), DetailTable.data1(where DetailTable.key2 = 2).

>Example output would then be

>S, 3, a, g, s, null
>S, 1, a, b, x, y
>S, 2, d, e, null, null

>I've worked on a similar issue before where we wanted to report on rows
>of data as if they were columns.  We wrote a little program to loop
>through all of the rows of a detail type table, storing them as columns
>of a work table that had one column for each possible row in the table.
>In this case, however, we knew how many rows were possible.  In my case
>now, we have n rows in the DetailTable.  To make it even worse, each row
>has a varchar(255) column in it.  So, even if we were to try the other
>method, we would be limited to about 7 varchar(255) columns in the work
>table.

>There are a lot of details that I have left out, but I wanted to keep it
>as simple and generic as possible.  Does anyone have any ideas about how
>we can solve this problem?   We would appreciate any insight you may
>have.   Our latest option is to limit the users to ordering by the first
>20 columns or so, thus allowing us to get more like 70 columns in a work
>table.  This is still limited, but not as much so.

>Thanks a lot.