IF OBJECT_ID('f_split') IS NOT NULL
BEGIN
PRINT 'Dropping function'
DROP FUNCTION f_split
IF @@ERROR = 0
PRINT 'Function dropped'
END
GO
CREATE FUNCTION f_split
(
@c VARCHAR(2000),
@split VARCHAR(2)
)
RETURNS @t TABLE(id INT IDENTITY, col VARCHAR(20))
AS
BEGIN
SET @c = REPLACE(@c, CHAR(10), '')
SET @c = REPLACE(@c, ' ', '')
SET @c = REPLACE(@c, CHAR(13), '')
WHILE (CHARINDEX(@split, @c) <> 0)
BEGIN
INSERT @t ( col )
VALUES ( SUBSTRING(@c, 1, CHARINDEX(@split, @c) -1) )
SET @c = STUFF(@c, 1, CHARINDEX(@split, @c), '')
END
INSERT @t ( col )
VALUES ( LTRIM(RTRIM(@c)) )
RETURN
END
GO
DECLARE @string NVARCHAR(MAX) =
N'
Custaccount,
salesID,
invoiceID,
FROMWHOUSE,
TOWHOUSE,
createddate,
ETADATE,
SHIPIMMEDCODE,
ordertype,
avorderstatus,
AVAPACCOUNT
';
WITH cte AS (
SELECT tname,
cname,
MAX(TYPE) AS [type],
MAX([len]) AS [len],
MAX([per]) AS [per] FROM (
SELECT * FROM ax
UNION SELECT * FROM drmpos
) ax
GROUP BY tname, cname
)
,coltype AS (
SELECT DISTINCT tname, cname,
CASE ty.system_type_id WHEN 108 THEN ty.name + '(' + CAST(cte.[len] AS NVARCHAR(5))
+ '),' WHEN 231 THEN ty.name +
'(' + CAST(cte.[len] AS NVARCHAR(5)) + '),' END AS typ FROM
sys.types ty
INNER JOIN cte
ON ty.system_type_id = cte.[type]
WHERE ty.name <> 'sysname'
)
,res AS (
SELECT id,col,
cname,
ISNULL(typ, 'nvarchar(50),') AS c , tname
FROM dbo.f_split(@string, ',') AS t
LEFT JOIN coltype
ON t.col = cname
)
SELECT id ,col,MAX(c)AS c from res GROUP BY id,col
ORDER BY id
Tsql split
最新推荐文章于 2022-01-05 15:47:32 发布