1

This question already has an answer here:

I need to know whether 'pivot' in SQL Server can be used for converting rows to columns if there is no aggregate function to be used. I saw lot of examples with aggregate function only. My columns are string data type and I need to convert this row data to column data. This is why I wrote this question. I just did it with case. Can anyone help me......Thanks in advance.

Sample format

empid    wagecode    amount
  1      basic       1000
  1      TA           500
  1      DA           500
  2      Basic       1500
  2      TA           750
  2      DA           750

Desired output:

empid   basic    TA    DA
  1     1000     500   500
  2     1500     750   750

marked as duplicate by Paul White Sep 28 '15 at 22:54

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

0
SELECT empid , [Basic], [TA],[DA]
FROM (
SELECT empid, wage, amount
FROM yourtable) up
PIVOT (SUM(amount) FOR wage IN (basic, ta,da)) AS pvt
ORDER BY empid
GO
  • hi, i have problem with--- SUM(amount) FOR wage IN (basic, ta,da) this part my codes are 1bas,1ot,1ta instead of basic,ta,da.. – Sivajith Jun 11 '12 at 10:55
  • For wage in( your data) – AmmarR Jun 11 '12 at 10:57
  • when i replace it by 1bas i got the error 'Incorrect syntax near '1'.' – Sivajith Jun 11 '12 at 11:00
  • thanks AmmarR. it works....for me. the problem is just put the wage in square brackets and execute..thanks ..thanks a lot.. – Sivajith Jun 11 '12 at 11:24
  • @Sivajith: So how does this help you with 80 rows per empid? I'd also compare performance of this solution vs mine – gbn Jun 11 '12 at 11:38
3

Don't pivot if you always have 3 rows per empid. Just self join

SELECT
   t1.empid, 
   t1.amount AS basic,
   t2.amount AS TA,
   t3.amount AS DA
FROM
   MyTable t1
   JOIN
   MyTable t2 ON t1.empid = t2.empid
   JOIN
   MyTable t3 ON t1.empid = t3.empid
WHERE
   t1.wagecode = 'basic'
   AND
   t2.wagecode = 'TA'
   AND
   t3.wagecode = 'DA'

Also, welcome to the pitfalls of EAV designs

  • Thanks For the response.. But There is minmum of 80 rows per employee and arount 650 employees are entered... – Sivajith Jun 11 '12 at 9:28
  • your query is working only for single month data..and for emshaving more than one month result is not getting.... – Sivajith Jun 11 '12 at 9:41
  • So you need 80 self joins then to make 80 rows into 80 columns. Simple. Not sure what you mean by "single months data" either – gbn Jun 11 '12 at 10:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.