0

my query is this:

SELECT
  r.ArtNr AS ArtNr,
  GROUP_CONCAT(CONCAT(m.HKZ, " - ", r.RefNr)) AS RefNr
FROM reference_numbers r
LEFT JOIN manufacturer m 
       ON r.KHerNr = m.HerNr
LEFT JOIN supplier_main_address s 
       ON r.DLNr = s.DLNr
LEFT JOIN article_to_generic_article_allocation a 
       ON r.ArtNr = a.ArtNr
LEFT JOIN generic_articles g 
       ON a.GenArtNr = g.GenArtNr
LEFT JOIN language_descriptions l 
       ON g.BezNr = l.BezNr AND l.SprachNr = 4
WHERE r.ArtNr = 'ADA104201'

my output is this:

ArtNr      |        RefNr 

ADA104201 | CHRYS - 05017843AA,CHRYS - 05017857AA,CHRYS - 05083853AA,CHRYS - 05083853AB,PAGID - T5144,BOSCH - 0 986 494 029,BOSCH - 0 986 424 586,TEXTAR - 2353202,JURID - 573033J,BENDIX - 573033B,MAG - 363700205144,MAG - 363702161337,MIN - MDB2082,QUINTON HA - BP1257,INT - QP704-02060,INT - QP704-02060X,TRW - GDB4118,BREMBO - P 11 012,FERODO - FDB1442,ZIMMERMANN - 23532.200.1,FTE - BL1787U2,TRISCAN - 8110 10522,PEX - 7.849,PEX - 7.849S,DELPHI - LP1522,METZGER - 0753.02,ROULUNDS - 813981,METELLI - 22-0521-0,NK - 229316,OPTIMAL - 12089,REMSA - 0753.02,ROADHOUS - 2753.02,JAPANPAR - PA-094AF,ICER - 181378,ICER - 181378-701,QH INTERNA - HP7322,QH INTERNA - 7322,TRUSTING - 537,TRUSTING - 537.0,ASHIKA - 50-00-094,BRAKE ENG - PA1330,APEC - PAD 1118,GIRLING - 6141181,BRECK - 23532 00 C,KAWE - 85116,KAWE - 0753 02,FRITECH - 537.0,FRITECH - 537,SBS - 1501229316,CIFAM - 822-521-0,WOKING - P6533.02,VILLAR - 626.1061,RAICAM - 721 0,FI.DI./ADL - D10045,FIRST LINE - FBP3353,COMLINE - CBP3894,IPS PARTS - IBD-1094,JAPKO - 50094,ABE - C1Y0

I want each of the refnrs to have separate headers(I:E comma separated), is this possible ?

Thanks in advance.

Nik

T I
  • 9,785
  • 4
  • 29
  • 51
Nik
  • 1
  • I don't understand what you are asking. You select to fields, the item number and the string of reference numbers. Two fields makes headers (ArtNr and RefNr). So what else do you want? Are you asking to get separate columns for the reference numbers rather than one single string? – Thorsten Kettner Feb 12 '14 at 13:34
  • Instead of `GROUP_CONCAT` I think you are wanting to `PIVOT`, what database are you using? – T I Feb 12 '14 at 13:38
  • Hi, yes, i wanted to get separate columns for the RefNr rather than one single string, I am using HeidiSQL. – Nik Feb 12 '14 at 13:54
  • You mean MySQL? see [this answer](http://stackoverflow.com/a/7675121/1114171) and the links it references. – T I Feb 12 '14 at 14:02
  • Forgive me for being thick but i am fairly new at this, are you able to help by amending my query above, i can't figure out how to apply the PIVOT query to that ? Thanks in advance. – Nik Feb 12 '14 at 14:09

0 Answers0