ms sql 由 & 拆分基于数据作为列名 [英] ms sql split by & based on data as a column name
问题描述
我在结果"字段的数据集"表中有 4 行，它由与号 (&) 分隔.我想根据字段名称将结果"字段划分为多个字段.如何在 SSIS 或 SQL 中执行此操作?字段名是高亮的.如果没有 .net 脚本也能实现，那就太好了.如果您注意到，字段名称是混乱的.
I have 4 rows in 'dataset' table in 'result' field and it is seperated by ampersand (&). I want to divide 'result' field into multiple fields based on field name. How do I do this in SSIS or SQL? Fieldname are higligtened. It would be nice it can be doable without .net script. If you notice, field names are jumbled.
结果(字段名)
deptid=1 &名字=din &姓氏=kal &中间名=kum
deptid=1 & firstname=din & lastname=kal & middlename=kum
名字=raj &姓=puli &中间名=kumar &deptid=2
firstname=raj & lastname=puli & middlename=kumar & deptid=2
名字=pavan &姓=gud &中间名=kumarrao
firstname=pavan & lastname=gud & middlename=kumarrao
deptid=7 &名字=sha &姓=hank
deptid=7 & firstname=sha & lastname=hank
输出
**deptid** **firstname** **lastname** **middlename**
1 din kal kum
2 raj puli kumar
pavan gud kumarrao
7 sha hank
推荐答案
CROSS APPLY 表格与某种形式的 拆分字符串 函数.使用这些结果创建动态 SQL 命令并执行.将这些结果保留到一个新表中，并DROP 保留旧表.存储这样的数据是不行.
CROSS APPLY the table with some form of split string function. Use these results to create a Dynamic SQL command and execute. Persist these results to a new table and DROP the old one. Storing data like that is not okay.
随机分割字符串:
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE name = 'SplitString'
AND type = 'TF' )
BEGIN
--DROP FUNCTION dbo.SplitString;
EXEC( 'CREATE FUNCTION dbo.SplitString() RETURNS @t TABLE ( x BIT ) AS BEGIN RETURN; END' );
END;
GO
ALTER FUNCTION dbo.SplitString
(
@String VARCHAR( MAX ),
@Delimiter VARCHAR( 16 )
) RETURNS @Values TABLE
(
SplitString VARCHAR( MAX )
) AS BEGIN
/*
dbo.SplitString
@Param:
String, the string to be split,
Delimiter, the delimiter to use.
@Returns:
Table, each tuple consisting of a tokenized value of the original string,
split by the provided delimiter. Empty expressions result in NULL values.
@Example:
SELECT *
FROM dbo.SplitString( 'This is a test', ' ' );
SELECT *
FROM dbo.SplitString( NULL, ' ' );
*/
DECLARE @i INTEGER,
@Split VARCHAR( MAX ),
@RowCount INTEGER;
SET @RowCount = 0;
SET @i = -1;
WHILE ( LEN( ISNULL( @String, '' ) ) > 0 )
BEGIN
SET @String = LTRIM( RTRIM( @String ) );
SET @i = CHARINDEX( @Delimiter, @String );
IF ( @i = 0 )
BEGIN
INSERT INTO @Values ( SplitString )
VALUES ( LTRIM( RTRIM( @String ) ) );
SET @String = '';
END ELSE BEGIN
INSERT INTO @Values ( SplitString )
VALUES ( LTRIM( RTRIM( LEFT( @String, @i - LEN( @Delimiter ) ) ) ) );
SET @String = RIGHT( @String, LEN( @String ) - @i );
END;
SET @RowCount = @RowCount + 1;
END;
IF ( @RowCount = 0 )
BEGIN
INSERT INTO @Values ( SplitString )
VALUES ( NULL );
END;
RETURN;
END;
GO
测试用例:
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE name = 'DropThisNotAtomicNonsense'
AND type = 'U' )
BEGIN
--DROP TABLE dbo.DropThisNotAtomicNonsense;
CREATE TABLE dbo.DropThisNotAtomicNonsense
(
DropThisNotAtomicNonsense_PK TINYINT IDENTITY( 1, 1 ) NOT NULL,
PRIMARY KEY ( DropThisNotAtomicNonsense_PK ),
Result VARCHAR( MAX )
);
INSERT INTO dbo.DropThisNotAtomicNonsense ( Result )
VALUES ( 'deptid=1 & firstname=din & lastname=kal & middlename=kum' ),
( 'firstname=raj & lastname=puli & middlename=kumar & deptid=2' ),
( 'firstname=pavan & lastname=gud & middlename=kumarrao' ),
( 'deptid=7 & firstname=sha & lastname=hank' );
END;
GO
解决的问题:
SET NOCOUNT ON;
DECLARE @Cols VARCHAR( MAX ),
@SQL NVARCHAR( MAX );
CREATE TABLE #t_KeyValue
(
OriginalEntity TINYINT,
FieldName VARCHAR( MAX ),
FieldValue VARCHAR( MAX )
);
INSERT INTO #t_KeyValue ( OriginalEntity, FieldName, FieldValue )
SELECT OriginalEntity = ugh.DropThisNotAtomicNonsense_PK,
FieldName = LEFT( x.SplitString, CHARINDEX( '=', x.SplitString ) - 1 ),
FieldValue = RIGHT( x.SplitString, CHARINDEX( '=', REVERSE( x.SplitString ) ) - 1 )
FROM dbo.DropThisNotAtomicNonsense ugh
CROSS APPLY dbo.SplitString( ugh.Result, '&' ) x;
CREATE TABLE #t_ColumnList
(
FieldName VARCHAR( MAX ),
Processed TINYINT NOT NULL DEFAULT 0
);
INSERT INTO #t_ColumnList ( FieldName )
SELECT DISTINCT FieldName
FROM #t_KeyValue;
IF NOT EXISTS ( SELECT 1
FROM sys.objects
WHERE name = 'NormalTable'
AND type = 'U' )
BEGIN
--DROP TABLE dbo.NormalTable;
CREATE TABLE dbo.NormalTable
(
NormalTable_PK TINYINT IDENTITY( 1, 1 ) NOT NULL,
PRIMARY KEY ( NormalTable_PK ),
OriginalEntity TINYINT
)
WHILE EXISTS ( SELECT 1
FROM #t_ColumnList
WHERE Processed = 0 )
BEGIN
SELECT @SQL = FieldName
FROM #t_ColumnList
WHERE Processed = 0;
UPDATE #t_ColumnList
SET Processed = 1
WHERE FieldName = @SQL;
SET @SQL = N'
ALTER TABLE dbo.NormalTable
ADD [' + REPLACE( @SQL, '''', '' ) + '] VARCHAR( 128 );'
EXECUTE dbo.sp_executesql @command = @SQL;
END;
SELECT @Cols = ISNULL( @Cols, '' ) + '[' + FieldName + '], '
FROM ( SELECT DISTINCT FieldName
FROM #t_KeyValue ) l
SET @Cols = LEFT( @Cols, LEN( @Cols ) - 1 );
SET @SQL = N'
INSERT INTO dbo.NormalTable ( OriginalEntity, ' + @Cols + ' )
SELECT OriginalEntity, ' + @Cols + '
FROM ( SELECT OriginalEntity, FieldName, FieldValue
FROM #t_KeyValue ) s
PIVOT ( MAX( FieldValue ) FOR
FieldName IN ( ' + @Cols + ' ) ) p;';
RAISERROR ( @SQL, 0, 1 ) WITH NOWAIT;
EXECUTE sp_executesql @command = @SQL;
END;
GO
DROP TABLE #t_ColumnList;
DROP TABLE #t_KeyValue;
GO
SELECT *
FROM dbo.NormalTable;
GO
DROP TABLE dbo.DropThisNotAtomicNonsense;
GO
SET NOCOUNT OFF;
现在，有了漂亮的新表，您可以执行 ALTER ... COLUMN 语句以切换到某些 这些列上的正确数据类型.
Now, with your nice, new table, you can perfom ALTER ... COLUMN statements to switch to some proper data types on those columns.
这篇关于ms sql 由 & 拆分基于数据作为列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
