December 30, 2008 at 1:38 am
i all,
I have a requirement and unable to solve this problem. I want ur urgent help. The problem is that there is a table like this
Allowance1 Allowance2 Allowance3 empid
12 13 14 56
15 45 89 89
78 102 89 20
I want to change this as
empid allowance amount
56 allowance1 12
56 allowance2 13
56 allowance3 14
89 allowance1 15
89 allowance2 45
89 allowance3 89
Like this one I want as outcome
Please help me in getting this output
December 30, 2008 at 2:05 am
This is a classis UNPIVOT process. You can review UNPIVOT in Books Online to see how to use it to get what you want. Another option you could use is a UNION ALL, as in:
SELECT a.empid
,'allowance1' As Allowance
,a.allowance1 As Amount
FROM dbo.MyTable
UNION ALL
SELECT b.empid
,'allowance2'
,b.allowance2
FROM dbo.MyTable
...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 30, 2008 at 2:15 am
select empid,'Allowance1',Allowance1 as amount from your_table union all
select empid,'Allowance2',Allowance2 from your_table union all
select empid,'Allowance3', Allowance3 from your_table
order by empid
Failing to plan is Planning to fail
December 30, 2008 at 2:17 am
[sniped]
Failing to plan is Planning to fail
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply