7

I have a stored procedure 

SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN MAX([ShipWorks].[dbo].[OrderItem].[Location]) ELSE MAX([Picklist].[dbo].[ItemData].[InventoryLocation]) END AS Location, SUM([ShipWorks].[dbo].[OrderItem].[Quantity]) AS Quantity, MAX(LEFT([ShipWorks].[dbo].[Store].[StoreName], 1)) AS Store FROM [ShipWorks].[dbo].[OrderItem] INNER JOIN [ShipWorks].[dbo].[Order] ON [ShipWorks].[dbo].[OrderItem].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID] INNER JOIN [ShipWorks].[dbo].[Store] ON [ShipWorks].[dbo].[Order].[StoreID] = [ShipWorks].[dbo].[Store].[StoreID] LEFT JOIN [ShipWorks].[dbo].[AmazonOrder] ON [ShipWorks].[dbo].[AmazonOrder].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID] LEFT JOIN [Picklist].[dbo].[ItemData] ON [Picklist].[dbo].[ItemData].[InventoryNumber] = [ShipWorks].[dbo].[OrderItem].[Code] OR [Picklist].[dbo].[ItemData].[MediaCreator] = [OrderItem].[Code] WHERE [ShipWorks].[dbo] [Order].[LocalStatus] = 'Recently Downloaded' AND ([ShipWorks].[dbo].[AmazonOrder].[FulfillmentChannel] = 2 OR [ShipWorks].[dbo].[Store].[StoreName] <> 'Amazon') GROUP BY [ShipWorks].[dbo].[OrderItem].[Code] ORDER BY Location

that returns this: 

Code Location Quantity Store L698-W-EA NULL 2 A L82009-EA A1K2, A1N2, C4Y3, CBP2 2 A L80401-A-EA A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20 17 A CWD2132W-BOX-25PK A-AISLE 1 M GM22660003-EA B1K2 1 M

I want to split the Location column into multiple columns .

Code loc1 loc2 loc3 loc4 loc5 loc6 Quantity Store L698-W-EA NULL 2 A L82009-EA A1K2, A1N2, C4Y3, CBP2 2 A L80401-A-EA A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20 17 A CWD2132W-BOX-25PK A-AI 1 M GM22660003-EA B1K2 1 M

I found this answer but that was for postgresql. Also this, but it returns a table with multiple rows and you can select the top 1 but I want all the results and I'm not sure how to use the tvf Split(string, ',')in the stored procedure to get the result set I want?

This select statement results in a error: 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery.'

SELECT [ShipWorks].[dbo].[OrderItem].[Code] AS Code, CASE WHEN (LEN(ISNULL(MAX([ShipWorks].[dbo].[OrderItem].[Location]),'')) = 1) THEN MAX((SELECT TOP 1 i.* FROM dbo.Split([ShipWorks].[dbo].[OrderItem].[Location], ',') as i)) ELSE MAX((SELECT TOP 1 i.* FROM dbo.Split([ShipWorks].[dbo].[OrderItem].[Location], ',') as i)) END AS Location, SUM([ShipWorks].[dbo].[OrderItem].[Quantity]) AS Quantity, MAX(LEFT([ShipWorks].[dbo].[Store].[StoreName], 1)) AS Store FROM [ShipWorks].[dbo].[OrderItem] INNER JOIN [ShipWorks].[dbo].[Order] ON [ShipWorks].[dbo].[OrderItem].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID] INNER JOIN [ShipWorks].[dbo].[Store] ON [ShipWorks].[dbo].[Order].[StoreID] = [ShipWorks].[dbo].[Store].[StoreID] LEFT JOIN [ShipWorks].[dbo].[AmazonOrder] ON [ShipWorks].[dbo].[AmazonOrder].[OrderID] = [ShipWorks].[dbo].[Order].[OrderID] LEFT JOIN [Picklist].[dbo].[ItemData] ON [Picklist].[dbo].[ItemData].[InventoryNumber] = [ShipWorks].[dbo].[OrderItem].[Code] OR [Picklist].[dbo].[ItemData].[MediaCreator] = [OrderItem].[Code] WHERE [Order].[LocalStatus] = 'Recently Downloaded' AND ([ShipWorks].[dbo].[AmazonOrder].[FulfillmentChannel] = 2 OR [ShipWorks].[dbo].[Store].[StoreName] <> 'Amazon') GROUP BY [ShipWorks].[dbo].[OrderItem].[Code] ORDER BY Location
Improve this question

1 Answer 1

Reset to default
6

Making use of Jeff Moden's Tally-Ho! CSV splitter from here:

CREATE FUNCTION [dbo].[DelimitedSplit8K] --===== Define I/O parameters (@pString VARCHAR(8000), @pDelimiter CHAR(1)) --WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE! RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000... -- enough to cover VARCHAR(8000) 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 ; go

We can code the solution as an apply against Jeff's function and a pivot like so:

with data as ( select Code,Location,Quantity,Store from ( values ('L698-W-EA', NULL, 2, 'A') ,('L82009-EA', 'A1K2, A1N2, C4Y3, CBP2', 2, 'A') ,('L80401-A-EA', 'A1S2, SHIP, R2F1, CBP5, BRP, BRP1-20', 17,'A') ,('CWD2132W-BOX-25PK', 'A-AISLE', 1, 'M') ,('GM22660003-EA', 'B1K2', 1, 'M') )data(Code,Location,Quantity,Store) ) ,shredded as ( select Code,Location,Quantity,Store,t.* from data cross apply [dbo].[DelimitedSplit8K](data.Location,',') as t ) select pvt.Code,pvt.Quantity,pvt.Store ,cast(isnull(pvt.[1],' ') as varchar(8)) as Loc1 ,cast(isnull(pvt.[2],' ') as varchar(8)) as Loc2 ,cast(isnull(pvt.[3],' ') as varchar(8)) as Loc3 ,cast(isnull(pvt.[4],' ') as varchar(8)) as Loc4 ,cast(isnull(pvt.[5],' ') as varchar(8)) as Loc5 ,cast(isnull(pvt.[6],' ') as varchar(8)) as Loc6 from shredded pivot (max(Item) for ItemNumber in ([1],[2],[3],[4],[5],[6])) pvt; ; go

yielding this:

Code Quantity Store Loc1 Loc2 Loc3 Loc4 Loc5 Loc6 ----------------- ----------- ----- -------- -------- -------- -------- -------- -------- L698-W-EA 2 A L82009-EA 2 A A1K2 A1N2 C4Y3 CBP2 L80401-A-EA 17 A A1S2 SHIP R2F1 CBP5 BRP BRP1-20 CWD2132W-BOX-25PK 1 M A-AISLE GM22660003-EA 1 M B1K2
Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.