J'ai datatables suivantes dans un tableau. Le nombre de valeurs dans chaque ligne peut varier et le nombre de lignes peut également varier.
La table a 1 colonne avec des valeurs au format csv. Les valeurs seront toujours numériques
Data 1,2 4 5,12, 10 6,7,8,9,10 15,17
Je voudrais finir avec une table temporaire avec les éléments suivants
Data Lowest Highest 1,2 1 2 4 4 4 5,12, 10 5 12 6,7,8,9,10 6 10 15,17 15 17
Quelqu'un peut-il aider à écrire une requête sql ou une fonction pour y parvenir
Au lieu de la fonction, vous pouvez réaliser par ce
;WITH tmp AS (SELECT A.rn,split.a.value('.', 'VARCHAR(100)') AS Ssortingng FROM (SELECT Row_number() OVER(ORDER BY (SELECT NULL)) AS RN, Cast ('<M>' + Replace([data], ',', '</M><M>') + '</M>' AS XML) AS Ssortingng FROM table1) AS A CROSS apply ssortingng.nodes ('/M') AS Split(a)) SELECT X.data,Tmp.lower,Tmp.higher FROM (SELECT rn,Min(Cast(ssortingng AS INT)) AS Lower,Max(Cast(ssortingng AS INT)) AS Higher FROM tmp GROUP BY rn) Tmp JOIN (SELECT Row_number() OVER(ORDER BY (SELECT NULL)) AS RN1,data FROM table1) X ON X.rn1 = Tmp.rn
FIDDLE DEMO
La sortie serait:
Data Lower Higher 1,2 1 2 4 4 4 5,12, 10 5 12 6,7,8,9,10 6 10 15,17 15 17
Créez d'abord une fonction définie par l'user pour convertir chaque ligne de la colonne 'DATA' en une table intermédiaire comme:
/****** Object: UserDefinedFunction [dbo].[CSVToTable]******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[CSVToTable] (@InStr VARCHAR(MAX)) RETURNS @TempTab TABLE (id int not null) AS BEGIN ;-- Ensure input ends with comma SET @InStr = REPLACE(@InStr + ',', ',,', ',') DECLARE @SP INT DECLARE @VALUE VARCHAR(1000) WHILE PATINDEX('%,%', @INSTR ) <> 0 BEGIN SELECT @SP = PATINDEX('%,%',@INSTR) SELECT @VALUE = LEFT(@INSTR , @SP - 1) SELECT @INSTR = STUFF(@INSTR, 1, @SP, '') INSERT INTO @TempTab(id) VALUES (@VALUE) END RETURN END GO
La fonction est expliquée plus loin ici .
Puis, en utilisant Cross Apply
nous pouvons get la sortie désirée comme:
With CTE as ( select T.Data, Min(udf.Id) as [Lowest],Max(udf.Id) as [Highest] from Test T CROSS APPLY dbo.CSVToTable(T.Data) udf Group By Data ) Select * from CTE
Exemple de code ici …
Ce que fait une Cross Apply
est la suivante: elle applique l'expression de table appropriée à chaque ligne de la table de gauche et produit une table de résultats avec les sets de résultats unifiés.
Create table #temp1 (name varchar(100),value int ) Declare @len int Select @len=(select max(LEN(name)-LEN(replace(name,',',''))) from table) Declare @i int = 1 while (@i<=@len+1) begin insert into #temp1 select name,PARSENAME(REPLACE(name,',','.'),@i) from table t set @i = @i+1 end Select name,MIN(value) MINV,MAX(value) MAXV from #temp1 group by name
declare @Testdata table ( Data varchar(max)) insert @Testdata select '1,2' insert @Testdata select '4' insert @Testdata select '5,12, 10' insert @Testdata select '6,7,8,9,10' ;with tmp( DataItem, Data, RN1) as ( select LEFT(Data, CHARINDEX(',',Data+',')-1), STUFF(Data, 1, CHARINDEX(',',Data+','), ''), ROW_NUMBER()OVER(ORDER BY (SELECT NULL))AS RN1 from @Testdata union all select LEFT(Data, CHARINDEX(',',Data+',')-1), STUFF(Data, 1, CHARINDEX(',',Data+','), ''),RN1 from tmp where Data > '' ) Select x.data,t.Low,t.Up FROM (Select RN1,MIN(Cast(DataItem AS INT)) As Low, MAX(Cast(DataItem AS INT)) As Up FROM tmp t GROUP BY t.RN1)t JOIN (Select ROW_NUMBER()OVER(ORDER BY (SELECT NULL))AS RN,data from @Testdata)X ON X.RN = t.RN1