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')
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
This helps me lots. Thank you so much!
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 |