Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Change decision tree to matrix (SQL Server 2005)

Posted on 2011-02-18
5
Medium Priority
?
445 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 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 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

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

877 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