none
Get field names from comma seperated values and match data from diffrent table.

    Question

  • I found a really awsome way to combine comma seperated values like the post here...
    [How to get comma separated data data from 1 field and find related data from each of the entry to another table?]

    My question is to get the Data a little diffrently in this format from the two tables. 

    CategoryId      CategoryName
    -------------------------------
    1              General
    2              dummy
    3              test

    ArticleID       Content       CategoryId
    -------------------------------------------
    1              Test            1,2
    2              Test2           1,3
    3              Test3           1,2,3
    4              Test4           2

     

    OUTPUT:
    ------------------------------
    General   Test,Test2,Test3
    dummy    Test,Test3,Test4
    test         Test2,Test3

    Wednesday, August 21, 2013 4:59 PM

Answers

  • Thanks for pointing out the miss.

    Corrected the like clause,

    declare @categories table(CategoryId   int,  CategoryName varchar(100))
    insert into @categories values(1,'General'),(2,'dummy'),(3,'test'),(11,'new test')
    
    declare @articles table(ArticleID  int, Content varchar(100),  CategoryId varchar(100))
    
    insert into @articles values(1,'Test','1,2'),(2,'Test2','1,3'),(3,'Test3','1,2,3'),(4,'Test4','2,11')
    
    select CategoryName, (select Content+',' from @articles A 
    where ','+A.CategoryId+',' like '%,'+cast(B.CategoryId as varchar(20))+',%' for XMl path('')) as 'Article list'
    from @categories B
    
    --or
    ;with cte
    as
    (
    select CategoryName, (select Content+',' from @articles A 
    where ','+A.CategoryId+',' like '%,'+cast(B.CategoryId as varchar(20))+',%' for XMl path(''))  as 'Article list'
    from @categories B)
    
    select CategoryName,left([Article list],LEN([Article list])-1) as [Article list] from cte
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Slayer Alvarez Wednesday, August 21, 2013 7:04 PM
    Wednesday, August 21, 2013 5:56 PM

All replies

  • Something like below:

      Create  Table Master(categoryID int,CategoryName varchar(100))
      Insert Into Master values(1,'General'),(2,'dummy'),(3,'test')

      create Table Article(ArticleID int,Content varchar(100),categoryID varchar(100))
      Insert Into Article values(1,'Test','1,2'),(1,'Test2','1,3'),(1,'Test3','1,2,3'),(1,'Test4','2')


      SELECT Distinct m.CategoryName,  
            m.categoryID,
            STUFF(( SELECT  ',' + a.Content
                    FROM   Article a
                   
                   Where ','+a.categoryID+',' like '%,'+cast(m.CategoryID as varchar)+',%'
                   
                  FOR
                    XML PATH('')
                  ), 1, 1, '') AS columns2
    FROM  Master m

    Order By m.categoryID

    Wednesday, August 21, 2013 5:25 PM
  • try,

    declare @categories table(CategoryId   int,  CategoryName varchar(100))
    insert into @categories values(1,'General'),(2,'dummy'),(3,'test')
    
    declare @articles table(ArticleID  int, Content varchar(100),  CategoryId varchar(100))
    
    insert into @articles values(1,'Test','1,2'),(2,'Test2','1,3'),(3,'Test3','1,2,3'),(4,'Test4','2')
    
    select CategoryName, (select Content+',' from @articles A 
    where A.CategoryId+',' like '%'+cast(B.CategoryId as varchar(20))+',%' for XMl path('')) 
    from @categories B
    
    --or
    ;with cte
    as
    (
    select CategoryName, (select Content+',' from @articles A 
    where A.CategoryId+',' like '%'+cast(B.CategoryId as varchar(20))+',%' for XMl path('')) as 'Article list'
    from @categories B)
    
    select CategoryName,left([Article list],LEN([Article list])-1) as [Article list] from cte
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, August 21, 2013 5:41 PM
  • try,

    declare @categories table(CategoryId   int,  CategoryName varchar(100))
    insert into @categories values(1,'General'),(2,'dummy'),(3,'test')
    
    declare @articles table(ArticleID  int, Content varchar(100),  CategoryId varchar(100))
    
    insert into @articles values(1,'Test','1,2'),(2,'Test2','1,3'),(3,'Test3','1,2,3'),(4,'Test4','2')
    
    select CategoryName, (select Content+',' from @articles A 
    where A.CategoryId+',' like '%'+cast(B.CategoryId as varchar(20))+',%' for XMl path('')) 
    from @categories B
    
    --or
    ;with cte
    as
    (
    select CategoryName, (select Content+',' from @articles A 
    where A.CategoryId+',' like '%'+cast(B.CategoryId as varchar(20))+',%' for XMl path('')) as 'Article list'
    from @categories B)
    
    select CategoryName,left([Article list],LEN([Article list])-1) as [Article list] from cte


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Dear Sarat,

    I believe you need to handle CategoryID "1," and "11," differently.

    changing the below line will produce incorrect output as per your query:

    insert into @articles values(1,'Test','1,2'),(2,'Test2','1,3'),(3,'Test3','11,2,3'),(4,'Test4','2')

    But I like your approach though.


    • Edited by Taherul673 Wednesday, August 21, 2013 5:49 PM Gramatical Mistake
    Wednesday, August 21, 2013 5:49 PM
  • Thanks for pointing out the miss.

    Corrected the like clause,

    declare @categories table(CategoryId   int,  CategoryName varchar(100))
    insert into @categories values(1,'General'),(2,'dummy'),(3,'test'),(11,'new test')
    
    declare @articles table(ArticleID  int, Content varchar(100),  CategoryId varchar(100))
    
    insert into @articles values(1,'Test','1,2'),(2,'Test2','1,3'),(3,'Test3','1,2,3'),(4,'Test4','2,11')
    
    select CategoryName, (select Content+',' from @articles A 
    where ','+A.CategoryId+',' like '%,'+cast(B.CategoryId as varchar(20))+',%' for XMl path('')) as 'Article list'
    from @categories B
    
    --or
    ;with cte
    as
    (
    select CategoryName, (select Content+',' from @articles A 
    where ','+A.CategoryId+',' like '%,'+cast(B.CategoryId as varchar(20))+',%' for XMl path(''))  as 'Article list'
    from @categories B)
    
    select CategoryName,left([Article list],LEN([Article list])-1) as [Article list] from cte
    


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Slayer Alvarez Wednesday, August 21, 2013 7:04 PM
    Wednesday, August 21, 2013 5:56 PM
  • I dont know how you guys do it, but it worked like a charm.  Thank you very much.

    What is a good SQL book to learn advanced queries such as this?

    Wednesday, August 21, 2013 7:06 PM
  • There are a lot but you can have a look at the below one:

    http://www.amazon.co.uk/Cookbook-Cookbooks-OReilly-Anthony-Molinaro/dp/0596009763/ref=pd_sim_b_5/280-3671359-6361319

    Forums are the best way to learn advance stuff.
    Wednesday, August 21, 2013 7:32 PM
  • check the books written by Itzik

    http://tsql.solidq.com/books/index.htm


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, August 22, 2013 12:41 AM