Working with Hierarchical Data in SQL

Hello,

I have a problem regarding hierarchical data in SQL. Below is a simplified version of my current data table.

Current Table
I need to goup all data with 'Group Flag' = 0 together underneither the lowest level that has 'Group Flag' = -1 so that the data looks like this.

Data as needed
How would I go about doing this using SQL? I amusing SQL Server 2008? I plan on creating a View that will then form the basis of a Crystal Reports report.

Rhys
Morgs77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
could you provide the data shown in first image as text please?
0
Morgs77Author Commented:
Here you go. Let me know if you would prefer as a file attachment.

Child,Master,Group Flag
10001000,,-1
10001001,,-1
10001002,,-1
20001000,10001000,-1
20001001,10001001,-1
20001002,10001001,-1
20001003,10001002,-1
30001000,20001000,0
30001001,20001000,0
30001002,20001001,0
30001003,20001002,0
30001004,20001002,0
30001005,20001003,0
30001006,20001003,0
30001007,30001000,0
30001008,30001000,0
30001009,30001001,0
30001010,30001002,0
30001011,30001002,0
30001012,30001003,0
30001013,30001004,0
30001014,30001005,0
30001015,30001006,0
30001016,30001006,0
0
Chris LuttrellSenior Database ArchitectCommented:
I think you are looking for something like this
--CREATE TABLE SampleData (Child INT ,Master INT,Group_Flag INT);
--INSERT INTO SampleData (Child,Master,Group_Flag)
--VALUES 
--(10001000, NULL,-1),
--(10001001, NULL,-1),
--(10001002, NULL,-1),
--(20001000,10001000,-1),
--(20001001,10001001,-1),
--(20001002,10001001,-1),
--(20001003,10001002,-1),
--(30001000,20001000,0),
--(30001001,20001000,0),
--(30001002,20001001,0),
--(30001003,20001002,0),
--(30001004,20001002,0),
--(30001005,20001003,0),
--(30001006,20001003,0),
--(30001007,30001000,0),
--(30001008,30001000,0),
--(30001009,30001001,0),
--(30001010,30001002,0),
--(30001011,30001002,0),
--(30001012,30001003,0),
--(30001013,30001004,0),
--(30001014,30001005,0),
--(30001015,30001006,0),
--(30001016,30001006,0);

--SELECT *
--FROM dbo.SampleData;

;WITH cteSample AS 
(
SELECT *
FROM dbo.SampleData SD
WHERE SD.Group_Flag = -1
UNION ALL
SELECT SD.*
FROM cteSample S
INNER JOIN dbo.SampleData SD ON S.Child = SD.Master
WHERE SD.Group_Flag = 0
)
SELECT *
FROM cteSample

Open in new window

It produced this output
 CTE Example Output
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Morgs77Author Commented:
I think this might be heading in the right direction, but the output I need is as follows.

Child,Master,Group Flag
10001000,,-1
10001001,,-1
10001002,,-1
20001000,10001000,-1
20001001,10001001,-1
20001002,10001001,-1
20001003,10001002,-1
30001000,20001000,0
30001001,20001000,0
30001002,20001001,0
30001003,20001002,0
30001004,20001002,0
30001005,20001003,0
30001006,20001003,0
30001007,20001000,0
30001008,20001000,0
30001009,20001000,0
30001010,20001001,0
30001011,20001001,0
30001012,20001002,0
30001013,20001002,0
30001014,20001003,0
30001015,20001003,0
30001016,20001003,0

It has me stumped.
0
Chris LuttrellSenior Database ArchitectCommented:
Ok, I added a value to keep track of the level of recursion in the CTE then an extra column and special condition to keep up with the "Master" value you want to display (the first level 0 Group Flag value it looks like).
Then I added an order by to make sure they come out in that level order which looks like what you are after
;WITH cteSample AS 
(
SELECT  SD.Child,
        SD.Master,
        SD.Master DisplayMaster,
        SD.Group_Flag,
		0 Level
FROM dbo.SampleData SD
WHERE SD.Group_Flag = -1
UNION ALL
SELECT  SD.Child,
        SD.Master,
		CASE S.Level WHEN 0 THEN S.Child ELSE S.DisplayMaster END AS DisplayMaster,
        SD.Group_Flag,
		S.Level + 1
FROM cteSample S
INNER JOIN dbo.SampleData SD ON S.Child = SD.Master
WHERE SD.Group_Flag = 0
)
SELECT   cteSample.Child,
        cteSample.DisplayMaster Master,
        cteSample.Group_Flag
