How to find hierarchy of email thread from this table.

Question

0.00/5 (No votes)

See more:

ProductId | ParentId | Levels
1174           0        1174
311           1174      311, 1174
1186          311       1186, 311, 1174
448           1186      448, 1186, 311, 1174
3365          448       3365, 448, 1186, 311, 1174

What I have tried:

;With Parents(ProductId, ParentId, Levels)
As(
  Select ProductId, ParentId, Levels
  From Products
  Where ParentId = 0 
  Union All
  Select p.ProductId, p.ParentId, p.Levels
  From Products p
  Join Parents cte On cte.ProductId = p.ParentId
)
Select *
From Parents

FROM EmailThread
CROSS APPLY STRING_SPLIT(EmailThreadChildHashcode, ',')
)

SELECT * FROM @spli

Posted 28-Mar-19 19:29pm

Member 14156756

Updated 8-Apr-19 2:57am

v4

Add a Solution

Comments

Santosh kumar Pithani 29-Mar-19 5:13am

Hello,please give one sample records and expected output then its very east to understand.

Member 14156756 1-Apr-19 0:07am

sample data are already declare in question and Example of Out put like :-

DocId ParentDocId TheradGroupId ThreadLevel
13376 0 1 0
13377 13348 1 1
13379 13378 1 2
13380 13348 1 2
13371 13456 1 3
13372 13457 2 1

RedDk 29-Mar-19 15:06pm

After a very quick look at what you've done I can say that the "output" (four columns) is never going to be anything more than a two column container ... so this last

inscription is fantasy.

See your "INSERT INTO @spli" statement where you've specifically quantized the number of return columns as two, [DocId] and [Dataa] ...

And look up CROSS APPLY STRING_SPLIT in the BOL ... something about that quotidian placeholder using the apostrpphe doesn't look right. Perhaps its the CP editor. I don't know.

Member 14156756 1-Apr-19 1:39am

i don't how to get this result from this table. I thought, firstly i split the parent and child hashcode from emailthreadchild column and after that i will compare the hashcode with emailthreadparenthashcode and display there loaddocumentid. that's why i use split_string function. may be i m wrong. but i try to get this result so..

RedDk 1-Apr-19 14:53pm

Ok,

I reconstructed a scenario using the data by CREATE TABLE and since I'm using SQLServer 2008, I had to investigate the problem of the SELECT within the SELECT using a UTF which I got from here:

https://stackoverflow.com/questions/10914576/t-sql-split-string

BUT, since STRING_SPLIT was invented and included in SQL Server 2016 any talk about it's magical properties is lost on me. I suggest running through this stackoverflow thread and gleening what you can about slicing up that 2, 4, and 6 substring [EmailThreadChildHashcode] using one of the response post solutions ... as I have begun to do ... but I just haven't got the time right now to get that actual SUB SELECT running (hint) ...

RedDk 2-Apr-19 16:20pm

More reply:

I see in the data posted no output data matching any of the [DocId] so I have to assume [LoadDocumentId] is not the same thing?

Member 14156756 3-Apr-19 0:06am

Yes right, it is just for example.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Answer 1 · 2019-04-03T10:00:00

So, anyway ... check this against the posted "What I have tried:"

CREATE TABLE [tbl_SS_stringsplit](
	[EmailThreadId] int,
		[LoadId] int,
			[LoadDocumentId] int,
				[EmailThreadParentHashcode] NVARCHAR(256),
					[EmailThreadChildHashcode] NVARCHAR (1100),
						[EmailThreadChildHashcodeCount] int
						)

I'm doing this because I can copy directly from the code dump you've provided and get a good purchase on the data ...

BULK INSERT  [tbl_SS_stringsplit]
	FROM 'C:\Users\SS\tabledata.txt'
	
SELECT [EmailThreadId]
      ,[LoadId]
      ,[LoadDocumentId]
      ,[EmailThreadParentHashcode]
      ,[EmailThreadChildHashcode]
      ,[EmailThreadChildHashcodeCount]
  FROM [tbl_SS_stringsplit]

The data comes back alright and I checked that by performing this query:

SELECT [EmailThreadId]
      ,[LoadId]
      ,[LoadDocumentId]
      ,[EmailThreadParentHashcode]
      ,[EmailThreadChildHashcode]
      ,[EmailThreadChildHashcodeCount]
  FROM [tbl_SS_stringsplit]
GO

To get this (abbreviated):

