Solved

Change decision tree to matrix (SQL Server 2005)

Posted on 2011-02-18
5
424 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

830 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