Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2010
    Posts
    6

    Unanswered: Split and count query?

    Greetings

    I have a column in a table that has values in SOME of the rows that i would like to split up and include in a count.

    ex:

    catalog
    111
    321
    412 | 123
    412
    111
    111 | 321
    444

    is it possiable to get this as a result?

    count catalog
    3 111
    1 123
    2 321
    2 412
    1 444

    thanks in advance! MS SQL server 2008

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a wonderful example of how efficient it is to store delimted strings. Read up on First Normal Form.
    Code:
    DECLARE @foo TABLE (bar NVARCHAR(999))
    
    INSERT INTO @foo (bar) SELECT
    N'111' UNION ALL SELECT
    N'321' UNION ALL SELECT
    N'412 | 123' UNION ALL SELECT
    N'412' UNION ALL SELECT
    N'111' UNION ALL SELECT
    N'111 | 321' UNION ALL SELECT
    N'444'
    
    DECLARE @bat TABLE (baz NVARCHAR(99))
    
    DECLARE zWonk CURSOR FOR SELECT
       bar
       FROM @foo
    OPEN zWonk
    
    DECLARE @poo NVARCHAR(999)
    
    FETCH zWonk INTO @poo
    WHILE 0 = @@FETCH_STATUS
       BEGIN
          WHILE @poo LIKE N'%|%'
             BEGIN
                INSERT INTO @bat
                   SELECT RTrim(LTrim(LEFT(@poo, CharIndex(N'|', @poo) - 1)))
                   SET @poo = SUBSTRING(@poo, 1 + CharIndex(N'|', @poo), 999)
             END
          
          INSERT INTO @bat
             SELECT RTrim(LTrim(@poo))
             
          FETCH zWonk INTO @poo
       END
    
    CLOSE zWonk
    DEALLOCATE zWonk
    
    SELECT COUNT(*) AS 'count', baz AS 'catalog'
       FROM @bat
       GROUP BY baz
       ORDER BY baz
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2010
    Posts
    6
    Thanks for your quick reply, I ran the example and it worked great! problem is I'm not sure "how" it worked.

    I already have 3 diffrent tables called "signs", "signsorder" and "signsstatus" and it has those values in there some are up to 4 values delimited in the one catalogNumber column. I would like to query them to get the values that have been returned here. Would I be able to setup something like a stored procedure that I can pass the table value and the other filtering value (where submital = "ABC") to get these results?

    Again thats for all your help!
    Jason

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First and foremost, I'd recommend that you fix the underlying problem with your table design. That will be easier now, and in the long run it will be a WHOLE LOT easier.

    If you have to stick with your present design with multiple values crammed into a single column, would using a simple LIKE clause be easier?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2010
    Posts
    6
    Unfortunatly i am not the creator of the DB and it is tied to 3 programs so I cannot change the tables. When I query the database with:

    SELECT [catalogNumber], COUNT(catalogNumber) As Count FROM [_MasterList] Where MasterList = 'ABCD' GROUP BY [catalogNumber] Order By catalogNumber Asc

    I get:

    85821-3-1 2
    85821-3-2 2
    85822-3-1 1
    85822-3-2 1
    85822-3-3 3
    85822-3-4 2
    60667-2 | 60667-6 1
    60667-6 | 60667-6 | 85822-3-4 | 85822-3-2 1

    and would like the ones that are | delimited to be counted as a single catalogNumber.

    Thanks for all of your help so far!

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Keep in mind that this is a question, not a suggestion. Does it help to use something like:
    Code:
    SELECT [catalogNumber], Count(catalogNumber) AS Count
       FROM [_MasterList]
       WHERE MasterList = 'ABCD' 
          AND catalogNumber LIKE N'%60667-6%'
       GROUP BY [catalogNumber] 
       ORDER BY catalogNumber ASC
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Oct 2010
    Posts
    6
    I don't think so, as this did not return any results. and the catalog numbers vary a lot.

  8. #8
    Join Date
    Oct 2010
    Posts
    6
    The first suggestion worked in theory but i can not figure out how to use in with an existing table

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by JayTreDoe View Post
    I don't think so, as this did not return any results. and the catalog numbers vary a lot.
    Based on the sample data you posted, the last SQL that I provided should have returned two rows. If it did not return the last two rows in your sample data, something is very, VERY wrong!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by JayTreDoe View Post
    The first suggestion worked in theory but i can not figure out how to use in with an existing table
    @foo in my example is a placeholder for an existing table like the [_MasterData] from your example. Skip the declaration and the populating of @foo, then replace @foo with [_MasterData] and you should be on the right track.

    This is a really good example of why delimited columns are such a bad idea. This is going to come back to haunt you over, and over, and over... You really need to get accustomed to the pain if you choose not to fix the design problems.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  11. #11
    Join Date
    Oct 2010
    Posts
    6
    Sorry It did work, I forgot to change the string in at the where clause. That works but for one which means i would have to query the entire group and then re query for each catalogNumber? wouldn't that be a lot of select statements?

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The number of SELECT statements depends on what you need to know. If you need to know how many of doohickey 60667-6 there are, then there is only one SELECT statement. If you need totals by doohickey, you've got a lot of work to do! The problem is that short of parsing, normalizing, and then counting (which is what my code does), the LIKE clause is your best option.

    I probably am starting to sound annoying, but you have a bad design. This is like driving a car that was designed so you could put a full sized dining table and serve dinner on it in the back seat. The design is bad, it limits what you can do, and a bad design can't be fixed it must be replaced. If you choose to live with the design, you'll have to find ways to live with the consequences of that decision.

    There are ways to mitigate the problems, but that mitigation needs to consider all of your requirements and all of your options. In the end, you'll mitigate the problems so that they don't bother you as much but you'll still have the problems. I feel bad for you, you're in a very bad place right now.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Jun 2005
    Posts
    319
    Quote Originally Posted by Pat Phelan View Post
    This is like driving a car that was designed so you could put a full sized dining table and serve dinner on it in the back seat.
    Please pass the Grey Poupon

  14. #14
    Join Date
    Oct 2010
    Posts
    1

    theory

    The first theory i tried and it worked perfectly,as a matter of fact i tried 3 of these theories and 2 worked.

Posting Permissions

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