Using column name as row value

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using column name as row value

Author

Topic

Osho4U
Starting Member

1 Post

Posted - 2008-12-30 : 03:53:49

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

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	@Sample
SELECT	12,  13, 14, 56 UNION ALL
SELECT	15,  45, 89, 89 UNION ALL
SELECT	78, 102, 89, 20

SELECT	u.empid,
	u.allowance,
	u.amount
FROM	@Sample AS s
UNPIVOT	(
		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)t
UNPIVOT (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,amount
FROM
(
SELECT empid,Allowance1 AS amount,'allowance1' AS allowance,1 AS Ord
FROM Table
UNION ALL
SELECT empid,Allowance2 AS amount,'allowance2' AS allowance,2 AS Ord
FROM Table
UNION ALL
SELECT empid,Allowance3 AS amount,'allowance3' AS allowance,3 AS Ord
FROM Table
)t
ORDER 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources