Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Change decision tree to matrix (SQL Server 2005)

Posted on 2011-02-18
Medium Priority
442 Views
Hello -

I'm trying to figure out how to transform data that's in decision tree form to be presented as a matrix:

TreeID      Node      Question                                     Result      NextNode            EndPoint
1              1              Is it blue?                                     YES               2                     {null}
1              1              Is it blue?                                      NO              {null}               You lose.
1              2              Is bigger than a breadbox?      YES               3                      {null}
1              2              Is bigger than a breadbox?      NO               {null}                 You lose.
1              3              Is it a car?                             YES               {null}                  You win.
1              3              Is it a car?                              NO              {null}                   You lose.

To look like this:
Is it blue?      Is bigger than a breadbox?      Is it a car?      Endpoint
Y                    Y                                         Y                      You win.
Y                    Y                                         N                      You lose.
Y                    N                                         N/A                   You lose.
N                   N/A                                         N/A                You lose.

I started looking at Analysis Services - but wasn't sure if it would help.

0
Question by:kwieckii
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 41

Expert Comment

ID: 34929837
Do you have only 3 questions every time?
0

LVL 15

Expert Comment

ID: 34931804
hi

select case question when 'is it blue ?' then result else null end  "is it blue ",
case question when ' Is bigger than a breadbox?' then result else null end  " Is bigger than a breadbox?",
...
...
from table

0

Author Comment

ID: 34932905
Hello -
I don't think I can use the case statement here because I need to follow the path to find each set of answers for a given result.

There are different questions - and different answers.  I do have a list (table) of all the questions and a list of all the answers (results) - but I never know the number of questions it would take to get to the result (or end of the path).

I don't know if it's clear from my example - but the logic would look more like this:

Q 1:  Is it blue?
A2: Yes - Is it a car?
Result:  You win.
A2:  No  -You lose.
Answer 1:   No --- Result:  You lose.

0

LVL 15

Accepted Solution

Aaron Shilo earned 2000 total points
ID: 34933045
ok
so use an SP in the SP
use a cursor and retrive the data then
use IF @col = 'is it ...'
IF @col2 = 'is it Bi...'
IF @col3 = 'is it abc..'    THEN doit.
else dontdoit

0

Author Comment

ID: 34960662
I won't always know the name of the answer - so I'm trying not to use it as a literal.  Without thinking of the decisions for each - is there a way to convert a rowname as a column name?

Kind of like doing a pivot table without summarizing data:

Using the data above - is it blue had been a row value... I'd like to use that as a column name.

Thanks in advance... sorry for the delayed response.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
###### Suggested Courses
Course of the Month8 days, 9 hours left to enroll