Post reply

How to unnest the category info in sql 2014

  • February 24, 2020 at 10:02 pm

    Go to Answer

    #3728406

     

    Below is the table:

    CREATE TABLE [dbo].[usecase](
    [id] [int] NULL,
    [desc] [varchar](50) NULL,
    [comm] [varchar](50) NULL,
    [catgroup] [varchar](50) NULL
    ) ON [PRIMARY]

    Below is the example data:

    insert into usecase (id, [desc], comm, catgroup) values(1,'desc 1', 'comment 1', '1')
    insert into usecase (id, [desc], comm, catgroup) values(2,'desc 2', 'comment 2', '1,2')
    insert into usecase (id, [desc], comm, catgroup) values(3,'desc 3', 'comment 3', '1,2,3')
    insert into usecase (id, [desc], comm, catgroup) values(4,'desc 4', 'comment 4', '60,61')
    insert into usecase (id, [desc], comm, catgroup) values(5,'desc 5', 'comment 5', '59,60,61')
    insert into usecase (id, [desc], comm, catgroup) values(6,'desc 6', 'comment 6', '9,3')

    The catgroup is the category id for each use case, it is a 1:n relationship - one use case could belong to more than one categories

    In the above example data, category 1 has 3 use cases (1,2,3), category 2 has 2 use cases (2,3), category 3 has 2 use cases (3, 6) and so on.

    What I am looking for is a query based on category id and return all the use cases that belong to this given category id.

    The database is on SQL 2014.

    Thank you very much.

     

    • This topic was modified 4 years, 9 months ago by  PasLe Choix.

  • February 24, 2020 at 10:41 pm

    Answer

    #3728412

    A little normalization is a beautiful thing. Your problem is that your table fails the 1NF test, so you have to use DelimitedSplit8K to break up that delimited string into individual elements. And while I'm at it, DESC is a reserved word in T-SQL. Don't use it as a column name - that's just asking for trouble.

    SELECT id
     , [desc] AS Descrip
     , comm
     , cat.Item
    FROM UseCase
    CROSS APPLY Teest.dbo.DelimitedSplit8K(catgroup,',') cat;

    You can find the code for DelimitedSplit8K here.

    you can simplify your inserts by doing this:

    insert into usecase (id, [desc], comm, catgroup) values(1,'desc 1', 'comment 1', '1')
    ,(2,'desc 2', 'comment 2', '1,2')
    ,(3,'desc 3', 'comment 3', '1,2,3')
    ,(4,'desc 4', 'comment 4', '60,61')
    ,(5,'desc 5', 'comment 5', '59,60,61')
    ,(6,'desc 6', 'comment 6', '9,3');

  • February 24, 2020 at 11:15 pm

    #3728418

    Thank you so much for your quick reply. It is working as amazing miracle.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply