dcsimg
Results 1 to 4 of 4

Thread: [RESOLVED] SQL Convert comma delimited field to rows and account for redundant commas

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    314

    Resolved [RESOLVED] SQL Convert comma delimited field to rows and account for redundant commas

    Hi
    I need help with the following queries:

    Code:
    SELECT Distinct [Case ID], Split.Temp.value('.', 'VARCHAR(100)') BatchCount FROM  
    (SELECT [Case ID], Cast ('<M>' + Replace(Cast(BATCH_NUMBER as varchar(MAX)), ',', '</M><M>') + '</M>' AS XML) AS Data
    FROM DMS_Deviation_Data where  [Batch Impact] = '1'  And [R_R_Action ID] like 'DV%'  And Status ='Open' ) AS Temp
    CROSS APPLY Data.nodes ('/M') AS Split(Temp)
    The query converts the column containing comma into rows with the respective case ID for each row. However, sometimes there are an extra comma (a comma right after another comma without a text in between) due to users not being attentive when inserting the text; we cannot avoid that.

    However, the query of course take that extra comma and count considers it a new item and generates a row with empty filed for it. This creates problem later for me as I am counting those using the extended version of the above query:

    Code:
    Select  Top 20   Count([Case ID]) AS DVCount, BatchCount From
    (
    Select [Case ID] ,COUNT( BatchCount) BatchCount from
    (
    SELECT Distinct [Case ID], Split.Temp.value('.', 'VARCHAR(100)') BatchCount FROM  
    (SELECT [Case ID], Cast ('<M>' + Replace(Cast(BATCH_NUMBER as varchar(MAX)), ',', '</M><M>') + '</M>' AS XML) AS Data
    FROM DMS_Deviation_Data where  [Batch Impact] = '1'  And [R_R_Action ID] like 'DV%'  And Status ='Open' ) AS Temp
    CROSS APPLY Data.nodes ('/M') AS Split(Temp)
    )TempB
    Group by [Case ID]
    )TempC
    Group by BatchCount
    Order by BatchCount DESC
    I have been trying to get rid of that empty field in the latter query but had no success.

    Would you please have a look and see how can I get rid of that or an alternative to this solution that produces the same result but without that problem where there are commas after each other without text in between

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,327

    Re: SQL Convert comma delimited field to rows and account for redundant commas

    Is it always double commas or might you have to account for triple, quadruples etc? If it's only doubles you could just use Replace(YourString, ',,', ',') to replace the double comma with a single one. If you need triples etc. it's going to be more complicated.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    1,870

    Re: SQL Convert comma delimited field to rows and account for redundant commas

    Isn't there a Filter-Condition to throw out "empty" results?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    314

    Re: SQL Convert comma delimited field to rows and account for redundant commas

    @ FunkyDexter
    They are most probably double commas (one at the end of the first text line), but as users have free hand then there might also be cases of triple or more depending on the case.
    @ Zvoni
    I think that would be solution which discards empty results regardless whether there are double or triple comma etc. And where that filter should be set?

    Edit:
    OK, I found out where to put that filter. It is fixed now. Thanks for help.

    Code:
    Select  Top 20   Count([Case ID]) AS DVCount, BatchCount From
    (
    Select [Case ID] ,COUNT( BatchNr) BatchCount from
    (
    SELECT Distinct [Case ID], Split.Temp.value('.', 'VARCHAR(100)') BatchNr FROM  
    (SELECT [Case ID], Cast ('<M>' + Replace(Cast(BATCH_NUMBER as varchar(MAX)), ',', '</M><M>') + '</M>' AS XML) AS Data
    FROM DMS_Deviation_Data where  [Batch Impact] = '1' And [R_R_Action ID] like 'DV%'  And Status ='Closed'   ) AS Temp
    CROSS APPLY Data.nodes ('/M') AS Split(Temp)
    )TempB
    Where BatchNr <> ''
    Group by [Case ID]
    )TempC
    Group by BatchCount
    Order by BatchCount DESC
    Last edited by Grand; Apr 27th, 2019 at 11:35 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width