Solved

Change decision tree to matrix (SQL Server 2005)

Posted on 2011-02-18
5
408 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
  • 2
  • 2
5 Comments
 
LVL 40

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

912 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now