Take the 2-minute tour ×
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.

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
    
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]
FROM (
SELECT empid, wage, amount
FROM yourtable) up
PIVOT (SUM(amount) FOR wage IN (basic, ta,da)) AS pvt
ORDER BY empid
GO
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

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

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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