February 25, 2015 at 6:41 am
ok here's an interesting one that just hit my workspace.
i'm getting a file that essentially has a parameter array: a specific like would have three known values, and then x number of groups of four parts;
the question is how to handle logical groups of parameters; i though maybe by using a modulous on their itemnumber ...
so i can easily use DelimitedSplit8K to definitively find the first thee values, but how do i dynamically get X number of groups of four;
eventually i need the first three fields combined with each quadgroup.
so a rough example of the desired output is this:
this:
'03,0000001,USD,010,81257946,,,015,121809761,,'
is split into rows with the first three columns included in each row:
030000001USD01081257946NULLNULL
030000001USD015121809761NULLNULL
here's a setup i've put together:
IF OBJECT_ID('tempdb.[dbo].[#AccountIdentifier]') IS NOT NULL
DROP TABLE [dbo].[#AccountIdentifier]
GO
CREATE TABLE [dbo].[#AccountIdentifier] (
[AccountIdentifierID] INT IDENTITY(1,1) NOT NULL,
[DateReceived] DATETIME NULL DEFAULT getdate(),
[RecordCode] VARCHAR(3) NULL,
[CustomerAccountNumber] VARCHAR(35) NULL,
[CurrencyCode] VARCHAR(20) NULL,
[TypeCode] VARCHAR(20) NULL,
[Amount] VARCHAR(20) NULL,
[ItemCount] VARCHAR(20) NULL,
[FundsType] VARCHAR(20) NULL);
;WITH MyCTE([Param])
AS
(
SELECT '03,0000001,USD,010,81257946,,' UNION ALL
SELECT '03,0000001,USD,010,81257946,,,015,121809761,,' UNION ALL
SELECT '03,0000001,USD,010,81257946,,,015,121809761,,,040,121809761,,,043,172465565,,,045,121809761,,,050,104928783,,,055,143699899,,,063,0,,,072,0,,,073,0,,,074,0,,,075,0,,,100,110000000,1,,190,110000000,1,,400,69448185,29,,470,9330683,23,,570,60117502,6,' UNION ALL
SELECT '03,0000002,USD,010,0,,,015,0,,,040,0,,,043,0,,,045,0,,,050,0,,,055,0,,,063,0,,,072,0,,,073,0,,,074,0,,,075,0,,,100,223103,1,,140,223103,1,,400,223103,1,,570,223103,1,' UNION ALL
SELECT '03,0000003,USD,010,0,,,015,0,,,040,0,,,043,-287818,,,045,0,,,050,-50055,,,055,-186548,,,063,0,,,072,0,,,073,0,,,074,0,,,075,0,,,100,219538,3,,110,54427,1,,140,165111,2,,400,219538,1,,570,219538,1,'
)
SELECT * FROM MyCTE
CROSS APPLY (SELECT Item From dbo.DelimitedSplit8k([Param],',') WHERE ItemNumber = 1) AS RecordCode
CROSS APPLY (SELECT Item From dbo.DelimitedSplit8k([Param],',') WHERE ItemNumber = 2) AS CustomerAccountNumber
CROSS APPLY (SELECT Item From dbo.DelimitedSplit8k([Param],',') WHERE ItemNumber = 3) AS CurrencyCode
Lowell
February 25, 2015 at 2:39 pm
ok while this gives me the desired results, it bothers me to be using distinct.
under the covers it's twisting these five rows into 250K rows, and back down to ~1300 rows; i doubt this would scale at all with a decent number of prelim rows.
any feedback from anyone on a better way to get this data?
;WITH MyCTE([ID],[val])
AS
(
SELECT 1,'03,0000001,USD,010,81257946,,' UNION ALL
SELECT 2,'03,0000001,USD,010,81257946,,,015,121809761,,' UNION ALL
SELECT 3,'03,0000001,USD,010,81257946,,,015,121809761,,,040,121809761,,,043,172465565,,,045,121809761,,,050,104928783,,,055,143699899,,,063,0,,,072,0,,,073,0,,,074,0,,,075,0,,,100,110000000,1,,190,110000000,1,,400,69448185,29,,470,9330683,23,,570,60117502,6,' UNION ALL
SELECT 4,'03,0000002,USD,010,0,,,015,0,,,040,0,,,043,0,,,045,0,,,050,0,,,055,0,,,063,0,,,072,0,,,073,0,,,074,0,,,075,0,,,100,223103,1,,140,223103,1,,400,223103,1,,570,223103,1,' UNION ALL
SELECT 5,'03,0000003,USD,010,0,,,015,0,,,040,0,,,043,-287818,,,045,0,,,050,-50055,,,055,-186548,,,063,0,,,072,0,,,073,0,,,074,0,,,075,0,,,100,219538,3,,110,54427,1,,140,165111,2,,400,219538,1,,570,219538,1,'
),
InterData
AS
(
SELECT * FROM MyCTE
CROSS APPLY (Select Item As [RecordCode] FROM dbo.DelimitedSplit8k(MyCTE.[val],',') WHERE ItemNumber = 1)C1
CROSS APPLY (Select Item As [CustomerAccountNumber] FROM dbo.DelimitedSplit8k(MyCTE.[val],',') WHERE ItemNumber = 2)C2
CROSS APPLY (Select Item As [CurrencyCode] FROM dbo.DelimitedSplit8k(MyCTE.[val],',') WHERE ItemNumber =3)C3
)
SELECT --row_number() over(partition by [ID] ORDER BY [ID]) As RW,
distinct *
FROM InterData
CROSS APPLY (Select Item As [TypeCode] FROM (SELECT * FROM dbo.DelimitedSplit8k(InterData.[val],',')) X WHERE (ItemNumber) % 4 = 0 AND ItemNumber > 3)T1
CROSS APPLY (Select Item As [Amount] FROM (SELECT * FROM dbo.DelimitedSplit8k(InterData.[val],',')) X WHERE (ItemNumber) % 4 = 1 AND ItemNumber > 3)T2
CROSS APPLY (Select Item As [ItemCount] FROM (SELECT * FROM dbo.DelimitedSplit8k(InterData.[val],',')) X WHERE (ItemNumber) % 4 = 2 AND ItemNumber > 3)T3
CROSS APPLY (Select Item As [FundsType] FROM (SELECT * FROM dbo.DelimitedSplit8k(InterData.[val],',')) X WHERE (ItemNumber) % 4 = 3 AND ItemNumber > 3)T4
ORDER BY InterData.[ID]
Lowell
February 25, 2015 at 5:39 pm
This works better, I believe.
Yours was suffering from a nasty cross join that was mixing up the data (see ID 2 for example)
;WITH MyCTE([ID],[val])
AS
(
SELECT 1,'03,0000001,USD,010,81257946,,' UNION ALL
SELECT 2,'03,0000001,USD,010,81257946,,,015,121809761,,' UNION ALL
SELECT 3,'03,0000001,USD,010,81257946,,,015,121809761,,,040,121809761,,,043,172465565,,,045,121809761,,,050,104928783,,,055,143699899,,,063,0,,,072,0,,,073,0,,,074,0,,,075,0,,,100,110000000,1,,190,110000000,1,,400,69448185,29,,470,9330683,23,,570,60117502,6,' UNION ALL
SELECT 4,'03,0000002,USD,010,0,,,015,0,,,040,0,,,043,0,,,045,0,,,050,0,,,055,0,,,063,0,,,072,0,,,073,0,,,074,0,,,075,0,,,100,223103,1,,140,223103,1,,400,223103,1,,570,223103,1,' UNION ALL
SELECT 5,'03,0000003,USD,010,0,,,015,0,,,040,0,,,043,-287818,,,045,0,,,050,-50055,,,055,-186548,,,063,0,,,072,0,,,073,0,,,074,0,,,075,0,,,100,219538,3,,110,54427,1,,140,165111,2,,400,219538,1,,570,219538,1,'
), InterData as
(
select
ID
, val
, max(case ItemNumber when 1 then Item end) as [RecordCode]
, max(case ItemNumber when 2 then Item end) as [CustomerAccountNumber]
, max(case ItemNumber when 3 then Item end) as [CurrencyCode]
from myCTE
cross apply dbo.DelimitedSplit8K(val,',')
where ItemNumber<=3 -- only need first 3 items in this part
group by ID,val
)
select
ID
, val
, RecordCode
, CustomerAccountNumber
, CurrencyCode
, ItemNumber / 4 as RowGroup
, max(case ( ItemNumber-4 ) % 4 when 0 then Item end) as [TypeCode]
, max(case ( ItemNumber-4 ) % 4 when 1 then Item end) as [Amount]
, max(case ( ItemNumber-4 ) % 4 when 2 then Item end) as [ItemCount]
, max(case ( ItemNumber-4 ) % 4 when 3 then Item end) as [FundsType]
from InterData
cross apply dbo.DelimitedSplit8K(val,',')
where ItemNumber>=4
group by
ID
, val
, RecordCode
, CustomerAccountNumber
, CurrencyCode
, ItemNumber / 4
order by ID, RowGroup;
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply