SQL How to group two separate tables, to get this output

Question

ok just to paint a picture of what i am trying to achieve.

I have an XML file:

<root>
    <item id="test1" level="1" />
    <item id="test2" level="1">
        <item id="test3" level="2" />
        <item id="test4" level="2" >
            <item id="test5" level="3">
                <item id="test6" level="4" />
            </item>
        </item>
        <item id="test7" level=2" />
    </item>
</root>

I read the XML fine, and store the data into a SQL table like this:

Lets call this tableA

ID     | ParentID  | level
---------------------------
test1      NULL         1
test2      NULL         1
test3      test2        2
test4      test2        2
test5      test4        3
test6      test5        4
test7      test2        2

Now table B looks like this:

    GUID                                |  ID
    -----------------------------------------------
   c567207d-5317-4d0e-b24d-5ae3f7fa5691    test1
   4567207d-4317-4d6e-b25d-7ae3f7fa5691    test3
   a7b94a42-fb00-4011-bd5a-4b48e6e578c5    test1
   fa7989d7-1708-4a90-9bf6-c91f6cef6952    test2
   8a7989d7-5608-5690-9bf6-591f6ce56852    test7
   gta7b94a42-fb00-4011-bd5a-4b48e6e578    test6

I want to write a select statement, that would give me a result like this using tableA and TableB from above:

EDIT: Basically think of it as a file path, I want to find the path to the ID,

so basically for ID: test6

path would be test2 -> test4 -> test5 -> test6

    GUID                                |  ID  |   ID_Level_1  | ID_Level_2 | ID_Level_3 | ID_Level_4    
    ---------------------------------------------------------------------------------------------------------
   c567207d-5317-4d0e-b24d-5ae3f7fa5691    test1       test1
   4567207d-4317-4d6e-b25d-7ae3f7fa5691    test3       test2     test3  
   a7b94a42-fb00-4011-bd5a-4b48e6e578c5    test1       test1     
   fa7989d7-1708-4a90-9bf6-c91f6cef6952    test2       test2
   8a7989d7-5608-5690-9bf6-591f6ce56852    test7       test2     test7
   gta7b94a42-fb00-4011-bd5a-4b48e6e578    test6       test2     test4           test5          test6

How do I achieve this result, what is the SQL CALL required to get the result above, using Table A and Table B?

I understand the basic logic of what you want but I'm wondering why the row with test3 as ID brings all the other IDs? Also, why are test3 and test4 together on level 2? — Gidil, Oct 07 '13 at 19:09
the way the Levels, in the final result work as is, it has to start of at the top most parent for that ID and than work its way down to the lowest level — Prakash Chennupati, Oct 07 '13 at 19:12
OK, I understand the logic now. This is much more complicated than I thought at the beginning. — Gidil, Oct 07 '13 at 19:14
Do you need to pivot on ID_Level_1, ID_Level2,....? or can you have Guid |ID|Level| LevelID for example x | test2|2|test3,test4? — gh9, Oct 07 '13 at 20:54
@gh9 not sure exactly what you mean by that? can you elaborate... — Prakash Chennupati, Oct 07 '13 at 23:37
I have edited my final result, @Gidil I think this was what you were originally thinking off aswell, sorry for the confusion i was confused on my spec as well. i edited my final result. I think this compiles with maybe what you were talking about before. please let me know — Prakash Chennupati, Oct 08 '13 at 13:29

gh9 · Accepted Answer · 2013-10-08T15:16:09.837

1

http://sqlfiddle.com/#!6/d41d8/8850

The trick was using a Recursive CTE. Just cut and paste the following code and it will create 2 temp tables IT WILL NOT DROP THEM. And tweek the recursive cte and select statement to get what you want. Let me know if this isnt what you need.

CREATE TABLE #tempA (ID VARCHAR(20) ,PARENTID VARCHAR(20),[LEVEL] INT)

INSERT INTO #TEMPa
VALUES( 'TEST1',NULL,1)

INSERT INTO #TEMPa
VALUES( 'TEST2',NULL,1)

INSERT INTO #TEMPa
VALUES ('TEST3','TEST2',2)

INSERT INTO #TEMPa
VALUES( 'TEST4','TEST2',2)

INSERT INTO #TEMPa
VALUES ('TEST5','TEST4',3)

INSERT INTO #TEMPa
VALUES ('TEST6','TEST5',4)