FROM cteSample
ORDER BY cteSample.Level, cteSample.Master, cteSample.Child

Open in new window

Child       Master      Group_Flag
----------- ----------- -----------
10001000    NULL        -1
10001001    NULL        -1
10001002    NULL        -1
20001000    10001000    -1
20001001    10001001    -1
20001002    10001001    -1
20001003    10001002    -1
30001000    20001000    0
30001001    20001000    0
30001002    20001001    0
30001003    20001002    0
30001004    20001002    0
30001005    20001003    0
30001006    20001003    0
30001007    20001000    0
30001008    20001000    0
30001009    20001000    0
30001010    20001001    0
30001011    20001001    0
30001012    20001002    0
30001013    20001002    0
30001014    20001003    0
30001015    20001003    0
30001016    20001003    0

(24 row(s) affected)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
No need for attachment, comma separated is perfect - thanks. (tip: if the sample is larger just use a code block).

Here is a query that matches your expected results I think - using brute force:
**Query 1**:

    SELECT DISTINCT
            M.[Child]
          , CASE WHEN C.[Group Flag] is not null then M.[Master] else C2.[Master] END as Master
          , ISNULL(C.[Group Flag],c2.[Group Flag]) as [Group Flag]
    
          --, M.[Master] AS x
          --, C2.[Master] AS y
          --, c2.[Group Flag] as z
    FROM DataTable AS M
    LEFT JOIN DataTable AS C ON c.master = m.child
    LEFT JOIN DataTable AS C2 ON m.master = C2.child

**[Results][2]**:
    
    |    CHILD |   MASTER | GROUP FLAG |
    |----------|----------|------------|
    | 10001000 |   (null) |         -1 |
    | 10001001 |   (null) |         -1 |
    | 10001002 |   (null) |         -1 |
    | 20001000 | 10001000 |          0 |
    | 20001001 | 10001001 |          0 |
    | 20001002 | 10001001 |          0 |
    | 20001003 | 10001002 |          0 |
    | 30001000 | 20001000 |          0 |
    | 30001001 | 20001000 |          0 |
    | 30001002 | 20001001 |          0 |
    | 30001003 | 20001002 |          0 |
    | 30001004 | 20001002 |          0 |
    | 30001005 | 20001003 |          0 |
    | 30001006 | 20001003 |          0 |
    | 30001007 | 20001000 |          0 |
    | 30001008 | 20001000 |          0 |
    | 30001009 | 20001000 |          0 |
    | 30001010 | 20001001 |          0 |
    | 30001011 | 20001001 |          0 |
    | 30001012 | 20001002 |          0 |
    | 30001013 | 20001002 |          0 |
    | 30001014 | 20001003 |          0 |
    | 30001015 | 20001003 |          0 |
    | 30001016 | 20001003 |          0 |

==================

    CREATE TABLE DataTable
    	([Child] int, [Master] int, [Group Flag] int)
    ;
    	
    INSERT INTO DataTable
    	([Child], [Master], [Group Flag])
    VALUES
    	(10001000, NULL, -1),
    	(10001001, NULL, -1),
    	(10001002, NULL, -1),
    	(20001000, 10001000, -1),
    	(20001001, 10001001, -1),
    	(20001002, 10001001, -1),
    	(20001003, 10001002, -1),
    	(30001000, 20001000, 0),
    	(30001001, 20001000, 0),
    	(30001002, 20001001, 0),
    	(30001003, 20001002, 0),
    	(30001004, 20001002, 0),
    	(30001005, 20001003, 0),
    	(30001006, 20001003, 0),
    	(30001007, 30001000, 0),
    	(30001008, 30001000, 0),
    	(30001009, 30001001, 0),
    	(30001010, 30001002, 0),
    	(30001011, 30001002, 0),
    	(30001012, 30001003, 0),
    	(30001013, 30001004, 0),
    	(30001014, 30001005, 0),
    	(30001015, 30001006, 0),
    	(30001016, 30001006, 0)
    ;

Open in new window

0
PortletPaulfreelancerCommented:
Mmmm, perhaps not, the group flag column is different... darn it
0
Morgs77Author Commented:
CGLuttrell, it looks like you have the solution but I am having trouble getting it to work with my actual data.

Using the actual table and field names I have the following:

