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

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
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 :(

1 solution

Abhinav S 25-Nov-14 22:37pm
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