qry for Excel report with weekly data in a vertical column

[Janis]

tblArea
AreaID
area



tblHeadEnd
HeadEndID
AreaID
headend

tblRouter
RouterID
HeadEndID
router

tblPorts
PortID
RouterID

tblTransmitter
TransmitterID
PortID
transmitter
PortID
RouterID

tblNodes
NodeID
transmitterID


tblNodeNamesID
NodeID
ParentNodeName
date created

tblUtilization
utilID
NodeID
percentage
Date

The report shows weekly utilization data for an ISP. This structure is 1:M
in the areas to the headend. The headend has 1:m routers, each router has
1:M ports. The transmitter can have either 1:1 to nodes or 1:M . The
nodeNames table is for the purpose of being able to split a node into 2
nodes. I don't need to get into that here but I need a script to allow the
user to do it.

I need to get the weekly percent of utilization >50% in Excel format in a
vertical column. Each week I want to add a new column for each weekending.
I wonder how to do that with SQL? I plan on making an Excel template to
import the data.

There is one anomaly for the SQL query. The data only makes sense to the
user from the perspective of the router port. All of the nodes related to
one port need to be on the same row in Excel how do I do this? In other
words each row would be a node but that would make the report non-sensical to
the users.

tia,

 

[Janis]

Since the utilization data is a column perhaps this is a crosstab query?? Is
a crosstab query just when the rows are reversed into columns? If so then I
may have answered my own question.