One of the way to achieve that is to use
CTE[
^]. See:
DECLARE @tmp TABLE(USERID VARCHAR(30), Vendors VARCHAR(10), dobyr INT, [login] VARCHAR(10), [source] VARCHAR(50))
INSERT INTO @tmp (USERID, Vendors, dobyr, [login], [source])
VALUES('10bta', 'yes', 1976, 'yes', 'google_hope'),
('25cwd', 'yes', 1986, 'yes', 'google_hln_1045'),
('45tyj', 'no', 1990, 'no', 'bing_hln_4345'),
('645io', 'no', 1960, 'no', 'bing')
;WITH CTE AS
(
SELECT 1 AS LoopNo, USERID, Vendors, dobyr,YEAR(GETDATE()) - dobyr AS Age, [login], LEFT([source], CHARINDEX('_', [source])-1) AS src, RIGHT([source], LEN([source])-CHARINDEX('_', [source])) AS Remainder
FROM @tmp
WHERE CHARINDEX('_', [source]) > 0
UNION ALL
SELECT LoopNo + 1 AS LoopNo, USERID, Vendors, dobyr, Age, [login], LEFT(Remainder, CHARINDEX('_', Remainder)-1) AS src, RIGHT(Remainder, LEN(Remainder)-CHARINDEX('_', Remainder)) AS Remainder
FROM CTE
WHERE CHARINDEX('_', Remainder) > 0
UNION ALL
SELECT LoopNo + 1 AS LoopNo, USERID, Vendors, dobyr, Age, [login], Remainder AS src, NULL AS Remainder
FROM CTE
WHERE CHARINDEX('_', Remainder) = 0
)
SELECT USERID, Vendors, dobyr, [login], Age, [1], [2], [3]
FROM
(
SELECT LoopNo, USERID, Vendors, dobyr, [login], Age, src
FROM CTE
) DT
PIVOT(MAX(src) FOR LoopNo IN([1], [2], [3])) PVT
Result:
USERID Vendors dobyr login Age 1 2 3
10bta yes 1976 yes 44 google hope NULL
25cwd yes 1986 yes 34 google hln 1045
45tyj no 1990 no 30 bing hln 4345
For further details, please see:
SQL Server CTE Basics - Simple Talk[
^]
SQL Server Common Table Expressions (CTE)[
^]