Populating grid with a tricky SQL 
Author Message
 Populating grid with a tricky SQL

Hi All

I need help with an SQL to perform the following

Say I a have a table called ItemTable like this

       Item      ColNo     Value
       -------------------------
       Item1       1         10
       Item1       2         20
       Item1       3         30
       Item2       1         10
       Item2       3         20
       Item3       2         30

I need to fill a grid so it looks like

    ----------------------------------------    
    Item1  |   10     |     20    |    30
    Item2  |   10     |            |    20
    Item3  |           |     30    |                            
    -----------------------------------------

I can achieve this using

        TRANSFORM First(Value) AS [The Value]
        SELECT Item
        FROM ItemTable
        GROUP BY Item
        PIVOT ColNo

But.....

       ....this query produces a recordset that cannot by updated and I
need the user          
 to be able to edit the 'cells'.

I am trying to use expresions with sub queries but cant seem to get it to
work

e.g.
        SELECT Col1:(SELECT Value From ItemTable WHERE ColNo = 1),
               Col2:(SELECT Value From ItemTable WHERE ColNo = 2),
               Col3:(SELECT Value From ItemTable WHERE ColNo = 3)
        FROM ItemTable

Any help would be very much appreciated

Thanks in advance

        Andrew James
--



Sun, 27 Aug 2000 03:00:00 GMT  
 Populating grid with a tricky SQL

        A transform-pivot satatement creates a recordset which values are
calculated,
        said, the fields do not exist in a table. So you can not use a grid to
modify it.

        I the sample data set occurs that the data is only transposed but not
sumarized,
        buit, you know, when you have something like

        item1, 1, 10
        item,1 ,20
        itenm,2,10,

        you'll end with some like
                1
        item1 | 40
        So, there is no underlying table => can't be bounded.
        You may want to create a temporary table and put there your
trasnform-Pivot result,
        then copy back your data in your orig table.

HTH
Raul Coba Poot
Merida, Yucatan Mexico



Quote:
> Hi All

> I need help with an SQL to perform the following

> Say I a have a table called ItemTable like this

>        Item      ColNo     Value
>        -------------------------
>        Item1       1         10
>        Item1       2         20
>        Item1       3         30
>        Item2       1         10
>        Item2       3         20
>        Item3       2         30

> I need to fill a grid so it looks like

>     ----------------------------------------    
>     Item1  |   10     |     20    |    30
>     Item2  |   10     |            |    20
>     Item3  |           |     30    |                              
>     -----------------------------------------

> I can achieve this using

>         TRANSFORM First(Value) AS [The Value]
>         SELECT Item
>         FROM ItemTable
>         GROUP BY Item
>         PIVOT ColNo

> But.....

>        ....this query produces a recordset that cannot by updated and I
> need the user              
>  to be able to edit the 'cells'.

> I am trying to use expresions with sub queries but cant seem to get it to
> work

> e.g.
>         SELECT Col1:(SELECT Value From ItemTable WHERE ColNo = 1),
>           Col2:(SELECT Value From ItemTable WHERE ColNo = 2),
>                Col3:(SELECT Value From ItemTable WHERE ColNo = 3)
>         FROM ItemTable

> Any help would be very much appreciated

> Thanks in advance

>    Andrew James
> --




Mon, 28 Aug 2000 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Populating grid with tricky SQL

2. Populating a DB-grid wit rows from large SQL server tables

3. Populating grid from SQL Server ?

4. Populate a grid with results from SQL query

5. Tricky One...a real Tricky one!!!!!

6. Tricky Access/SQL Crosstab Question / (Long)

7. Tricky Access/SQL Crosstab Question / (Long)

8. tricky sql question

9. A Tricky SQL Statement...............

10. How to populate a grid with data

11. Populating a Grid from Access

12. Populating MS Flex Grid w/Data

 

 
Powered by phpBB® Forum Software