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
5
Medium Priority
?
442 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

721 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