2

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
Improve this question
2
  • 1
    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!). Commented Jun 11, 2012 at 15:12
  • Check this social.technet.microsoft.com/wiki/contents/articles/… Commented Aug 16, 2017 at 20:44

2 Answers 2

Reset to default
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
Improve this answer
6
  • 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.. Commented Jun 11, 2012 at 10:55
  • For wage in( your data) Commented Jun 11, 2012 at 10:57
  • when i replace it by 1bas i got the error 'Incorrect syntax near '1'.' Commented Jun 11, 2012 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.. Commented Jun 11, 2012 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 Commented Jun 11, 2012 at 11:38
4

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

Improve this answer
3
  • Thanks For the response.. But There is minmum of 80 rows per employee and arount 650 employees are entered... Commented Jun 11, 2012 at 9:28
  • your query is working only for single month data..and for emshaving more than one month result is not getting.... Commented Jun 11, 2012 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 Commented Jun 11, 2012 at 10:04

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.