Solved

Change decision tree to matrix (SQL Server 2005)

Posted on 2011-02-18
5
437 Views
Last Modified: 2012-05-11
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.

Thanks in advance!!
0
Comment
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
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 34929837
Do you have only 3 questions every time?
0
 
LVL 15

Expert Comment

by:Aaron Shilo
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

by:kwieckii
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?
                    Answer 1:  Yes --- Q2:  Is it bigger than a breadbox?
                                                         A2: Yes - Is it a car?
                                                                          Result:  You win.
                                                         A2:  No  -You lose.
                    Answer 1:   No --- Result:  You lose.


0
 
LVL 15

Accepted Solution

by:
Aaron Shilo earned 500 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

by:kwieckii
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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question