将单个列值拆分为多个列
数据管理中可能会出现将包含多个值的单个列拆分为单独列的任务。例如,考虑一个具有名为“SubscriptionNumber”的列的表,该列将订阅详细信息存储在单个字段中。为了使数据更易于管理和访问,我们需要将各个值提取到多个列中。
使用交叉应用和 XML 的方法
实现此目的的一种方法是通过使用交叉应用和 XML 操作技术。假设我们有一个名为“Subscriptions”的表,其中包含“SubscriptionNumber”列。此列中的示例数据如下:
SC 5-1395-174-25P SC 1-2134-123-ABC C1-2 SC 12-5245-1247-14&P SC ABCD-2525-120
要将这些值拆分为单独的列,我们可以使用以下查询:
Declare @YourTable table (SubscriptionNumber varchar(max)) Insert Into @YourTable values ('SC 5-1395-174-25P'), ('SC 1-2134-123-ABC C1-2'), ('SC 12-5245-1247-14&P'), ('SC ABCD-2525-120') Select B.* From @YourTable A Cross Apply ( Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)'))) ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)'))) ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)'))) ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)'))) ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)'))) ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)'))) ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)'))) From (Select Cast('<x>' + replace((Select replace(replace(A.SubscriptionNumber,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A ) B
此查询使用交叉应用到为 @YourTable 表中的每一行生成一组行。在交叉应用中,XML 数据类型用于将 SubscriptionNumber 值转换为 XML 片段。然后解析 XML 片段以提取各个值并将它们分配给 Pos1 到 Pos7 列。
使用动态 SQL 的替代方法
动态 SQL 方法也可以用于将 SubscriptionNumber 列拆分为多列。这种方法需要创建一个临时表来存储分割值:
Declare @YourTable table (SubscriptionNumber varchar(max)) Insert Into @YourTable values ('SC 5-1395-174-25P'), ('SC 1-2134-123-ABC C1-2'), ('SC 12-5245-1247-14&P'), ('SC ABCD-2525-120') Declare @ColNames nvarchar(max) = '' Declare @SQL nvarchar(max) = 'CREATE TABLE #TempTable (SubscriptionNumber varchar(max), ' -- Generate column names dynamically Select @ColNames += ', Col' + CAST(Row_Number() OVER (ORDER BY (SELECT NULL)) AS nvarchar(10)) From (Select * From @YourTable) AS T Cross Join (Select * From @YourTable) AS T2 -- Append column names to SQL statement Set @SQL += @ColNames + ')' -- Execute the dynamic SQL to create the temporary table Exec (@SQL) -- Insert split values into the temporary table Insert Into #TempTable Select SubscriptionNumber, ltrim(rtrim(xDim.value('/x[1]','varchar(max)'))) , ltrim(rtrim(xDim.value('/x[2]','varchar(max)'))) , ltrim(rtrim(xDim.value('/x[3]','varchar(max)'))) , ltrim(rtrim(xDim.value('/x[4]','varchar(max)'))) , ltrim(rtrim(xDim.value('/x[5]','varchar(max)'))) , ltrim(rtrim(xDim.value('/x[6]','varchar(max)'))) , ltrim(rtrim(xDim.value('/x[7]','varchar(max)'))) From @YourTable Cross Apply ( Select Cast('<x>' + replace((Select replace(replace(SubscriptionNumber,' ','-'),'-','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml) as xDim) as A -- Select data from the temporary table Select * From #TempTable
动态 SQL 方法动态生成 SQL 语句来创建具有适当列名的临时表。然后将分割后的值插入到这个临时表中,可以根据需要从临时表中选择数据。
以上是如何在 SQL Server 中将包含多个值的单个列值拆分为多个列?的详细内容。更多信息请关注PHP中文网其他相关文章!