Results 1 to 3 of 3
  1. #1
    Join Date
    May 2010
    Posts
    11

    Unanswered: Group by query issue

    I am trying very hard to figure out how to write this query and just having a really tough time. I don't think I can put an attachment so let me see if I can paste something.

    data have
    vendor name function market line contact contactemail address city state zip
    Access IPA Alpha MSO CM VA COM Jason Doe jldoe@comcast.net 121 Single Street Lancaster CA 92777
    Access IPA Alpha MSO CR DC MCD Sarah Doe sdoe@comcast.net 5732 Jones Street Fremont CA 92898
    Access IPA Alpha MSO UM TX MCR Jessica Doe jdoe@comcast.net 181 Pringle Street Hayward CA 93092

    data need
    vendor name function market line contact contactemail address city state zip
    Access IPA Alpha MSO CM, CR, UM VA, DC, TX COM, MCD, MCR Jason doe, Sarah Doe, Jessica Doe jldoe@comcast.net, sdoe@comcast.net, jdoe@comcast.net 121 Single Street, 5732 Jones Street, 181 Pringle Street Lancaster, Fremont, Hayward CA 92777, 92898, 93092

    I will try and paste an attachment. I have over 100k rows in this table and this is just one example of what is in the table. well that did not work. let me try a .txt
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2007
    Posts
    48
    Provided Answers: 3
    have you looked at PIVOT table or LISTAGG?

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    This isn't particularly pretty, but you could use FOR XML. The catch here is it is going to create comma separated list of every item in a column (including duplicates), so if this is an issue, you will have to ad a step before this to de-dup the values for each column.

    Code:
    create table #test (
    	vendorname varchar(20)
    	,functions varchar(10)
    	,market varchar(10))
    
    INSERT INTO #test
    values ('Access IPA','CM','VA'),('Access IPA','CR','DC'),('Access IPA','UM','TX')
    
    select distinct a.vendorname
      ,substring(
          ( Select ', ' + ltrim(rtrim (b.functions)) as [text()]
        from #test b
        where a.vendorname = b.vendorname
        order by b.vendorname, b.functions
        for XML PATH ('')
        ), 2, 1000) functions
      ,substring(
          ( Select ', ' + ltrim(rtrim (b.market)) as [text()]
        from #test b
        where a.vendorname = b.vendorname
        order by b.vendorname, b.market
        for XML PATH ('')
        ), 2, 1000) market
    from #test a
    order by a.vendorname

Posting Permissions

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