locked
select as comma separated RRS feed

  • Question

  • Table values

    select 1 se_ref_no, 'ABC' cust_name, "507 167" cust_address union all
    select 2 se_ref_no, 'ABC' cust_name, "507 167" cust_address union all
    select 3 se_ref_no, 'ABC' cust_name, "507 167" cust_address union all
    select 4 se_ref_no, 'ABC' cust_name, "507 167" cust_address

    Need output as

    1, "ABC", "507 167", "2,3,4"

    2, "ABC", "507 167", "1,3,4"

    3, "ABC", "507 167", "1,2,4"

    4, "ABC", "507 167", "1,2,3"

    Can any one help on this please


    Naresh

    Saturday, June 17, 2017 1:33 PM

All replies

  • Hello,

    See Query Master Data with Details as CSV for an example


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, June 17, 2017 1:45 PM
  • create table test(se_ref_no varchar(10),cust_name varchar(100), cust_address varchar(1000))
    
    insert into test 
    select 1 se_ref_no, 'ABC' cust_name, '507 167' cust_address union all
    select 2 se_ref_no, 'ABC' cust_name, '507 167' cust_address union all
    select 3 se_ref_no, 'ABC' cust_name, '507 167' cust_address union all
    select 4 se_ref_no, 'ABC' cust_name, '507 167' cust_address
    
    declare @tmp varchar(250)
    SET @tmp = ''
    select @tmp = @tmp + se_ref_no + ',' from test
    select *, substring(replace(@tmp,se_ref_no+',',''),1, LEN(replace(@tmp,se_ref_no+',',''))-1) from test
    Sunday, June 18, 2017 4:37 AM
  • with cte
    as
    (
    select 1 se_ref_no, 'ABC' cust_name, '507 167' cust_address union all
    select 2 se_ref_no, 'ABC' cust_name, '507 167' cust_address union all
    select 3 se_ref_no, 'ABC' cust_name, '507 167' cust_address union all
    select 4 se_ref_no, 'ABC' cust_name, '507 167' cust_address
    )
    SELECT 
            cust_name,cust_address,
             REPLACE(STUFF(group_list, 1, 1, ''),se_ref_no,'')  AS groups
    FROM cte AS A
    CROSS APPLY (SELECT ',' + cast(se_ref_no as varchar(20))
                  FROM cte AS B
                  WHERE B.cust_name = A.cust_name
                  FOR XML PATH('')) AS T(group_list);

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, June 18, 2017 4:44 AM
  • Any idea on how to write same thing in MY SQL?

    Naresh

    Monday, June 19, 2017 4:59 PM
  • This is a forum for T-SQL. For getting solutions in MySQL, it would be better that you post in the MySQL forum

    https://forums.mysql.com/

    Monday, June 19, 2017 5:32 PM