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.
| Author |
Topic |
|
devisetti
Starting Member
30 Posts |
 Posted - 2008-03-28 : 05:51:43
|
| SELECT h1.ftraccode,CASE WHEN FTRAADDRED='A' then h1.ftrascode end as 'From Sec',CASE WHEN FTRAADDRED='r' then h1.ftrascode end as 'To Sec',case when ftraaddred ='A' then h1.ftradesc end as 'From Description',case when ftraaddred ='r' then h1.ftradesc end as 'to Description'from bHISfile h1where h1.ftradesc like 'sw%'order by 1----------------------------------------------------------------clintcode |from_sec | to_sec| from_desc | to_desc---------------------------------------------------------------ABADJ16421 |NULL | MMTEI | NULL | SWITCH TO OAPIFABADJ16421 |OAPIF | NULL | SWITCH FROM MMTEI | NULL2(row)Expected output like this----------------------------------------------------------------clintcode |from_sec | to_sec| from_desc | to_desc---------------------------------------------------------------ABADJ16421 |OAPIF | MMTEI | SWITCH FROM MMTEI | SWITCH TO OAPIF1(row) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-28 : 07:41:57
|
[code]SELECT ftraccode, max(CASE WHEN FTRAADDRED = 'A' then ftrascode else '' end) as 'From Sec', max(CASE WHEN FTRAADDRED = 'r' then ftrascode else '' end) as 'To Sec', max(case when ftraaddred = 'A' then ftradesc else '' end) as 'From Description', max(case when ftraaddred = 'r' then ftradesc else '' end) as 'to Description'from bHISfilewhere ftradesc like 'sw%'order by ftraccode[/code]
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
devisetti
Starting Member
30 Posts |
Posted - 2008-03-30 : 23:38:18
|
| I have use the above code with adding group by but no luck. Its executing but iam getting spaces instead of null. I need to avoid the spaces/nulls.I tried other way of doing by using self joins.but returning multiple values.SELECT ftraccode, max(CASE WHEN FTRAADDRED = 'A' then ftrascode else '' end) as 'From Sec', max(CASE WHEN FTRAADDRED = 'r' then ftrascode else '' end) as 'To Sec', max(case when ftraaddred = 'A' then ftradesc else '' end) as 'From Description', max(case when ftraaddred = 'r' then ftradesc else '' end) as 'to Description'from bHISfilewhere ftradesc like 'sw%'group by ftraccode,ftraaddredorder by ftraccodeAny help on this is appreciated |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-30 : 23:48:12
|
| Use coalesce/Isnull. |
|
|
|
|
|
|
|
|
converting rows to columns using case.Problem with