;WITH cteSample AS 
(
SELECT  vw_JobCosting_WIPMasterSub.SJob,
        vw_JobCosting_WIPMasterSub.MJob,
        vw_JobCosting_WIPMasterSub.MJob DisplayMJob,
        vw_JobCosting_WIPMasterSub.GroupF,
		0 Level
FROM vw_JobCosting_WIPMasterSub
WHERE vw_JobCosting_WIPMasterSub.GroupF = 'True'
UNION ALL
SELECT  vw_JobCosting_WIPMasterSub.SJob,
        vw_JobCosting_WIPMasterSub.MJob,
		CASE S.Level WHEN 0 THEN vw_JobCosting_WIPMasterSub.SJob ELSE S.DisplayMJob END AS DisplayMJob,
        vw_JobCosting_WIPMasterSub.GroupF,
		S.Level + 1
FROM cteSample S
INNER JOIN vw_JobCosting_WIPMasterSub ON S.SJob =vw_JobCosting_WIPMasterSub.MJob
WHERE vw_JobCosting_WIPMasterSub.GroupF = 'False'
)
SELECT   cteSample.SJob,
        cteSample.DisplayMJob MJob,
        cteSample.GroupF
FROM cteSample
ORDER BY cteSample.Level, cteSample.MJob, cteSample.SJob

Open in new window


There are a couple of problems. The code runs but I get:

Sjob,Mjob,GroupF
10001046,NULL,TRUE
10001047,NULL,TRUE
10001082,NULL,TRUE
20001021,10001046,TRUE
20001010,10001047,TRUE
30001616,30001616,FALSE
30001617,30001617,FALSE
30001618,30001618,FALSE
30001619,30001619,FALSE
30001620,30001620,FALSE
30001621,30001621,FALSE
30001622,30001622,FALSE
30001623,30001623,FALSE
30001624,30001624,FALSE
30001625,30001625,FALSE
30001626,30001626,FALSE
30001627,30001627,FALSE
30001628,30001628,FALSE
30001629,30001629,FALSE
30001630,30001630,FALSE
30001631,30001631,FALSE
30001632,30001632,FALSE
30001633,30001633,FALSE
30001634,30001634,FALSE
30001635,30001635,FALSE
30001636,30001636,FALSE

Where, in this case, all the SJobs starting with 3000 should have a corresponding MJob of either 20001010 or 20001021.

SJobs are child and MJob parent.

I also get the following error message when I try to save:

"Incorrect syntax near ';'.

I also get the following the first time I try executing the SQL:

Error Message
0
PortletPaulfreelancerCommented:
one small correction, line 13:
;WITH cteSample AS 
(
SELECT  vw_JobCosting_WIPMasterSub.SJob,
        vw_JobCosting_WIPMasterSub.MJob,
        vw_JobCosting_WIPMasterSub.MJob DisplayMJob,
        vw_JobCosting_WIPMasterSub.GroupF,
		0 Level
FROM vw_JobCosting_WIPMasterSub
WHERE vw_JobCosting_WIPMasterSub.GroupF = -1 --'True'
UNION ALL
SELECT  vw_JobCosting_WIPMasterSub.SJob,
        vw_JobCosting_WIPMasterSub.MJob,
		CASE S.Level WHEN 0 THEN S.SJob ELSE S.DisplayMJob END AS DisplayMJob,
        vw_JobCosting_WIPMasterSub.GroupF,
		S.Level + 1
FROM cteSample S
INNER JOIN vw_JobCosting_WIPMasterSub ON S.SJob =vw_JobCosting_WIPMasterSub.MJob
WHERE vw_JobCosting_WIPMasterSub.GroupF = 0 --'False'
)
SELECT   cteSample.SJob,
        cteSample.DisplayMJob MJob,
        cteSample.GroupF
FROM cteSample
ORDER BY cteSample.Level, cteSample.MJob, cteSample.SJob
;

Open in new window

|     SJOB |     MJOB | GROUPF |
-----------|----------|--------|
| 10001000 |   (null) |   TRUE |
| 10001001 |   (null) |   TRUE |
| 10001002 |   (null) |   TRUE |
| 20001000 | 10001000 |   TRUE |
| 20001001 | 10001001 |   TRUE |
| 20001002 | 10001001 |   TRUE |
| 20001003 | 10001002 |   TRUE |
| 30001000 | 20001000 |  FALSE |
| 30001001 | 20001000 |  FALSE |
| 30001002 | 20001001 |  FALSE |
| 30001003 | 20001002 |  FALSE |
| 30001004 | 20001002 |  FALSE |
| 30001005 | 20001003 |  FALSE |
| 30001006 | 20001003 |  FALSE |
| 30001007 | 20001000 |  FALSE |
| 30001008 | 20001000 |  FALSE |
| 30001009 | 20001000 |  FALSE |
| 30001010 | 20001001 |  FALSE |
| 30001011 | 20001001 |  FALSE |
| 30001012 | 20001002 |  FALSE |
| 30001013 | 20001002 |  FALSE |
| 30001014 | 20001003 |  FALSE |
| 30001015 | 20001003 |  FALSE |
| 30001016 | 20001003 |  FALSE |

