Make multiple row result to one row SQL

Question

I'm trying to create

select c.FullName, b.*, e.Description as Posisilama, f.Description as Posisibaru, g.Description,
       GROUP_CONCAT('b.nilai' SEPARATOR ',') AS nilai
from penilaian_header a
    left join penilaian_detail b on a.KodePenilaian = b.KodePenilaianH
    left join employee c on a.Nip = c.Nip
    left join HistoryPosition d on a.KodePenilaian = d.KodePenilaian    
    left join Position e on d.OldPosition = e.PositionCode
    left join Position f on d.NewPosition = f.PositionCode
    left join Outlet g on c.OutletCode = g.OutletCode

Can you check my query, because I got this error:

Incorrect syntax near 'SEPARATOR'

What I need to do is to make multiple row result in to one row SQL. Because my result is like this

I found a solution. I use pivot.

select * from 
            (
             select row, c.Nilai,b.Fullname,a.KodePenilaian,d.Description from penilaian_header a 
            left join employee b on a.Nip = b.Nip 
            left join outlet d on a.Outlet = d.OutletCode
            left join (select ROW_NUMBER() OVER(PARTITION BY KodePenilaianH ORDER BY idPenilaiand DESC) AS Row, Nilai,KodePenilaianH from penilaian_Detail
            ) c on a.KodePenilaian = c.KodePenilaianH where a.Outlet like '%%' and Periode like '%%'
            ) nilai 
            pivot
            (
            sum(nilai)
            for row in ([1],[2],[3],[4],[5])
            ) piv;

You need a GROUP BY clause. (And remove single quotes around column names.) — jarlh, Mar 03 '16 at 09:45
Check STUFF function instead of Group_concat, that's for TSQL — Veljko89, Mar 03 '16 at 09:47
How do you want to group this in one row? what will you do to the column2,3,4? They have different values — Kim, Mar 03 '16 at 09:58
Change the quotes around _b.nilai_ to back tics (or just remove the quotes) — Kickstart, Mar 03 '16 at 11:23
So what RDBMS are you using? Your tags say SQL Server, your SQL statement tells something different. — TT., Mar 04 '16 at 06:56

score 1 · Accepted Answer · edited May 23 '17 at 11:58

miss a group by clause,check this : How to use GROUP_CONCAT in a CONCAT in MySQL

i will write like this :

select c.FullName, b.*, e.Description as Posisilama
, f.Description as Posisibaru, g.Description,
       GROUP_CONCAT('b.nilai' SEPARATOR ',') AS nilai
from penilaian_header a
    left join penilaian_detail b on a.KodePenilaian = b.KodePenilaianH
    left join employee c on a.Nip = c.Nip
    left join HistoryPosition d on a.KodePenilaian = d.KodePenilaian    
    left join Position e on d.OldPosition = e.PositionCode
    left join Position f on d.NewPosition = f.PositionCode
    left join Outlet g on c.OutletCode = g.OutletCode  
group by  c.FullName, b.*, e.Description 
, f.Description , g.Description

Make multiple row result to one row SQL

1 Answers1