Results 1 to 9 of 9

Thread: Sql help

  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Sql help

    I have a table called products with the following fields

    pd_id int
    pd_name varchar
    cat_ids varchar (eg 1,2,3)

    And a table called categories with the following fields

    cat_id int
    cat_name varchar


    How can you run a query to give a list of products and their category names listed as follows:

    Code:
     
    pd_id         pd_name            cat_names
    
      1          product_A           cat_a, cat_b, cat_c
      2          product_b           cat_a, cat_c


    Am having trouble generating the cat_names displayed as a comma separated string as above.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by roac View Post
    The problem is I have cat_ids in the products table stored as a string eg. '1,4,6'

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by ozzii View Post
    The problem is I have cat_ids in the products table stored as a string eg. '1,4,6'
    That is indeed the real problem. Relational databases are really easy to work with, when they are normalised. A column containing '1,4,6' is an example of a non-normalised column. It will be hard to work with such a situation. Performance too will suffer due to the non-normalised nature of the data model.

    Do you control the structure of the database? Can you alter it? If you can, we can give you instructions on how to normalise it. Your SQL life will be a lot easier afterwards.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by Wim View Post
    That is indeed the real problem. Relational databases are really easy to work with, when they are normalised. A column containing '1,4,6' is an example of a non-normalised column. It will be hard to work with such a situation. Performance too will suffer due to the non-normalised nature of the data model.

    Do you control the structure of the database? Can you alter it? If you can, we can give you instructions on how to normalise it. Your SQL life will be a lot easier afterwards.

    Unfortunately I dont control the structure of this database and the guys who build the table didnt give any thought to the reporting aspect of this whne they designed it! Personally I would have stored the cat_ids in a separate table in which case the example link given by roac would have probably worked.

    any other ideas of how I can pull this data and display it as per orginal question.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ozzii View Post
    any other ideas of how I can pull this data and display it as per orginal question.
    the solution will require application language coding (asp, php, whatever)

    read the products table into a memory array, scan the cat_ids, pull out all the unique ones, use them to construct an IN list of cat_ids, query the categories table for the cat_names, then go back to the products array and do a search and replace on the cat_names

    difficult and inefficient coding? you betcha

    blame the guy who designed the table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This example was created to deal with your specific example. It can be done other ways that may be more useful depending on your intended use.
    Code:
    IF OBJECT_ID('dbo.cat_fight') IS NOT NULL
       DROP FUNCTION dbo.cat_fight
    IF OBJECT_ID('catagories') IS NOT NULL
       DROP TABLE catagories
    IF OBJECT_ID('products') IS NOT NULL
       DROP TABLE products
    GO
    CREATE TABLE catagories (
       cat_id       INT         NOT NULL
       CONSTRAINT XPKcatagories
          PRIMARY KEY CLUSTERED (cat_id)
    ,  cat_name		VARCHAR(50) NOT NULL
    )
    GO
    CREATE TABLE products (
       pd_id        INT         NOT NULL
    ,  CONSTRAINT XPKproducts
          PRIMARY KEY CLUSTERED (pd_id)
    ,  pd_name      VARCHAR(50) NOT NULL
    ,  cat_ids      VARCHAR(50) NOT NULL
    )
    GO
    INSERT INTO catagories
       (cat_id, cat_name)
       SELECT 1, 'cat_a' UNION ALL
       SELECT 2, 'cat_b' UNION ALL
       SELECT 3, 'cat_c'
    GO 
    INSERT INTO products
       (pd_id, pd_name, cat_ids)
       SELECT 1, 'product_A', '1, 2, 3' UNION ALL
       SELECT 2, 'product_B', '1, 3'
    GO
    CREATE FUNCTION dbo.cat_fight (
       @cat_crap	VARCHAR(50)
    ) RETURNS VARCHAR(50)  AS 
    BEGIN
       DECLARE
          @front	INT
    ,     @len		INT
    
       SET @cat_crap = @cat_crap + ' '
       
       WHILE @cat_crap LIKE '%[0-9]%'
          BEGIN
             SET @front = PatIndex('%[0-9]%', @cat_crap)
             SET @len = PatIndex('%[^0-9]%'
    ,           SubString(@cat_crap, @front, 7999)) - 1
             
             SELECT @cat_crap = Stuff(@cat_crap, @front, @len, cat_name)
                FROM catagories
                WHERE  cat_id = SubString(@cat_crap, @front, @len)
          END
          
       RETURN Rtrim(@cat_crap)
    END
    GO
    SELECT pd_id, pd_name
    ,  dbo.cat_fight(cat_ids) AS cat_names
       FROM products
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by Pat Phelan View Post
    This example was created to deal with your specific example. It can be done other ways that may be more useful depending on your intended use.
    Code:
    IF OBJECT_ID('dbo.cat_fight') IS NOT NULL
       DROP FUNCTION dbo.cat_fight
    IF OBJECT_ID('catagories') IS NOT NULL
       DROP TABLE catagories
    IF OBJECT_ID('products') IS NOT NULL
       DROP TABLE products
    GO
    CREATE TABLE catagories (
       cat_id       INT         NOT NULL
       CONSTRAINT XPKcatagories
          PRIMARY KEY CLUSTERED (cat_id)
    ,  cat_name		VARCHAR(50) NOT NULL
    )
    GO
    CREATE TABLE products (
       pd_id        INT         NOT NULL
    ,  CONSTRAINT XPKproducts
          PRIMARY KEY CLUSTERED (pd_id)
    ,  pd_name      VARCHAR(50) NOT NULL
    ,  cat_ids      VARCHAR(50) NOT NULL
    )
    GO
    INSERT INTO catagories
       (cat_id, cat_name)
       SELECT 1, 'cat_a' UNION ALL
       SELECT 2, 'cat_b' UNION ALL
       SELECT 3, 'cat_c'
    GO 
    INSERT INTO products
       (pd_id, pd_name, cat_ids)
       SELECT 1, 'product_A', '1, 2, 3' UNION ALL
       SELECT 2, 'product_B', '1, 3'
    GO
    CREATE FUNCTION dbo.cat_fight (
       @cat_crap	VARCHAR(50)
    ) RETURNS VARCHAR(50)  AS 
    BEGIN
       DECLARE
          @front	INT
    ,     @len		INT
    
       SET @cat_crap = @cat_crap + ' '
       
       WHILE @cat_crap LIKE '%[0-9]%'
          BEGIN
             SET @front = PatIndex('%[0-9]%', @cat_crap)
             SET @len = PatIndex('%[^0-9]%'
    ,           SubString(@cat_crap, @front, 7999)) - 1
             
             SELECT @cat_crap = Stuff(@cat_crap, @front, @len, cat_name)
                FROM catagories
                WHERE  cat_id = SubString(@cat_crap, @front, @len)
          END
          
       RETURN Rtrim(@cat_crap)
    END
    GO
    SELECT pd_id, pd_name
    ,  dbo.cat_fight(cat_ids) AS cat_names
       FROM products
    -PatP
    Thanks for the above solution.

    Another way I've thought of doing this is by using the xml data command as follows:

    Code:
    SELECT pd_id, pd_name
    ,  (SELECT cat_name + ', ' as 'data()'  FROM categories WHERE cat_id IN (products.cat_ids
    ) for xml path('') )  as cat_names   FROM products
    Only one problem the IN function throws an error since products.cat_ids is of datatype varchar. In mysql the FIND_IN_SET function would work. Is there anything similar in SQL SERVER?
    Last edited by ozzii; 08-16-11 at 05:19.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are many ways to work around this kind of schema flaw. Each has its own costs and benefits. The only way to pick the "best" solution is to know all of the constraints.

    You apparently have two working solutions. Both are fragile, slow, and ugly, but they work around the schema flaw. Either fix the schema flaw or pick the workaround that is least offensive and go with it!

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

Posting Permissions

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