http://sqlfiddle.com/#!3/d562a/2

Open in new window

0
Morgs77Author Commented:
Thank you. Now gives exactly the output I am after. I still get the error messages though and can not save. Any suggestions in that regard? Thanks for the help so far.
0
PortletPaulfreelancerCommented:
remove all semi-colons and try again
it might not like

;with

although MS SQL Server normally does expect it
0
Morgs77Author Commented:
With semi-colons removed I get this error when I try to execute.

Execute Error
And this when I try to save.

Save Error
0
PortletPaulfreelancerCommented:
I have no idea, what are you using exactly? (product name and version)

as you can see here: http://sqlfiddle.com/#!3/d562a/2
the query with the semi colons is valid (click the Run SQL button to test)

perhaps try this:
a.
leave the semi-colon in front of WITH
remove the last semi-colon

b.
leave the semi-colon in front of WITH
remove the last semi-colon
use GO as the last line

c.
remove the semi-colon in front of WITH
remove the last semi-colon
use GO as the last line

d.
remove the semi-colon in front of WITH
have the last line as a semi-colon

e.
no further options I can think of
0
Morgs77Author Commented:
It is strange that it will execute correctly but give error messages and prevent from saving.

I tried the options you suggested without success. Details of what I am using are below:

Software Version
0
PortletPaulfreelancerCommented:
sorry, I simply do not know why a valid query would fail to save.
0
Morgs77Author Commented:
I suggest it is to do with the 'Unable to parse query text.' error which appears the first time it is executed after a change. I included a screen shot earlier. It then executes fine though so I have no idea what the problem is.
0
Chris LuttrellSenior Database ArchitectCommented:
Are you trying to use this code snippet inside another view or function or what?  That is what that last error message is saying.  
And as Paul asked, what tool are you using to run this query?  Those error message boxes are not from Management Studio, or any that I have ever seen, sometimes the tool can add constraints to what you can do, i.e. Access or Excel queries can not do everything that you can do in a query window inside SSMS.

Show us your code again with "the semi-colons removed"  your other error message looks like something else might have got removed by mistake.

Thanks Paul for catching his first error in tranlating from my test code to his real tables and columns.
0
Morgs77Author Commented:
I am using the code in an SQL View to create a dataset that I will later use in Crystal Reports. I am running it in Microsoft SQL Server Management Studio. See screen shot below.

Screen Shot
Below is the code again with "the semi-colons removed":

WITH cteSample AS 
(
SELECT  vw_JobCosting_WIPMasterSub.SJob,
        vw_JobCosting_WIPMasterSub.MJob,
        vw_JobCosting_WIPMasterSub.MJob DisplayMJob,
        vw_JobCosting_WIPMasterSub.GroupF,
		0 Level
FROM vw_JobCosting_WIPMasterSub
WHERE vw_JobCosting_WIPMasterSub.GroupF = 'True'
UNION ALL
SELECT  vw_JobCosting_WIPMasterSub.SJob,
        vw_JobCosting_WIPMasterSub.MJob,
		CASE S.Level WHEN 0 THEN S.SJob ELSE S.DisplayMJob END AS DisplayMJob,
        vw_JobCosting_WIPMasterSub.GroupF,
		S.Level + 1
FROM cteSample S
INNER JOIN vw_JobCosting_WIPMasterSub ON S.SJob =vw_JobCosting_WIPMasterSub.MJob
WHERE vw_JobCosting_WIPMasterSub.GroupF = 'False'
)
SELECT   cteSample.SJob,
        cteSample.DisplayMJob MJob,
        cteSample.GroupF
FROM cteSample
ORDER BY cteSample.Level, cteSample.MJob, cteSample.SJob

Open in new window


Thanks for sticking with this to help me out.
0
Morgs77Author Commented:
I have tried saving the code as a query and it saves fine. It also executes fine as a query without any error messages. It is only when I try saving as an SQL View that I get the "Unable to parse query text" error and error on trying to save. Is there any reason why this code would cause an error in an SQL View?
0
PortletPaulfreelancerCommented:
does a view of the same name already exist?

