SQL HELP

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nitin - 01 Mar 2007 10:23 GMT

I have two tables in sql database as follows.
I need to generate the output given at the end.

=======
Table1
=======

PlanName PlanType    PlanDescription    Sequence

PHome    Promo    Home        1
PPro    Promo    Pro        2
PProPlus    Promo    ProPlus        3

=======
Table2
=======

PlanName    AttributeName    AttributeText    AttributeValue

PHome        Download    700 Kbps 1
PHome        Upload    128 Kbps    2
PHome        Fee    $59.99        3
PHome        Term    15 Months     4
PHome        Hardware    15 Months    5
PHome        Installation    $499.98        6
PHome        Rebate    - $100        7
PHome        Cost    $399.98        8
PPro        Download    1 Mbps        9
PPro        Upload    200 Kbps     10
PPro        Fee    $69.99        11
PPro        Term    15 Months    12
PPro        Hardware    15 Months    13
PPro        Installation    $499.98        14
PPro        Rebate    - $100        15
PPro        Cost    $399.98        16
PProPlus    Download    1.5 Mbps 17
PProPlus    Upload    200 Kbps    18
PProPlus    Fee    $79.99         19
PProPlus    Term    15 Months      20
PProPlus     Hardware    15 Months    21
PProPlus    Installation    $499.98        22
PProPlus    Rebate    - $100        23
PProPlus    Cost    $399.98        24

==============
Output Required
==============

700 Kbps    1 Mbps    1.5 Mbps
128 Kbps    200 Kbps    200 Kbps
$59.99    $69.99    $79.99
15 Months    15 Months    15 Months
15 Months    15 Months    15 Months
$499.98    $499.98    $499.98
- $100    - $100    - $100
$399.98    $399.98    $399.98

I am new to sql, I tried to build a query but couldn't get the result.
Need help

Thanks in advance.

Nitin

Reply to this Message

Duane Hookom - 01 Mar 2007 12:26 GMT

Create a crosstab query with SQL view like:
TRANSFORM First(AttributeText) AS FirstOfAttributeText
SELECT ([AttributeValue]-1) Mod 8 AS RowHead
FROM Table2 INNER JOIN Table1 ON Table2.PlanName = Table1.PlanName
GROUP BY ([AttributeValue]-1) Mod 8
PIVOT Table1.Sequence;

Signature

Duane Hookom
Microsoft Access MVP

> I have two tables in sql database as follows.
> I need to generate the output given at the end.
[quoted text clipped - 59 lines]
>
> Nitin

Reply to this Message