SQL SERVER make a row data to column

Question

I'm currently making a program to store data in database sql server. and the data is a cost that divided into max 5 column cost details. As for the first row it labeled with 1 and second with 2, up to 5 row with same id. So i'm making the table like the picture below. And Now i want to select the table from row to column like in the picture. i'm making the query like this. I get the result like i want, but the problem is that the query cost like 5/more times the execution plan by just selecting the same table. My question is, is there a way to make the result like in the picture just by selecting once the table or is there any other way to make like the result with better performance, i hear of using pivot for transposing row to column, but in my case i don't know how to do it. Thanks for reply

this is the execution plan https://www.brentozar.com/pastetheplan/?id=SJGJdaCB7

AND this is the query i used

select * FROM TblKecelakaanBiaya; 

with tbl AS (
    select *, ROW_NUMBER() OVER(PARTITION by id_kasus ORDER BY id_kasus) rn FROM TblKecelakaanBiaya
)

SELECT A.id_kasus, A.ket_biaya1, A.jlh_biaya1, C.ket_biaya2, C.jlh_biaya2,
        D.ket_biaya3, D.jlh_biaya3, E.ket_biaya4, E.jlh_biaya4, F.ket_biaya5, F.jlh_biaya5
FROM (SELECT id_kasus, ket_biaya ket_biaya1, jlh_biaya jlh_biaya1 FROM tbl WHERE rn = 1) A
LEFT JOIN (SELECT id_kasus, ket_biaya ket_biaya2, jlh_biaya jlh_biaya2 FROM tbl WHERE rn = 2) C ON A.id_kasus = C.id_kasus
LEFT JOIN (SELECT id_kasus, ket_biaya ket_biaya3, jlh_biaya jlh_biaya3 FROM tbl WHERE rn = 3) D ON A.id_kasus = D.id_kasus
LEFT JOIN (SELECT id_kasus, ket_biaya ket_biaya4, jlh_biaya jlh_biaya4 FROM tbl WHERE rn = 4) E ON A.id_kasus = E.id_kasus
LEFT JOIN (SELECT id_kasus, ket_biaya ket_biaya5, jlh_biaya jlh_biaya5 FROM tbl WHERE rn = 5) F ON A.id_kasus = F.id_kasus

The execution plan for query 1 is just 1% and the other one is 99%.

please don't paste images, when you could add your query and output as text and the actual execution plan could be posted online: https://www.brentozar.com/pastetheplan/ — Tanner, Aug 13 '18 at 08:54
sorry, i dont know if i can copy execution plan. but now how to edit the question ? — arden, Aug 13 '18 at 08:57
https://www.brentozar.com/pastetheplan/?id=SJGJdaCB7 @Tanner — arden, Aug 13 '18 at 09:01
Please take a look at this question of mine as an example, you can post a question without images: https://stackoverflow.com/questions/41840829 if you can do that, you're more likely to get a response — Tanner, Aug 13 '18 at 09:03
ok, i will post a question without image next. but can i edit the post ? or i can delete it and create new one ? @Tanner — arden, Aug 13 '18 at 09:06
you can edit, underneath the post above the comments you should see a grey _edit_ text/button. Don't delete and add a new question. You can delete and edit the same question, then when you're happy you can undelete it. — Tanner, Aug 13 '18 at 09:09
@Tanner, i edited the question now. but its hard to make the result table. so i keep the table result in the image — arden, Aug 13 '18 at 09:29

score 2 · Accepted Answer · answered Aug 13 '18 at 09:38

Perhaps a Pivot inconcert with a Cross Apply.

Example

Select * 
  From (
        Select id_kasus,item,value
         From (Select * ,RN = Row_Number() over (Partition By id_kasus Order By id_kasus) From TblKecekakaanBiaya ) A
         Cross Apply (values (concat('jih_biaya',RN),convert(varchar(150),jih_biaya))
                            ,(concat('ket_biaya',RN),ket_biaya)
                     ) b(item,value)
       ) src
 Pivot (max(value) for item in ([ket_biaya1],[jih_biaya1],[ket_biaya2],[jih_biaya2],[ket_biaya3],[jih_biaya3],[ket_biaya4],[jih_biaya4],[ket_biaya5],[jih_biaya5])  ) pvt

Returns

@arden Happy to help – John Cappelletti Aug 13 '18 at 09:49 — John Cappelletti, Aug 13 '18 at 09:49

SQL SERVER make a row data to column

1 Answers1