Toad World® Forums

Report row restructuring- can TOAD make the leap?

I have a “friend” with a question…..

Hello- asking question about re-structuring rows using query on behalf of a friend that uses TDP, please excuse my first timer question. I am NOT a current user (however I do use other tools for connection to databases and scripting, data processing/analysis/statistics). I may have some future interest in TDP, but want to understand capabilities….

Situation:

Imagine a parent:child data relationship where a parent will have 1,2 or 3 children (never zero or >3)

For a source data table with a row structure on a PARENT basis with columns like
Text Parent Name, Numeric Child #1 weight, numeric Child #2 weight, numeric, child #3 weight

The goal is to generate a report on a CHILD basis, for only rows for children with nonzero weights. Parent Name, Child ID, weight. The idea is that children with nonzero weights “exist”, if their weight is zero they do not exist. (the example is fictitious, but describes the intended structure)

Questions 1) is re-organization into multiple rows feasible possible in TDP (y/n)

  1. any suggestions on approach ?…

FROM Sample data PARENT Basis (Name, #1 weight, #2 wt, #3 wt)
Jones, 67,78, 89
Smith, 55,0,0
Williams, 34,45,0

TO Desired Report from TDP on CHILD basis (Parent Name, Child ID {1,2, or 3}, that child’s weight)
Jones, 1, 67
Jones, 2, 78
Jones, 3, 89
Smith, 1, 55
Williams, 1, 34
Williams, 2, 45

I looked at other postings, I didn’t see anything that appeared to match.
This may be an unpivot, followed by select child rows with zero weights and delete?

this other post looked like a conditional related to columns not rows.

https://forums.toadworld.com/t/case-statements-in-calculated-column/50191

This looks like a helpful, friendly group so any input would be appreciated….

??thoughts??

Hi Greg,

I used a union query to query the same table multiple times but pulled back different fields. Here is what my query looked like ("AutoRange_Sheet1" is the name of my "table" from Excel. I will attach a pic of my excel sample). I hope that helps!

SELECT AutoRange_Sheet1.parent, 1 AS ChildNumber, AutoRange_Sheet1.Child1
FROM AutoRange_Sheet1 AutoRange_Sheet1
WHERE (AutoRange_Sheet1.Child1 <> 0)
UNION
SELECT AutoRange_Sheet1_1.parent, 2 AS ChildNumber, AutoRange_Sheet1_1.Child2
FROM AutoRange_Sheet1 AutoRange_Sheet1_1
WHERE (AutoRange_Sheet1_1.Child2 <> 0)
UNION
SELECT AutoRange_Sheet1_2.parent,
3 AS ChildNumber,
AutoRange_Sheet1_2.Child21
FROM AutoRange_Sheet1 AutoRange_Sheet1_2
WHERE (AutoRange_Sheet1_2.Child21 <> 0)

image

And here were my results in Toad Data Point

image

and finally here is how the query looks in the visual query builder in Toad Data Point:

Julie- thanks so much that looks great. I really appreciate your input. Thanks Greg