learning_new Ответов: 4

Коса столбец, который имеет подчеркивание для нескольких колонок

I have a table called clients and I'm trying to split the value  which contains underscore that is one column into multiple columns and I'm also trying to create a column that calculate the age of the person. Here is how the table looks like

USERID    Vendors   (dobyr)     login    source
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


The goal is to have that look like this:
USERID    Vendors   (dobyr)   login    source1  Source2    source3     Age
10bta      yes       1976       yes     google    hope                 44
25cwd      yes       1986       yes     google    hln         1045     34
45tyj      no        1992       no      bing      hln         4345     28
645io      no        1960       no      bing                           30




Что я уже пробовал:

Итак, пока мне удалось выяснить, как вычислить возраст, но не разобрать исходный столбец:
select *, datepart(year, CURRENT_TIMESTAMP) - dobyr as Age
FROM clint

j snooze

Возможно, вы захотите проверить это
https://raresql.com/2013/01/10/sql-server-split-string-into-rows-based-on-multiple-delimiters/

MadMyche

Эта статья основана на устаревшей версии SQL Server - с выпуском 2016 года
они добавили это вместе с Split_String функция.

4 Ответов

Рейтинг:
20

Maciej Los

Один из способов достичь этого-использовать КТОС[^]. Видеть:

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
(
	--initial part
	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
	--recursive part
	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 
	--ORDER BY USERID, LoopNo
) DT
PIVOT(MAX(src) FOR LoopNo IN([1], [2], [3])) PVT


Результат:
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


Для получения более подробной информации, пожалуйста, смотрите:
Основы SQL Server CTE - простой разговор[^]
Общие табличные выражения SQL Server (CTE)[^]

Рейтинг:
2

MadMyche

SQL Server имеет функцию, которая может разбивать очерченный текст: Split_String().

Я бы рекомендовал вам прочитать о том, как его использовать, и пройти через образцы, которые есть у MS, не будет точно соответствовать тому, что вы хотите, но вы сможете работать с ним в качестве начала
STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^]

Рейтинг:
1

NEGIN_K 

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_hl_445'),
('645io', 'no', 1960, 'no', 'bing')
 
SELECT USERID, Vendors, dobyr, [login],[source],
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN [source] ELSE
	 CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))>=2 THEN SUBSTRING([source],1,(CHARINDEX('_',[source])-1)) 
      END 
	END AS source1,
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN '' ELSE
	 CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=2 THEN SUBSTRING([source],CHARINDEX('_',[source])+1,LEN([source])-CHARINDEX('_',[source])) ELSE
	  CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=3 THEN SUBSTRING([source],CHARINDEX('_',[source])+1,CHARINDEX('_',[source],CHARINDEX('_',[source])+1)-CHARINDEX('_',[source])-1) ELSE ''
      END  END
	END AS source2,
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN '' ELSE
		CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=2 THEN '' ELSE
		CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=3 THEN SUBSTRING([source],CHARINDEX('_',[source],CHARINDEX('_',[source])+1)+1,LEN([source])-CHARINDEX('_',[source],CHARINDEX('_',[source])+1)) ELSE ''
	  END  END
	END AS source3,
		Year(GETDATE())-dobyr AS Age
FROM @tmp

Рейтинг:
0

NEGIN_K 

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_hl_445'),
('645io', 'no', 1960, 'no', 'bing')
  
 
SELECT USERID, Vendors, dobyr, [login],[source],
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN [source] ELSE
	 CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))>=2 THEN SUBSTRING([source],1,(CHARINDEX('_',[source])-1)) 
      END 
	END AS source1,
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN '' ELSE
	 CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=2 THEN SUBSTRING([source],CHARINDEX('_',[source])+1,LEN([source])-CHARINDEX('_',[source])) ELSE
	  CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=3 THEN SUBSTRING([source],CHARINDEX('_',[source])+1,CHARINDEX('_',[source],CHARINDEX('_',[source])+1)-CHARINDEX('_',[source])-1) ELSE ''
      END  END
	END AS source2,
	CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=1 THEN '' ELSE
		CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=2 THEN '' ELSE
		CASE WHEN (SELECT COUNT(*) FROM string_split([source],'_'))=3 THEN SUBSTRING([source],CHARINDEX('_',[source],CHARINDEX('_',[source])+1)+1,LEN([source])-CHARINDEX('_',[source],CHARINDEX('_',[source])+1)) ELSE ''
	  END  END
	END AS source3,
		Year(GETDATE())-dobyr AS Age
FROM @tmp