EmailThreadId	LoadId	LoadDocumentId	EmailThreadParentHashcode	EmailThreadChildHashcode	EmailThreadChildHashcodeCount
1	18	13388	cc4daed8ce8114a66cb914eb2ce9fa75	NULL	0
2	18	13389	afbfe22c38834f0a8838d8bcf75dbab9	afbfe22c38834f0a8838d8bcf75dbab9bb7f39e1781ce52a587f8380fafde71d	1
3	18	13391	41a9539ab798aba22485831373780218	41a9539ab798aba22485831373780218a0fe9b1f2fcfa25380c8abcac1cebb9a	1
4	18	13392	a52053d8f4b2d2302b79a0ebef617be4	a52053d8f4b2d2302b79a0ebef617be46ee25b4c32f9b2992743cd71184aeb34	1
5	18	13393	684b8eb73d3faa9ca3e18de471c6880a	NULL	0
6	18	13394	5af02be12a4f8e626e15262c6633a431	1b1b00ac5c9ffd1dbeaf10877e86822e6abdf163046bc08c2f31b737542aeb6,5af02be12a4f8e626e15262c6633a4311b1b00ac5c9ffd1dbeaf10877e86822e	2
.
.
.
20	19	13423	d658fffb9710b42f81aadef034c2ced4	d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe	1
21	19	13424	d658fffb9710b42f81aadef034c2ced4	eac8cbfb1aa25bc4c451efaec6c5f86d74e4fde19c5f57970480fc3924558506,53b9e269f5a156f19f9297a4c467be64eac8cbfb1aa25bc4c451efaec6c5f86d,cebdb5365b0f764b28ebea3325aa3d8953b9e269f5a156f19f9297a4c467be64,eaee8869bc2d9f22123796f521f4e81ecebdb5365b0f764b28ebea3325aa3d89,7100fbd68ecdfba259f19a52ccefecfeeaee8869bc2d9f22123796f521f4e81e,d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe	6

So, here's the alteration to the FUNCTION you've posted"

Declare @spli table 
(
 DocId INT,
 Dataa NVARCHAR(1000)
)

INSERT INTO @spli (DocId,Dataa) (
--SELECT LoadDocumentId,value
SELECT [LoadDocumentId], [keyword]
FROM [tbl_SS_stringsplit] [a]
    --CROSS APPLY STRING_SPLIT(EmailThreadChildHashcode, ',')
        CROSS APPLY STRING_SPLIT([a].[EmailThreadChildHashcode], ',') [b]
	)

SELECT * FROM @spli

... in order to get ANYTHING back, by the way ... which looks like this (in its entirety):

DocId	Dataa
13388	NULL
13389	afbfe22c38834f0a8838d8bcf75dbab9bb7f39e1781ce52a587f8380fafde71d
13391	41a9539ab798aba22485831373780218a0fe9b1f2fcfa25380c8abcac1cebb9a
13392	a52053d8f4b2d2302b79a0ebef617be46ee25b4c32f9b2992743cd71184aeb34
13393	NULL
13394	1b1b00ac5c9ffd1dbeaf10877e86822e6abdf163046bc08c2f31b737542aeb6
13394	5af02be12a4f8e626e15262c6633a4311b1b00ac5c9ffd1dbeaf10877e86822e
13395	NULL
13398	NULL
13399	d63fc2c2c869f796088831c1257ce0464fbcc7891545f2d6fbe31d83405a5e06
13399	595d80afcdc22eca596e9953a8ca164dd63fc2c2c869f796088831c1257ce046
13361	NULL
13362	aebbb64f3c3293c78188a8f0e316e8338fbf58a7b42e1875287c0c0b31b004d4
13362	8fbf58a7b42e1875287c0c0b31b004d4aebbb64f3c3293c78188a8f0e316e833
13363	NULL
13416	NULL
13417	d658fffb9710b42f81aadef034c2ced450ce8fc0fd6461364168fce14c51a3c3
13418	515c78fada1b30f404b82e30e7d4025fcf0075c15f1ee19485c1f82e948964a1
13419	cebdb5365b0f764b28ebea3325aa3d8953b9e269f5a156f19f9297a4c467be64
13419	eaee8869bc2d9f22123796f521f4e81ecebdb5365b0f764b28ebea3325aa3d89
13419	7100fbd68ecdfba259f19a52ccefecfeeaee8869bc2d9f22123796f521f4e81e
13419	d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe
13420	NULL
13421	eaee8869bc2d9f22123796f521f4e81ecebdb5365b0f764b28ebea3325aa3d89
13421	7100fbd68ecdfba259f19a52ccefecfeeaee8869bc2d9f22123796f521f4e81e
13421	d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe
13422	NULL
13423	d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe
13424	eac8cbfb1aa25bc4c451efaec6c5f86d74e4fde19c5f57970480fc3924558506
13424	53b9e269f5a156f19f9297a4c467be64eac8cbfb1aa25bc4c451efaec6c5f86d
13424	cebdb5365b0f764b28ebea3325aa3d8953b9e269f5a156f19f9297a4c467be64
13424	eaee8869bc2d9f22123796f521f4e81ecebdb5365b0f764b28ebea3325aa3d89
13424	7100fbd68ecdfba259f19a52ccefecfeeaee8869bc2d9f22123796f521f4e81e
13424	d658fffb9710b42f81aadef034c2ced47100fbd68ecdfba259f19a52ccefecfe

Can you see where I'm going with this now after I've modified the code you posted?
This is NOT the solution you're asking for but I'm thinking using STRING_SPLIT was the hang-up you were hoping someone could help you with.