Need a query to do this:

[LiLMsNinja]

LowerLvL HighLvL UnitName UnitID
16923 16949 Store1 0120
16949 16921 District2 11


This is a small sample of how the data for structure comes in. So, you can see how Store1 rolls up to District2 if you follow the LowerLvL and HighLvL numbers. I would like some fancy way to pull the data so that it will display more like this:

0120 Store1 11 District2

We are using T/SQL in SSMS... I thought this type of thing might be more achievable with PL/SQL but I am not really sure if it's even possible at all.

Anyone know of any real sweet tricks...

[WingedPanther]

Can you show us what your raw data looks like? I'm thinking something like:

  select min(level) LowerLvl, max(level) HighLvl, UnitName, UnitID from BLAH group by UnitName, UnitID

might work.

[LiLMsNinja]

The 'keys' reside in one table called 'Relations' and looks like:

LowerLvL HighLvL
16923 16949
16923 16921
16924 16949
16924 16921
16925 16949
16925 16921


Then the Store or District names and real IDs reside within another table called 'Hierarchy':

H_ID UnitID UnitName
16921 1 System
16923 0120 Store1
16924 0121 Store2
16925 0122 Store3
16949 11 District1


Essentially I would want to use the 'HighLvL' key from Relations to identify the UnitID and UnitName from 'Hierarchy' then put those values in the same row, where each 'lowest level' rolls up to the 'highest level'. In this case, Store is the lowest and System is the Highest.

From the two tables I would like the data to display like:
UnitID UnitName UnitId UnitName UnitID UnitName
0120 Store1 11 District1 1 System
0121 Store2 11 District1 1 System
0122 Store3 11 District1 1 System

I am wondering if something this fancy can be done completely through SSMS. Or, if it's something I will need to investigate much further and put together with C# or something?

[WingedPanther]

You can probably do it as a query, but it'll be messy

[LiLMsNinja]

I was thinking more along the lines of a temp table and stored procedure to step through and compare each record then drop it into the correct place.

[debtboy]

This should do the trick for your example,
but if you have additional Distinct HighLvL keys
(more than 2) you will require small loop to
generate additional views and additional
joins in the final query.
Also, "TOP" works for SQL Server,
"LIMIT" works for MySQL and I can't
remember the one for Oracle.

CREATE VIEW section1 AS
SELECT TOP 1 Hierarchy.H_ID,
Relations.LowerLvL,
Hierarchy.UnitID,
Hierarchy.UnitName
FROM Hierarchy LEFT OUTER JOIN Relations
ON Hierarchy.H_ID = Releations.HighLvL

CREATE VIEW section2 AS
SELECT TOP 1 Hierarchy.H_ID,
Relations.Lower_LvL,
Hierarchy.UnitID,
Hierarchy.UnitName
FROM Hierarchy LEFT OUTER JOIN Relations
ON Hierarchy.H_ID = Releations.HighLvL
WHERE Hierarchy.H_ID NOT IN 
(SELECT H_ID FROM section1)

SELECT Hierarchy.UnitID, Hierarchy.UnitName,
section2.UnitID, section2.UnitName,
section1.UnitID, section1.UnitName
FROM section1 INNER JOIN section2
ON section1.LowerLvL = section2.LowerLvL
INNER JOIN Hierarchy
ON section1.LowerLvL = Hierarchy.H_ID
ORDER BY Hierarchy.H_ID

[LiLMsNinja]

haha wow! My hero! I didn't even think of approaching it like that.. i will play with it today. Thank you.

[LiLMsNinja]

Ok, soo... i've been messing with this all day. I am soo close i can taste it...

I am getting the data to pull like:

UnitID UnitName
0120 Store1 1 System 1 System
0120 Store1 1 System 11 District1
0120 Store1 11 District1 1 System
0120 Store1 11 District1 11 District1

Row 3 is correct..

The part i failed to mention in my previous posts were the fact that everything rolls up the the 'System' level.

I think they are storing the data incorrectly.

If you had, what i like to call a 'Key Table' that defines the numeric link between the units like:

Store1 District1
Store2 District1
Store1 System
Store2 System
District1 System

I believe it should be:

Store1 District1
Store2 District1
District1 System

If the data was stored in this manner i wouldn't get the useless rows 1,2, & 4 like listed above.

[debtboy]

If you were to keep the terms store, system and district consistent
as in this example, this should work (% is wildcard).

CREATE VIEW section1 AS
SELECT H_ID, UnitID, UnitName
FROM Hierarchy
WHERE UnitName Like 'Store%'

This should give you:

H_ID UnitID UnitName
16923 0120 Store1
16924 0121 Store2
16925 0122 Store3

CREATE VIEW section2 AS
SELECT B.LowerLvL, A.H_ID, A.UnitID, A.UnitName
FROM Hierarchy A INNER JOIN Relations B
ON A.H_ID = B.HighLvL
WHERE A.UnitName Like 'District%'

I would expect the view to contain:

LowerLvL H_ID UnitID UnitName
16923 16949 11 District1
16924 16949 11 District1
16925 16949 11 District1

CREATE VIEW section3 AS
SELECT B.LowerLvL, A.H_ID, A.UnitID, A.UnitName
FROM Hierarchy A INNER JOIN Relations B
ON A.H_ID = B.HighLvL
WHERE A.UnitName Like 'System%'

I would expect the view to contain:

LowerLvL H_ID UnitID UnitName
16923 16921 1 System
16924 16921 1 System
16925 16921 1 System

Now it's just a matter of putting it together.

SELECT A.UnitID, A.UnitName,
B.UnitID, B.UnitName,
C.UnitID, C.UnitName
FROM section1 A INNER JOIN section2 B
ON A.H_ID = B.LowerLvL
INNER JOIN section3 C
ON A.H_ID = C.LowerLvL
ORDER BY A.UnitID

I would expect this output to look like this:

UnitID UnitName UnitID UnitName UnitID UnitName
0120 Store1 11 District1 1 System
0121 Store2 11 District1 1 System
0122 Store3 11 District1 1 System

Be advised, I'm doing everything from my head as I'm having
trouble with MySQL on my Linuxbox, it's being stubborn.

[LiLMsNinja]

Well the wildcard effect would work if the data actually said Store1, District1... but it doesn't. It's always different... i changed the data to make it more 'forum friendly'.