please paste all of the code you are trying to save that fails, without edits
(& with the create view ...)
0
Morgs77Author Commented:
This is all the code. A direct copy and paste. See screen shot a few posts back to see exactly how I am going about it. There is no create view statement as I am creating and saving a View in SQL Server Management Studio which I will later connect to from Crystal Reports.

; WITH cteSample AS (SELECT     vw_JobCosting_WIPMasterSub.SJob, vw_JobCosting_WIPMasterSub.MJob, vw_JobCosting_WIPMasterSub.MJob DisplayMJob, 
                                                                       vw_JobCosting_WIPMasterSub.GroupF, 0 LEVEL
                                                FROM         vw_JobCosting_WIPMasterSub
                                                WHERE     vw_JobCosting_WIPMasterSub.GroupF = 'True'
                                                UNION ALL
                                                SELECT     vw_JobCosting_WIPMasterSub.SJob, vw_JobCosting_WIPMasterSub.MJob, 
                                                                      CASE S. LEVEL WHEN 0 THEN S.SJob ELSE S.DisplayMJob END AS DisplayMJob, vw_JobCosting_WIPMasterSub.GroupF, S. LEVEL + 1
                                                FROM         cteSample S INNER JOIN
                                                                      vw_JobCosting_WIPMasterSub ON S.SJob = vw_JobCosting_WIPMasterSub.MJob
                                                WHERE     vw_JobCosting_WIPMasterSub.GroupF = 'False')
    SELECT     cteSample.SJob, cteSample.DisplayMJob MJob, cteSample.GroupF
     FROM         cteSample
     ORDER BY cteSample. LEVEL, cteSample.MJob, cteSample.SJob

Open in new window


I hope this makes sense.
0
PortletPaulfreelancerCommented:
try this, I have replaced 'LEVEL' with 'LVL', it's possible the parser didn't like using LEVEL

other than that I cannot see anything and the syntax checks-out fine.
;WITH
        cteSample
        AS (
                        SELECT
                                vw_JobCosting_WIPMasterSub.SJob
                              , vw_JobCosting_WIPMasterSub.MJob
                              , vw_JobCosting_WIPMasterSub.MJob DisplayMJob
                              , vw_JobCosting_WIPMasterSub.GroupF
                              , 0 AS LVL
                        FROM vw_JobCosting_WIPMasterSub
                        WHERE vw_JobCosting_WIPMasterSub.GroupF = 'True'
                        UNION ALL
                                SELECT
                                        vw_JobCosting_WIPMasterSub.SJob
                                      , vw_JobCosting_WIPMasterSub.MJob
                                      , CASE S.LVL
                                                WHEN 0 THEN S.SJob
                                                ELSE S.DisplayMJob
                                                END AS DisplayMJob
                                      , vw_JobCosting_WIPMasterSub.GroupF
                                      , S.LVL + 1
                                FROM cteSample S
                                INNER JOIN vw_JobCosting_WIPMasterSub
                                        ON S.SJob = vw_JobCosting_WIPMasterSub.MJob
                                WHERE vw_JobCosting_WIPMasterSub.GroupF = 'False'
                )
SELECT
        cteSample.SJob
      , cteSample.DisplayMJob MJob
      , cteSample.GroupF
FROM cteSample
ORDER BY
        cteSample.LVL
      , cteSample.MJob
      , cteSample.SJob

Open in new window

0
Morgs77Author Commented:
I did a direct copy and past and no change with the new code I am afraid.

This is a tough one. Particularly frustrating as it executes fine after I Ok the error.
0
PortletPaulfreelancerCommented:
ok, I'm not exactly sure how you are going about creating the view, this is how  would do it:
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'dbo.View_Hierarchy'))
DROP VIEW dbo.View_Hierarchy
;

CREATE VIEW dbo.View_Hierarchy
AS
WITH
        cteSample
        AS (
                        SELECT
                                vw_JobCosting_WIPMasterSub.SJob
                              , vw_JobCosting_WIPMasterSub.MJob
                              , vw_JobCosting_WIPMasterSub.MJob DisplayMJob
                              , vw_JobCosting_WIPMasterSub.GroupF
                              , 0 AS LVL
                        FROM vw_JobCosting_WIPMasterSub
                        WHERE vw_JobCosting_WIPMasterSub.GroupF = 'True'
                        UNION ALL
                                SELECT
                                        vw_JobCosting_WIPMasterSub.SJob
                                      , vw_JobCosting_WIPMasterSub.MJob
                                      , CASE S.LVL
                                                WHEN 0 THEN S.SJob
                                                ELSE S.DisplayMJob
                                                END AS DisplayMJob
                                      , vw_JobCosting_WIPMasterSub.GroupF
                                      , S.LVL + 1
                                FROM cteSample S
                                INNER JOIN vw_JobCosting_WIPMasterSub
                                        ON S.SJob = vw_JobCosting_WIPMasterSub.MJob
                                WHERE vw_JobCosting_WIPMasterSub.GroupF = 'False'
                )
