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
– jarlh, Commented Mar 3, 2016 at 9:45
Check STUFF function instead of Group_concat, that's for TSQL — Veljko89
– Veljko89, Commented Mar 3, 2016 at 9: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
– Kim, Commented Mar 3, 2016 at 9:58

Community · Accepted Answer · 2017-05-23 11:58:50Z

1

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

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Mar 3, 2016 at 9:53

miranda

261 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Make multiple row result to one row SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related