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

what type of SQL is this? MySQL? – Abdo Adel Jul 16 '15 at 09:15 — Abdo Adel, Jul 16 '15 at 09:15
Yes, im sorry i havent put that in the question – Joop Schoolse Jul 16 '15 at 09:17 — Joop Schoolse, Jul 16 '15 at 09:17

score 4 · Accepted Answer · answered Jul 16 '15 at 09:19

4

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 ''

answered Jul 16 '15 at 09:19

teoreda

2,392
1
21
28

1

what if he is not using MySQL :D, anyway it's a valuable answer – Abdo Adel Jul 16 '15 at 09:20

score 0 · Answer 2 · answered Jul 16 '15 at 09:30

0

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

answered Jul 16 '15 at 09:30

koushik veldanda

1,079
10
23

score 0 · Answer 3 · edited May 23 '17 at 12:29

0

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

edited May 23 '17 at 12:29

Community

1
1

answered Jul 16 '15 at 09:47

Sabyasachi Mishra

1,677
2
31
49

SQL join column within 1 table

3 Answers3