asked on

Dynamic SQL

Team -

I have a table with the below split.

First Table: RawData
First Column: ReportID
Second Column to TenthColumn: Customer Levels (Ex: Customer_Level1, Customer_Level2..., Customer_Level9)
Same goes for Organization for the next 9 columns and the next 9 columns for Products Level (9 levels)
Then I will few columns for values / calculations. i.e., ShippedOrders, BackOrders etc.,

Ex: of Raw Data attached.

My 2nd Table is a Criteria Table
First Column: Unique identifier
Second Column: ReportID
Third Column: KeyName
FourthColumn: KeyType
FifthColumn: KeyLevel
(Ex: Check the attachment for sample) Test-EE-v1.xlsx

What i need to do is..,

Create an output table with the below columns
ReportID
Organization
Customer
Product
Shipments
Backorder

How I can get this...,

Criteria table will have, lets say 2 customer numbers each with their levels and 2 org numbers with their levels and 2 products with their levels.,

From the raw data, I need to check for every organization from the criteria table & create a table like below
Ex:
OrgNumber1 | Product1 | Customer1 | Sum(Shipments) | Sum(Backorder)
OrgNumber1 | Product1 | Customer2 | Sum(Shipments) | Sum(Backorder)
OrgNumber1 | Product2 | Customer1 | Sum(Shipments) | Sum(Backorder)
OrgNumber1 | Product2 | Customer2 | Sum(Shipments) | Sum(Backorder)
OrgNumber2 | Product1 | Customer1 | Sum(Shipments) | Sum(Backorder)
OrgNumber2 | Product1 | Customer2 | Sum(Shipments) | Sum(Backorder)
OrgNumber2 | Product2 | Customer1 | Sum(Shipments) | Sum(Backorder)
OrgNumber2 | Product2 | Customer2 | Sum(Shipments) | Sum(Backorder)

Here, the orgnumber1/2 or Product / Customer needs to be searched against the 9 levels in the raw data & then populate the output.

I am totally at a loss here as i couldnt even understand of how to get the data. Please help / guide me with a sample query.

ASKER CERTIFIED SOLUTION

Jerry_Justice

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Manju

ASKER

Jerry - I agree however data format is out of my control.this is how I get the raw data from the client. I need to have some work around.

SOLUTION

Jerry_Justice

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Manju

ASKER

I've requested that this question be deleted for the following reason:

Deleting this question as the solution doesnt seem to be possible in SQL. points to be refunded.

Jerry_Justice

I provided an expert opinion that what the poster asked cannot be done as an SQL statement. I provided an alternative method to get what he needs. That IS an answer!