Splitting a csv column from select query into multiple columns

Question

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

Answer 1

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