如何在SQL Server中将列的值拆分为动态列？ [英] How to split value of column to dynamic column in SQL Server?
问题描述
我想创建一个函数，将 TBL_Sku 中的一列（ sku （ TBL_Sku2 ）
I want to create function to split the value of a column (sku) in TBL_Sku to any column (Sku1, 2, 3, 4, ...) in another table (TBL_Sku2)
列
在 TBL_SkU 中的$ c> sku c $ c> 1-Acer Aspire 3811TZG 1GB DDR3-1066 PC8500内存模块，SKU：
1GBDDR3-1066-21，2-Acer Aspire 3811TZG 2GB DDR3-1066 PC8500内存模块，SKU：
2GBDDR3-1066-21，& 3-Acer Aspire 3811TZG 4GB DDR3-1066 PC8500内存模块，SKU：
4GBDDR3-1066-414，&
Row1 in column Des : 1-Acer Aspire 3811TZG 1GB DDR3-1066 PC8500 Memory Module,SKU:
1GBDDR3-1066-21,&2-Acer Aspire 3811TZG 2GB DDR3-1066 PC8500 Memory Module,SKU:
2GBDDR3-1066-21,&3-Acer Aspire 3811TZG 4GB DDR3-1066 PC8500 Memory Module,SKU:
4GBDDR3-1066-414,&
和列Des： 1-SKU：512MBDDR2-533-1038中的第2行，Sony VAIO PCG-1J1L 512MB DDR2-533 PC4200存储器模块， code>
and row 2 in column Des : 1-SKU: 512MBDDR2-533-1038,Sony VAIO PCG-1J1L 512MB DDR2-533 PC4200 Memory Module,&
和第3列Des： 1-MSI S271 1GB DDR2-533 PC4200内存模块，SKU：1GBDDR2-533 -1068，& 2-MSI S271 512MB DDR2-533 PC4200内存模块，SKU：512MBDDR2-533-1033&
：
TBL_Sku2 ：
id sku1 sku2 sku3 sku4 sku5
--- ----------- ----------------- ------------------- ----------------- ---------
1 1GBDDR3-1066-21 2GBDDR3-1066-21 4GBDDR3-1066-414 Null Null
2 512MBDDR2-533-1038 null null null null
3 1GBDDR2-533-1068 512MBDDR2-533-1033 Null Null Null
我想要专业的功能。稍后发送给我我的代码。在子字符串和charindex中使用
I want to professional function. Send for you my code later. Use in substring and charindex
sp：
ALTER PROCEDURE [dbo].[SP_SpilitSKU]
(@ID int ,@SKU nvarchar(Max) )
AS
BEGIN
Declare @YourTable table (ID int,SKU varchar(max))
Insert Into @YourTable values
(@ID,@SKU)
;with cte as (
Select A.ID
,RN = Row_Number() over (Partition By ID Order by RetSeq)
,SKU = LTrim(RTrim(Replace(RetVal,'SKU:','')))
From @YourTable A
Cross Apply (
Select RetSeq = Row_Number() over (Order By (Select null))
,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
From (Select x = Cast('<x>'+ replace((Select A.SKU as [*] For XML Path('')),',','</x><x>')+'</x>' as xml).query('.')) as A
Cross Apply x.nodes('x') AS B(i)
) B
Where RetVal like 'SKU:%'
)
INSERT INTO [dbo].[TBL_Sku]
(id
,[SKU1]
,[SKU2]
,[SKU3]
,[SKU4]
,[SKU5]
,[SKU6]
)
Select ID,[1] as SKU1,[2] as SKU2,[3] as SKU3,[4] as SKU4,[5] as SKU5,[6] as SKU6
From cte
Pivot (max(SKU) For [RN] in ([1],[2],[3],[4],[5],[6]) ) p
SP：
ALTER PROCEDURE [dbo].[SP_CreateTableSku4Column]
AS
BEGIN
declare @DesOrginal as nvarchar(max);
Declare @i as int = 0;
Declare @Count as int = (select COUNT(des) from TBL_Product_Test);
while (@i<= @Count)
Begin
WITH TBL_SKUTemp AS
(
SELECT
ROW_NUMBER() OVER( ORDER BY (SELECT null)) AS 'RowNumber',[des]
FROM TBL_Product_Test
)
SELECT @DesOrginal = [des]
FROM TBL_SKUTemp
WHERE RowNumber = @i+1;
set @i = @i+1;
EXECUTE [dbo].[SP_SpilitSKU] @i,@DesOrginal ;
END
END
计数列在表中= 15563
count column Des in the Table = 15563
在TBL_Sku = 8449中影响
affect in the TBL_Sku = 8449
(1 row(s) affected)
8449
15563
des = 1-Samsung NP940X5JI (NP940X5J-S01US) 4GB DDR3L-1600 PC12800 1.35V Memory Module,SKU: 4GBDDR3L-1600-9,&
(1 row(s) affected)
(0 row(s) affected)
8450
15563
des = 1-SKU: 512MBDDR2-533-1038,Sony VAIO PCG-1J1L 512MB DDR2-533 PC4200 Memory Module,&
other Method :
$ b declare @DesOrginal as nvarchar（max）;
声明@i为int = 0;
将@Count声明为int =（从TBL_Product_Test中选择COUNT（des））;
while（@ i <= @Count）
begin
WITH TBL_SKUTemp AS
（
SELECT
ROW_NUMBER（）OVER（ORDER BY ）AS'RowNumber'，[des]
FROM TBL_Product_Test
）
SELECT @DesOrginal = [des]
FROM TBL_SKUTemp
WHERE RowNumber = @i +1;
set @i = @ i + 1;
DECLARE @str VARCHAR（8000）= @ DesOrginal，
@col_list VARCHAR（1000）=''，
@sql NVARCHAR（max）
$ b b SET @col_list =（SELECT Concat（'，'，Quotename（Concat（'sku'，Row_number（）
OVER（
ORDER BY ItemNumber））））
FROM dbo.Delimitedsplit8k @str，'，sku：'）
WHERE Item LIKE'sku：％'
FOR xml path（''））
SET @col_list = Stuff（@col_list， ''）
SET @sql ='SELECT *
FROM（SELECT Concat（''sku''，Row_number（）OVER（ORDER BY ItemNumber））rn，
（item，1，5，''''）AS item
FROM dbo.Delimitedsplit8k（@str，''，sku：''）
WHERE Item LIKE''sku：％''）a
PIVOT（Max（item）
For rn IN（'+ @col_list +'））pv'
--PRINT @sql
EXEC Sp_executesql
@sql，
N'@ str VARCHAR（8000）'，
@ str = @str
INSERT INTO [dbo] TBL_Sku6]
（[SKU1]
，[SKU2]
，[SKU3]
，[SKU4]
，[SKU5]
）SELECT *
FROM（SELECT Concat（'sku'，Row_number（）OVER（ORDER BY ItemNumber））rn，
Stuff（item，1,5，''）AS item
FROM dbo.Delimitedsplit8k @str，'，sku：'）
WHERE Item LIKE'sku：％'）a
PIVOT（Max（item）
FOR rn IN（+ @col_list +））pv
print @i
print @Count
--print @str
--print RowNumber
print'des ='+ @ DesOrginal
--PRINT @sql
----打印SKU
----打印RetVal
----打印RetSeq
------打印cte
end
TBL_Sku6：
TABLE [dbo].[TBL_Sku6](
[SKU1] [varchar](7996) NULL,
[SKU2] [varchar](7996) NULL,
[SKU3] [varchar](7996) NULL,
[SKU4] [varchar](7996) NULL,
[SKU5] [varchar](7996) NULL,
[SKU6] [varchar](7996) NULL
) ON [PRIMARY]
/ p>
Error
INSERT语句的选择列表包含的项目少于插入列表。 SELECT值的数量必须与INSERT列的数量相匹配。
The select list for the INSERT statement contains fewer items than the insert list. The number of SELECT values must match the number of INSERT columns.
推荐答案
方法
DECLARE @str VARCHAR(8000)='1-Acer Aspire 3811TZG 1GB DDR3-1066 PC8500 Memory Module,SKU: 1GBDDR3-1066-21,&2-Acer Aspire 3811TZG 2GB DDR3-1066 PC8500 Memory Module,SKU: 2GBDDR3-1066-21,&3-Acer Aspire 3811TZG 4GB DDR3-1066 PC8500 Memory Module,SKU: 4GBDDR3-1066-414,&',
@col_list VARCHAR(1000)='',
@sql NVARCHAR(max)
SET @col_list =(SELECT Concat(',', Quotename(Concat('sku', Row_number()
OVER(
ORDER BY ItemNumber))))
FROM dbo.Delimitedsplit8k(@str, ',sku:')
WHERE Item LIKE 'sku:%'
FOR xml path(''))
SET @col_list = Stuff(@col_list, 1, 1, '')
SET @sql = 'SELECT * into TBL_Sku2
FROM (SELECT Concat(''sku'',Row_number()OVER(ORDER BY ItemNumber)) rn,
Stuff(item, 1, 5, '''') AS item
FROM dbo.Delimitedsplit8k(@str, '',sku:'')
WHERE Item LIKE ''sku:%'') a
PIVOT (Max(item)
FOR rn IN (' + @col_list + ')) pv '
PRINT @sql
EXEC Sp_executesql
@sql,
N'@str VARCHAR(8000)',
@str= @str
：
Result :
+-----------------+-----------------+------------------+
| sku1 | sku2 | sku3 |
+-----------------+-----------------+------------------+
| 1GBDDR3-1066-21 | 2GBDDR3-1066-21 | 4GBDDR3-1066-414 |
+-----------------+-----------------+------------------+
考虑规范化表结构以更轻松地解析数据。有一个单独的表 sku 数字和值
Consider normalizing your table structure to parse data easier. Have a separate table for sku number and value
我已经使用分割字符串函数分割每个 sku 。
I have used split string function to split the records for each sku.
Create FUNCTION [dbo].[DelimitedSplit8K]
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover NVARCHAR(4000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
参考 http://www.sqlservercentral.com/articles/Tally+Table/72993/
这篇关于如何在SQL Server中将列的值拆分为动态列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
