Join column on other row to the root row

Transact-SQL
1

Hi, I have stuck on this.
I have table which name 'edulist' as below
| emp_no | edu_1 |
| 12345 | Sicence |
| 12345 | Math |
| 12345 | Computer|
| 23456 | Math |
I want to apend column 'edu_1' of all row with the same key 'emp_no' as result like this:
| emp_no | edu_1 |
| 12345 | Sicence>>Math>>Computer |
| 23456 | Math |
Can you guys help me?? Thanks too much
Query:
create table edulist
(
emp_no varchar(5),
edu_1 varchar(100)
)
insert into edulist values('12345','Sicence ')
insert into edulist values('12345','Math')
insert into edulist values('12345','Computer')
insert into edulist values('23456','Math')

2

No handy inbuilt way to do that in T-SQL, I use a side effect of an XML query, here's my template:

SELECT
	  H.HeaderCol1
	, H.HeaderCol2
	, [MyValueList] = 
					STUFF(
					(
						SELECT	',' + C.StringColToList
						FROM	dbo.ChildTable AS C
						WHERE	C.MyKey = H.MyKey
						ORDER BY C.C_ID
						FOR XML PATH(''), TYPE
					).value('.', 'varchar(max)')
			        , 1, 1, '')
-- For a 2 character delimiter e.g. ", " use:	, 1, 2, '')
FROM	dbo.HeaderTable AS H
1 Like
3

This helps me lots. Thank you so much!

4

Hey can you give me a solution for this:
If the value of row is Math>>Bio>>Computer then can we split it to each column.
Ex:
| emp_no | edu_1 |
| 12345 | Sicence>>Math>>Computer |
| 23456 | Math |
Output:
| emp_no | edu_1 | edu_2 | edu_3 |
| 12345 | Sicence | Math | Computer |
| 23456 | Math |