SQL join column within 1 table

Question

Im busy with a script to display transactions.

I want to write an SQL query which sets everything on one line.

Here is the SQL :

SELECT transactienummer, code
FROM gb_kaarten 
ORDER BY transactienummer DESC
LIMIT 4

this is the output :

transactienummer code
43141            1600
43141            4410
43141            1513
43141            1514

I just want to have a line that looks like this :

transactienummer code
43141            1600        4410         1513         1514

I have tried some joins, but i dont seem to get it.

Thanks in forehand for the help

Answer 1

I assume that you are using mysql, you should do something like this:

SELECT transactienummer, GROUP_CONCAT(string SEPARATOR ' ') 
FROM gb_kaarten
GROUP BY code;

Pay attention that you will have all the result in one field separated by ''

Answer 2

DECLARE @col NVARCHAR(MAX);
SELECT @col = COALESCE(@col + char, '') +Code 
  FROM gb_kaarten

select transactienummer,@col 
from gb_kaarten
group by transactienummer

NOTE: It will works only for one common type

Answer 3

You can find this in SO Link below

https://stackoverflow.com/a/6980/4701699

In your case find the try this

SELECT distinct [transactienummer]
,(STUFF((SELECT CAST('   ' + Code AS VARCHAR(MAX)) 
     FROM Table
     WHERE (transactienummer= Table.transactienummer) 
     FOR XML PATH ('')), 1, 2, '')) AS Code
FROM Table

Find the solution in Fiddle http://sqlfiddle.com/#!6/f8339/1