May 17, 2011 at 7:24 am
Hi All,
I have the below requirement.
create table emp
(
eno int,
ename varchar(30),
age int,
salary int
)
insert into emp
select 1,'aa',25,5000
insert into emp
select 2,'bb',30,8000
insert into emp
select 3,'cc',20,3000
select ename,col,[Value] from
(select eno, ename, age, salary
from emp)p
UNPIVOT
(
[Value] for col in (eno,age,salary))
AS UNPVT
My requirement is,
I have 3 variables like below,
Declare @pos1 int
Declare @pos2 int
Declare @pos3 int
set @Pos1 = 25
set @Pos2 = 35
set @Pos3 = 45
I have to display the final output as below
Expected Output:
aa25 eno1
aa35 age25
aa45 salary5000
bb25 eno2
bb35 age30
bb45 salary8000
cc25 eno3
cc35 age20
cc45 salary3000
current output:
aaeno1
aaage25
aasalary5000
bbeno2
bbage30
bbsalary8000
cceno3
ccage20
ccsalary3000
karthik
May 17, 2011 at 8:45 am
You can certainly obtain the desired output, but since the correlation between the variable and the column name seems rather arbitrary, the following code is completely ad hoc and might not be the best approach to use in your actual situation.
SELECT ename
, CASE col WHEN 'eno' THEN @pos1 WHEN 'age' THEN @pos2 WHEN 'salary' THEN @pos3 END
, col
,[Value]
FROM (
SELECT eno, ename, age, salary
FROM emp
) AS p
UNPIVOT (
[Value]
FOR col IN (eno,age,salary)
) AS UNPVT
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 17, 2011 at 9:41 am
Karthik,
It looks like you're ordering by the value column, and for the first three rows putting in the appropriate variable?
If so, how does this work for you?
DECLARE @Vars TABLE (ID INT, [Value] INT);
INSERT INTO @Vars
SELECT 1, 25 UNION ALL
SELECT 2, 35 UNION ALL
SELECT 3, 45;
;
WITH cte AS
(
select ename,col,[Value], RN = ROW_NUMBER() OVER (PARTITION BY ename ORDER BY [Value]) from
(select eno, ename, age, salary
from emp)p
UNPIVOT
(
[Value] for col in (eno,age,salary))
AS UNPVT
)
SELECT cte.ename, v.value, cte.col, cte.Value
FROM cte
JOIN @Vars v
ON cte.rn = v.ID
Edit: Which returns this data:
ename value col Value
----- ----- ------ -----
aa 25 eno 1
aa 35 age 25
aa 45 salary 5000
bb 25 eno 2
bb 35 age 30
bb 45 salary 8000
cc 25 eno 3
cc 35 age 20
cc 45 salary 3000
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply