0

I have a table that is structured in the following manner that we have imported from another source into SQL Server:

Tier 0 Tier 1 Tier 2 Tier 3
AA;AB;BB T1A;T1B T1A2A;T1A2B;T1B2A T1A2A3A;T1A2B3A;T1B2A3Z
AAA;CC T1A;T1C T1A2A;T1A2C;T1C2F T1A2A3A;T1A2B3A;T1C2F3G
AA;ZZ T1Z T1Z2A;T1Z2C;T1Z2F T1Z2A3A;T1Z2B3A;T1Z2F3G

This data is structured in such a way that each subsequent column is like a child of the previous one. Also, each item is divided by semi-colon.

Is there a way to get a list of the subsequent tier, based on a search. Using the above table as an example, if I want to get a list of all possible tier 1, where the tier 0 contains "AA", it will return this result:

Tier 1
T1A;T1B
T1Z

But the user could also select multiple Tier 0 items. If he would search by "AA" together with "BB" the table would then return

Tier 1
T1A;T1B

Is it possible to achieve this with a SELECT statement?

Unfortunately, stored procedures and functions are not options I have available.

We didn't have the option to restructure on import as our client has reports that use this structure, so we have to reverse engineer the data as is. While it would be possible now, we have more than a million rows. Manually setting this hierarchy would not be feasible.

Improve this question
0

1 Answer 1

Reset to default
2

Whilst it may be possible to do this, the performance of such queries will be appalling because your database cannot use any Indexes to locate the data. That means Table-Scanning your "more than a million rows", which will be terrible.

Your effectively using where conditions like this:

... WHERE ( ';' + Tier_0 + ';' ) LIKE '%;' + value + ';%' ...

It would be far, far better to reorganise your data into something that your DBMS can cope with far better, something like ...

+--------+---------+
| Parent | Child   | 
+--------+---------+
| AA     | T1A     | 
| AA     | T1B     | 
| AB     | T1A     | 
| AB     | T1B     | 
| BB     | T1A     | 
| BB     | T1B     | 
| T1A    | T1A2A   | 
| T1A    | T1A2B   | 
| T1A    | T1B2A   | 
| T1B    | T1A2A   | 
| T1B    | T1A2B   | 
| T1B    | T1B2A   | 
| T1A2A  | T1A2A3A | 
| T1A2A  | T1A2B3A |
| T1A2A  | T1B2A3Z |
| T1A2B  | T1A2A3A | 
| T1A2B  | T1A2B3A |
| T1A2B  | T1B2A3Z |
| T1B2A  | T1A2A3A | 
| T1B2A  | T1A2B3A |
| T1B2A  | T1B2A3Z |
+--------+---------+

... and so on and use a Common Table Expression (CTE) to "reassemble" the original structure, if it's required.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.