Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

marked as duplicate by Paul White Sep 28 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.

Note that you can always use, say, MIN() when you're pivoting without really aggregating. As long as there's just one value, MIN() or MAX() will work just as well. Also, it works for string values as described by your title (though contradicted by the body of your question!). – Jon of All Trades Jun 11 '12 at 15:12

2 Answers 2

up vote 0 down vote accepted
SELECT empid , [Basic], [TA],[DA]
SELECT empid, wage, amount
FROM yourtable) up
PIVOT (SUM(amount) FOR wage IN (basic, ta,da)) AS pvt
ORDER BY empid
share|improve this answer
thanks for the replay..i will check it and replay – Sivajith Jun 11 '12 at 10:37
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

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

   t1.amount AS basic,
   t2.amount AS TA,
   t3.amount AS DA
   MyTable t1
   MyTable t2 ON t1.empid = t2.empid
   MyTable t3 ON t1.empid = t3.empid
   t1.wagecode = 'basic'
   t2.wagecode = 'TA'
   t3.wagecode = 'DA'

Also, welcome to the pitfalls of EAV designs

share|improve this answer
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.