INSERT INTO #TEMPa
VALUES ('TEST7','TEST2',2)

create table #tableb(guid varchar(50), id varchar(50));
insert into #tableb values ('c567207d-5317-4d0e-b24d-5ae3f7fa5691',    'test1');
insert into #tableb values ('4567207d-4317-4d6e-b25d-7ae3f7fa5691',    'test3');
insert into #tableb values ('a7b94a42-fb00-4011-bd5a-4b48e6e578c5',    'test1');
insert into #tableb values ('fa7989d7-1708-4a90-9bf6-c91f6cef6952',    'test2');
insert into #tableb values ('8a7989d7-5608-5690-9bf6-591f6ce56852',    'test7');
insert into #tableb values ('gta7b94a42-fb00-4011-bd5a-4b48e6e578',   'test6');

;WITH coolRecursionCTE as
(
SELECT a.id,a.parentid,a.id as TargetElement, 1 AS level,convert(varchar(max),a.parentid) as [path]
FROM #tempA AS a
UNION ALL
SELECT a.ID,a.parentid,c.TargetElement,c.Level+1,convert(varchar(max),a.parentid) +'->' + c.[path]   as [path]
FROM #tempA AS a 
INNER JOIN  coolRecursionCTE AS c ON c.parentid = a.id
where a.parentid is not null
)



SELECT [targetelement], [path] + '->'+[targetelement] FROM coolRecursionCTE AS c
INNER JOIN
(
select targetElement as t , max([level]) as maxLevel from coolRecursionCTE
group by TargetElement) AS E on c.TargetElement = e.t and c.[level] = e.maxLevel

edited Oct 08 '13 at 15:16

answered Oct 08 '13 at 14:45

gh9

10,169
10
63
96

I think your base functionality is really close to the solution, but can you tweak it some how to get the data to display as my final result, I need them in separate columns instead of the -> and also i need to have its matching GUID from tableB as well, so basically I want final result to be exactly like the one i posted above – Prakash Chennupati Oct 08 '13 at 15:07
Also, there are a few inconsistencies with your result, for instance there is an error for test6 and test5, the hierarchy is not correct. – Prakash Chennupati Oct 08 '13 at 15:09
@prakash when i asked if you needed to pivot you said the format x->y->z was acceptable. That is no longer the case? – gh9 Oct 08 '13 at 15:17
What I meant was, instead of a single Column_1 that displays everything, I want it to display as, Column_1, Column_2, Column_3, Column_4. and for example test5 would have values in Column_1 = test2, Column_2 = test4, Column_3 = test5 – Prakash Chennupati Oct 08 '13 at 15:20
Yes, that is called pivoting. Do you really need to pivot or can you have the application run a split ? – gh9 Oct 08 '13 at 15:52
yes i really need to pivot cause there is additional queries i need to run against those columns, so i need the data to be seperated. – Prakash Chennupati Oct 08 '13 at 15:57
why? is that an issue to have pivot columns? – Prakash Chennupati Oct 08 '13 at 16:27
ok here is another idea instead of using pivot, is it possible to use SQL subtrings and split the path at '->' and create 4 seperate columns? would that work? – Prakash Chennupati Oct 08 '13 at 17:31
No. The only way to get multiple columns is by pivioting. or doing some crazy dynamic sql – gh9 Oct 08 '13 at 18:03
i found a solution to this without using pivots from here: http://stackoverflow.com/questions/17040890/split-one-long-comma-string-into-multi-column-new-table just incase you wanted to know for future reference :) – Prakash Chennupati Oct 08 '13 at 18:56

score -1 · Answer 2 · edited May 23 '17 at 12:29

-1

Without adjusting the stored procedure sample, it might give you a good jump-start to what you are looking to do. Here is the original answer I provided on another question.

It handles a cycling approach of getting recursive hierarchical querying and populating a result table for all records not already processed. You would have to adjust it, and I would suggest at each level you need to go deeper, add an "alter table add " for the next column you would be running it for. You might even need to get into dynamic-SQL as your column names will keep going out.

edited May 23 '17 at 12:29

Community

1
1

answered Oct 08 '13 at 13:53

DRapp

47,638
12
72
142

I dont think Dynamic tables are required as you can only have max of 4 levels, based on the the structure already defined, but I would like to hide the column names if there is no data represent for that column – Prakash Chennupati Oct 08 '13 at 13:55

SQL How to group two separate tables, to get this output

2 Answers2