SELECT
        cteSample.SJob
      , cteSample.DisplayMJob MJob
      , cteSample.GroupF
FROM cteSample
;

Open in new window

Note there is no semi-colon in front of the CTE here (because it's a view) and also there is a small snippet above the CREATE VIEW to drop the view if it already exists.

Also note it isn't good practice to use an ORDER BY in a view, this is because you might want a different sort when you come to use it, and therefore the embedded sort is wasted effort. Oh, and the view name I used is arbitrary - you can change it to suit your preference. Hopefully this will solve your issue.

see: http://sqlfiddle.com/#!3/6f54d/1
0
Anthony PerkinsCommented:
The author keeps mentioning "saving" the VIEW, which since you do not "save" VIEWs, makes me think that they are using the infamous Designer as opposed to the Query window.
0
Morgs77Author Commented:
I have been using Designer. I have now opened a query windao by clicking the "New Query" button and have directly pasted the latest code above in. When I execute I get the following in the message window:

Msg 111, Level 15, State 1, Line 5
'CREATE VIEW' must be the first statement in a query batch.
Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
0
PortletPaulfreelancerCommented:
there must be a semi-colon after the IF

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'dbo.View_Hierarchy'))
DROP VIEW dbo.View_Hierarchy
;

Then Create View will be the first statement in the query batch.
As you can see at the sqlfiddle address, the code does create a view.
0
Morgs77Author Commented:
I have included a screen shot of the Query Window below with code exactly as you provided earlier including a semi-colon after the IF.

Still no luck.

Screen Shot
0
Morgs77Author Commented:
Success! I finally have it working with the following in the Query window:

CREATE VIEW vw_JobCosting_Grouping AS
WITH
    cteSample
    AS (
                    SELECT
                            vw_JobCosting_WIPMasterSub.SJob
                          , vw_JobCosting_WIPMasterSub.MJob
                          , vw_JobCosting_WIPMasterSub.MJob DisplayMJob
                          , vw_JobCosting_WIPMasterSub.GroupF
                          , 0 AS LVL
                    FROM vw_JobCosting_WIPMasterSub
                    WHERE vw_JobCosting_WIPMasterSub.GroupF = 'True'
                    UNION ALL
                            SELECT
                                    vw_JobCosting_WIPMasterSub.SJob
                                  , vw_JobCosting_WIPMasterSub.MJob
                                  , CASE S.LVL
                                            WHEN 0 THEN S.SJob
                                            ELSE S.DisplayMJob
                                            END AS DisplayMJob
                                  , vw_JobCosting_WIPMasterSub.GroupF
                                  , S.LVL + 1
                            FROM cteSample S
                            INNER JOIN vw_JobCosting_WIPMasterSub
                                    ON S.SJob = vw_JobCosting_WIPMasterSub.MJob
                            WHERE vw_JobCosting_WIPMasterSub.GroupF = 'False'
            )
SELECT
    cteSample.SJob
  , cteSample.DisplayMJob MJob
  , cteSample.GroupF
FROM cteSample

Open in new window


I am not completely sure what is different but it works so I am happy. Thanks for all your work and patience.
0
PortletPaulfreelancerCommented:
:) Hooray!

don't forget that you need to apply an order by when you use this view

select
*
from vw_JobCosting_Grouping
ORDER BY lvl, MJob, SJob
0
Anthony PerkinsCommented:
I have included a screen shot of the Query Window
Unless you want to lose more hair, learn to write code in the Query window. The Designer is crippled at best.
0
Chris LuttrellSenior Database ArchitectCommented:
Glad to see you got things working.  I have not been able to get back to the conversation till now.  
I agree with acperkins, learn to use the Query Window and avoid the designer, it will cause you grief and all the newest SQL features may not work in it.
0
Morgs77Author Commented:
Fantastic help on this one. Thanks.

I now need help to add another column with the parent of the output MJob. Should this be posted as a new question?
0
PortletPaulfreelancerCommented:
>>Should this be posted as a new question?
absolutely
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.