Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
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 thisAllowance1 Allowance2 Allowance3 empid12 13 14 5615 45 89 8978 102 89 20I want to change this as empid allowance amount56 allowance1 1256 allowance2 1356 allowance3 1489 allowance1 1589 allowance2 4589 allowance3 89Like this one I want as outcomePlease help me in getting this output
SwePeso
Patron Saint of Lost Yaks
30421 Posts
Posted - 2008-12-30 : 03:57:42
Use UNPIVOT operator.
DECLARE @Sample TABLE ( Allowance1 INT, Allowance2 INT, Allowance3 INT, EmpID INT )INSERT @SampleSELECT 12, 13, 14, 56 UNION ALLSELECT 15, 45, 89, 89 UNION ALLSELECT 78, 102, 89, 20SELECT u.empid, u.allowance, u.amountFROM @Sample AS sUNPIVOT ( amount FOR allowance IN (s.Allowance1, s.Allowance2, s.Allowance3) ) AS u
E 12°55'05.63"N 56°04'39.26"
visakh16
Very Important crosS Applying yaK Herder
52326 Posts
Posted - 2008-12-30 : 03:58:50
[code]SELECT *FROM (SELECT * FROM YourTable)tUNPIVOT (Amount FOR Allowance IN ([Allowance1],[Allowance2],[Alowance3]))p[/code]
visakh16
Very Important crosS Applying yaK Herder
52326 Posts
Posted - 2008-12-30 : 03:59:16
visakh16
Very Important crosS Applying yaK Herder
52326 Posts
Posted - 2008-12-30 : 04:17:06
Also
SELECT empid,allowance,amountFROM(SELECT empid,Allowance1 AS amount,'allowance1' AS allowance,1 AS OrdFROM TableUNION ALLSELECT empid,Allowance2 AS amount,'allowance2' AS allowance,2 AS OrdFROM TableUNION ALLSELECT empid,Allowance3 AS amount,'allowance3' AS allowance,3 AS OrdFROM Table)tORDER BY empid,Ord
SwePeso
Patron Saint of Lost Yaks
30421 Posts
Posted - 2008-12-30 : 04:18:40
What is the purpose of "ord" column?ORDER BY empid, allowance should do it.E 12°55'05.63"N 56°04'39.26"
visakh16
Very Important crosS Applying yaK Herder
52326 Posts
Posted - 2008-12-30 : 04:22:24
quote:Originally posted by Peso What is the purpose of "ord" column?ORDER BY empid, allowance should do it.E 12°55'05.63"N 56°04'39.26"
i kept just in case real scenartio allowance column names are not allowance1,allowance2,... in which we cant guarantee if they are in sorted order.