You're JOINing the table together. This will place the columns of the table on the same row. If I'm envisioning what you want correctly, it sounds like the universal table that is created during the FOR XML EXPLICIT process.

The Universal Table is created through a series of queries UNIONed together. The columns from the appropriate table will have values, the values that are supposed to come from the other tables are NULL (or an appropriate value for sorting).

Check out XML and Internet Support | Retrieving and Writing XML Data | Retrieving XML Documents Using FOR XML | Using EXPLICIT Mode

in the SQL Server BOL.

-Mike

>I have a query that I thought would return a "level" that represents which level in the table hiarchary the row came from. I use this to parse into seperate lists and eventually want to use it to load a treeview type control. But as happens, it didn't work. Each record shows all levels. I thought that the level1 would have a 1 in it for records from that table, but nulls for the rest and the same for levels2-4.
>
>Hoping someone can tell me how to accomplish this. Here is the query I'm currently using. The table hiarcy is:
>
>NoteDic (Level1)
>NoteSectionDic (Level2)
>NoteSubGrpDic (Level3)
>NoteSubGrpDtls (Level4)
>
>

>SELECT     TOP 100 PERCENT dbo.NoteDic.NoteTitleText, dbo.NoteDic.NoteID, dbo.NoteDic.NoteLevel AS Level1, dbo.NoteSectionDic.NoteSectionText,
>                      dbo.NoteSectionDic.NoteSectionDicID, dbo.NoteSectionDic.SectionOrder, dbo.NoteSectionDic.NoteLevel AS Level2,
>                      dbo.NoteSubGrpDic.NoteSubGrpDicID, dbo.NoteSubGrpDic.NoteSubGrpText, dbo.NoteSubGrpDic.NoteSubGrpOrder,
>                      dbo.NoteSubGrpDic.NoteLevel AS Level3, dbo.NoteSubGrpDtls.QuestionText, dbo.NoteSubGrpDtls.NoteSubGrpDtlsID,
>                      dbo.NoteSubGrpDtls.QuestionOrder, dbo.NoteSubGrpDtls.NoteLevel AS Level4
>FROM         dbo.NoteSubGrpDic LEFT OUTER JOIN
>                      dbo.NoteSubGrpDtls ON dbo.NoteSubGrpDic.NoteSubGrpDicID = dbo.NoteSubGrpDtls.NoteSubGrpDicID RIGHT OUTER JOIN
>                      dbo.NoteSectionDic ON dbo.NoteSubGrpDic.NoteSectionDicID = dbo.NoteSectionDic.NoteSectionDicID RIGHT OUTER JOIN
>                      dbo.NoteDic ON dbo.NoteSectionDic.NoteID = dbo.NoteDic.NoteID
>where dbo.noteDic.NoteID=@tiNoteID
>ORDER BY Level1, dbo.NoteDic.NoteTitleText, Level2, dbo.NoteSectionDic.SectionOrder, Level3, dbo.NoteSubGrpDic.NoteSubGrpOrder, Level4,
>                      dbo.NoteSubGrpDtls.QuestionOrder
>

>
>Thanks
>Kirk