How can I attach distinct values from the same column?

Question

I have this query:

[select distinct pckwrk.ordnum AA,
    pckwrk.prtnum BB,
    pckwrk.pckqty CC,
    pckwrk.appqty DD,
    invdtl.rcvkey EE
from pckwrk,
     invdtl
where pckwrk.ship_line_id = invdtl.ship_line_id
    and pckwrk.wrkref = invdtl.wrkref
    and pckwrk.prtnum = invdtl.prtnum
    and pckwrk.wh_id = 'MFTZ'
    and pckwrk.prt_client_id = 'HUS'
    and invdtl.prt_client_id = 'HUS'
    and pckwrk.ordnum = '85684780'
    and pckwrk.prtnum = '1103329'
group by pckwrk.ordnum,
      pckwrk.prtnum,
      pckwrk.pckqty,
      pckwrk.appqty,
      invdtl.rcvkey]

It displays the following results:

 aa            bb      cc   dd   ee
85684780     1103329    3   3   150900000164043
85684780     1103329    4   4   150900000164043
85684780     1103329    4   4   150900000164065
85684780     1103329    6   6   151200000170364

What can I do to consolidate rows 3 and 4 since they are identical and display column EE as follows:

  aa            bb     cc   dd   ee
85684780     1103329    3   3   150900000164043
85684780     1103329    4   4   150900000164043,150900000164065
85684780     1103329    6   6   151200000170364

Thank you very much for your support!!

This is the code I am using that is giving me the error message: Status: 511-Database Error 511-Incorrect syntax near 'cast'.

[SELECT y.FF,
    STUFF((SELECT ', ' + y.II
             FROM (select distinct pckwrk.ordnum EE,
                          pckwrk.prtnum FF,
                          pckwrk.pckqty GG,
                          pckwrk.appqty HH,
                          invdtl.rcvkey II
                     from pckwrk,
                          invdtl
                    where pckwrk.ship_line_id = invdtl.ship_line_id
                      and pckwrk.wrkref = invdtl.wrkref
                      and pckwrk.prtnum = invdtl.prtnum
                      and pckwrk.wh_id = 'MFTZ'
                      and pckwrk.prt_client_id = 'HUS'
                      and invdtl.prt_client_id = 'HUS'
                      and pckwrk.ordnum = '85684780'
                      and pckwrk.prtnum = '1103329'
                    group by pckwrk.ordnum,
                          pckwrk.prtnum,
                          pckwrk.pckqty,
                          pckwrk.appqty,
                          invdtl.rcvkey) y
            WHERE y.FF = x.FF FOR XML PATH(''), TYPE) .value('.[1]',' nvarchar(max)'), 1, 2, '')
FROM (select distinct pckwrk.ordnum EE,
            pckwrk.prtnum FF,
            pckwrk.pckqty GG,
            pckwrk.appqty HH,
            invdtl.rcvkey II
       from pckwrk,
            invdtl
      where pckwrk.ship_line_id = invdtl.ship_line_id
        and pckwrk.wrkref = invdtl.wrkref
        and pckwrk.prtnum = invdtl.prtnum
        and pckwrk.wh_id = 'MFTZ'
        and pckwrk.prt_client_id = 'HUS'
        and invdtl.prt_client_id = 'HUS'
        and pckwrk.ordnum = '85684780'
        and pckwrk.prtnum = '1103329'
      group by pckwrk.ordnum,
            pckwrk.prtnum,
            pckwrk.pckqty,
            pckwrk.appqty,
            invdtl.rcvkey) x
  GROUP BY y.FF]

if it is `SQL-Server`, you need to use `STUFF` with `FOR XML`. Look at [this question](http://stackoverflow.com/questions/31211506/how-stuff-and-for-xml-path-work-in-sql-server) — techspider, Aug 16 '16 at 15:36
Slight detour...you should also stop using a comma separated list of tables for joins. The "newer" syntax has been available for almost 30 years now. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — Sean Lange, Aug 16 '16 at 15:39
group_concat does not work I am getting the following error GROUP_CONCAT is not a recognizable built in function" Is there any other way to get the same results? @juergen d — Uriel Munar, Aug 17 '16 at 16:41
STUFF with FOR XML are giving me the following error message: Database error :511 Incorrect Syntax near 'CAST' @techspider — Uriel Munar, Aug 17 '16 at 16:45
You need to post your code otherwise it is near to impossible for anyone to guess — techspider, Aug 17 '16 at 16:48
is this mysql or sql-server? if it is mysql, I'm not sure if you have the same FOR XML therre — techspider, Aug 17 '16 at 16:49
@techspider I posted the code I used for stuff and xml path...I really apprecite your support! — Uriel Munar, Aug 17 '16 at 18:15

score 0 · Answer 1 · answered Aug 16 '16 at 15:43

0

try using GROUP_CONCAT

select distinct p.ordnum AA,
p.prtnum BB,
p.pckqty CC,
p.appqty DD,
GROUP_CONCAT(i.rcvkey) EE
from pckwrk p
inner join invdtl i 
ON p.ship_line_id = i.ship_line_id
and p.wrkref = i.wrkref
and p.prtnum = i.prtnum
and p.wh_id = 'MFTZ'
and p.prt_client_id = 'HUS'
and i.prt_client_id = 'HUS'
and p.ordnum = '85684780'
and p.prtnum = '1103329'
group by p.ordnum,
  p.prtnum,
  p.pckqty,
  p.appqty

answered Aug 16 '16 at 15:43

Coding Duchess

6,445
20
113
209

Thank you ElenaDBA when I ran the query I encountered the following error message: "Error executing command. status<511> text<511-Database error: GROUP_CONCAT is not a recognizable built in function" Is there any other way to get the same results in mysql? – Uriel Munar Aug 16 '16 at 15:45
GROUP_CONCAT is a mysql function. I think you might be using SQL Server if you get that error – Coding Duchess Aug 16 '16 at 15:51

How can I attach distinct values from the same column?

1 Answers1