Sql-server – Splitting a csv column from select query into multiple columns

sql serverstored-procedurest-sql

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

Best Answer

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

Related Solutions

Sql-server – Separate data from 1 column into multiple columns

CREATE TABLE #Data
    (
      ItemNo VARCHAR(10),
      Variant INT,
      SalesCode VARCHAR(30),
      UnitPrice INT
    )
GO
INSERT  INTO #Data
        ( ItemNo, Variant, SalesCode, UnitPrice )
VALUES  ( 'A', 1, 'SRP', 100 ),
        ( 'A', 2, 'SRP', 200 ),
        ( 'A', 1, 'WSP', 50 ),
        ( 'A', 2, 'WSP', 100 ),
        ( 'B', 1, 'SRP', 300 ),
        ( 'B', 2, 'SRP', 400 ),
        ( 'B', 1, 'WSP', 150 ),
        ( 'B', 2, 'WSP', 200 )
GO
SELECT  ItemNo, Variant, 'SRP' AS SalesCode_SRP,
        SUM(CASE SalesCode
              WHEN 'SRP' THEN UnitPrice
              ELSE 0
            END) UnitPrice_SRP, 'WSP' AS SalesCode_WSP,
        SUM(CASE SalesCode
              WHEN 'WSP' THEN UnitPrice
              ELSE 0
            END) UnitPrice_WSP
FROM    #Data
GROUP BY ItemNo, Variant 

GO
DROP TABLE  #Data

Sql-server – Grouping Data Using Joined Tables with Certain Distinct Values

If it is safe to assume that a single order can have only one distinct StoreID, you could resolve your issue by generating the set of distinct OrderID, StoreID pairs from orderLines and join that set instead of the table itself. That way you will not need to use DISTINCT with aggregation:

SELECT 
  `ol`.`storeID`,
  SUM(`o`.`grossValue`) AS 'Total',
  SUM(`o`.`paymentValue`) AS 'paymentTotal'
FROM
  `orders` AS `o`
LEFT JOIN
  (SELECT DISTINCT `OrderID`, `StoreID` FROM `orderLines`) AS `ol`
  ON `o`.`orderID` = `ol`.`orderID`
WHERE
  (`o`.`orderDate` BETWEEN '2015-07-07 00:00:00' AND '2015-07-07 23:59:59')
GROUP
  BY `ol`.`storeID`
;

And, as has already been mentioned, you should probably use INNER JOIN instead of the LEFT JOIN, unless you have orders that do not have order lines but for some reason do have a gross value and a payment (a little strange, but may be there is a reason for that), and you want them included in the results. (They will be represented as a single row with a null Store ID.)

Best Answer

Related Solutions

Sql-server – Separate data from 1 column into multiple columns

Sql-server – Grouping Data Using Joined Tables with Certain Distinct Values

Related Question