Click here to Skip to main content
14,694,507 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
problem: Converting rows into column
Explanation: I have table which is returning two values(it will always return 2). e.g values are developer_1, developer_2.

Query is: Select ajxDevlopers From tblDeveloper Where DevId = 5 And CurrntProject = 3 AND Active = 1
it will return 2 records always.
Output: ajxDevloper
---------------------
developer_1
developer_2

desired output:
firstDev SecondDev
developer_1 developer_2

my efforts: i tried pivot-unpivot but no luck. i tried everything i know about sql.
please help
Posted
Comments
King Fisher 25-Nov-14 8:05am
   
Can you show with Sample Records to try
Magic Wonder 25-Nov-14 8:05am
   
kindly provide complete table structure to help you.
Shweta N Mishra 25-Nov-14 8:05am
   
what does Pivot results to you ?
DipAnikap 25-Nov-14 8:11am
   
devID ajxDeveloper
426189 35
426191 25

now i want this

FirstDev SecondDev
35 25

DipAnikap 25-Nov-14 8:12am
   
i am not able to figure this out :(
King Fisher 25-Nov-14 8:24am
   
from you Sample Table :

create table #tbl_test(devID bigint,ajxDeveloper nvarchar(max))
insert into #tbl_test values(426189,'35')
insert into #tbl_test values(426191,'25')

select [426189] as FirstDev ,[426191] as SecondDev from(
select devId,ajxDeveloper from #tbl_test) up pivot (max(ajxDeveloper) for devId in ([426189],[426191])) as pvt
DipAnikap 25-Nov-14 8:28am
   
Thanks .
Yes, this is a feasible one but in that case [426189],[426191 values are fixed, my records are fixed to 2 but values can be vary.
if now its [426189],[426191 it can [426192],[4261101] in some case.
Is it possible?
King Fisher 25-Nov-14 8:31am
   
yes it is Possible
King Fisher 25-Nov-14 8:32am
   
try to use Row_number() for that two rows and pivot that by [1],[2].try it.i gotta go
DipAnikap 25-Nov-14 8:33am
   
Okay thanks
King Fisher 25-Nov-14 10:43am
   
you got that ?
DipAnikap 25-Nov-14 23:49pm
   
Not yet, sorry for my bad sql.
new to it :(
trying

1 solution

   
Comments
Abhinav S 25-Nov-14 22:37pm
   
5!
King Fisher 25-Nov-14 23:10pm
   
good Links 5+ :)
DipAnikap 26-Nov-14 0:40am
   
not able to implement what i want :(
DipAnikap 26-Nov-14 1:04am
   
Its working, but one thing
devID ajxDeveloper
426189 35
426191 25

now output is
426189 426191
35 25
My question is : can i put alias in place of 426189, 426191 ?
Maciej Los 26-Nov-14 1:41am
   
Thank you, Abhinav ;)
Maciej Los 26-Nov-14 1:44am
   
What exactly? It's impossible to get FirstDeveloper, SecodDeveloper, etc.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900