Select statement and column listing 4

[navigator703]

I have been searching the normal sites [will remain nameless], but I should have tried here first. Ugh how we realize the best answer is the first answer. I have run the normal search, but haven't found anything near to my question in the forums.

Anyway, for general knowledge purposes how would you select multiple columns as just column result. For example if there a many different ID's [atm, parking, tenant] and I would like to make the return data set equal ID, how would I go about doing this?

Here is the example in question:

select occpt_id, (select o1.cafe_id, o1.tnnt_id, o1.park_id, o1.mortg_ofc_id, o1.atm_id, o1.brn_id from t_occpt o1) as specocc

from t_occpt

This is what I have started with, but have the error

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

~ a journey of a thousand miles must begin with a single step ~

 

[monksnake]

You can convert all columns you want to pull into varchars or chars (if they're not already) then concatenate them:

select occpt_id, (select convert(varchar, o1.cafe_id) + ' ' + convert(varchar, o1.tnnt_id) + ' ' + ....etc from t_occpt o1) as specocc

<.

 

[navigator703]

Thats a good start! Thanks.

select (convert(varchar, o1.cafe_id) + ' ' + convert(varchar, o1.tnnt_id) + ' ' +  convert(varchar, o1.park_id) + ' ' + 
convert(varchar, o1.atm_id) + ' ' +  convert(varchar, o1.mortg_ofc_id) + ' ' + convert(varchar, o1.brn_id)) as specocc 
from t_occpt o1

Now I must determine how to return actual data. Currently all the data is NULL. Any thoughts?

~ a journey of a thousand miles must begin with a single step ~

 

[SQLDenis]

yes because null + something is always null

wrap all of them with COALESCE

example

+ COALESCE(convert(varchar, o1.mortg_ofc_id),'')

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

For each column, put (for example)

convert(varchar, coalesce(o1.cafe_id, ''))

If you have a NULL value, and try to add it to a string, the whole string becomes null. What coalesce will do is replace nulls with an empty string, allowing you to put them together.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

>>convert(varchar, coalesce(o1.cafe_id, ''))

Alex that might to blow up if the cafe_id column is an integer, that is why I used the coalesce around the varchar

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[navigator703]

All the fields are varchar. I believe I will be safe with that. Let me give this a shot!



~ a journey of a thousand miles must begin with a single step ~

 

[AlexCuse]

Yeah I thought about that when I saw your reply. Good thinking

Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

if they vare all varchar then why are you converting?
all you need is this

select coalesce(o1.cafe_id,'') + ' ' +  coalesce(o1.tnnt_id,'') + ' ' +   coalesce(o1.park_id,'') + ' ' + 
 coalesce(o1.atm_id,'') + ' ' +   coalesce(o1.mortg_ofc_id,'') + ' ' +  coalesce(o1.brn_id,'') as specocc 
from t_occpt o1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[navigator703]

You are right. What was I thinking. But it worked. Thanks alot Denis.

~ a journey of a thousand miles must begin with a single step ~

 

[navigator703]

By they way, what does coalesce represent?

~ a journey of a thousand miles must begin with a single step ~

 

[AlexCuse]

What coalesce will do is replace nulls with an empty string, allowing you to put them together.


Ignorance of certain subjects is a great part of wisdom

 

[SQLDenis]

>>By they way, what does coalesce represent?

lookup COALESCE in BOL it is very simillar to ISNULL

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

>> All the fields are varchar.

I may be a little late to the party, but... Since all fields are varchar, you could use:

Set concat_null_yields_null off

By default, this is set to on.

Take a look at this example.

[COLOR=blue]declare[/color] @Temp [COLOR=blue]Table[/color](colA [COLOR=blue]varchar[/color](20), colB [COLOR=blue]varchar[/color](20))

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'a'[/color],[COLOR=red]'b'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'a'[/color],null)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](null,[COLOR=red]'b'[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](null,null)

[COLOR=blue]Select[/color] ColA + ColB
[COLOR=blue]From[/color]   @Temp

[COLOR=blue]Set[/color] concat_null_yields_null [COLOR=blue]off[/color]

[COLOR=blue]Select[/color] ColA + ColB
[COLOR=blue]From[/color]   @Temp

[COLOR=blue]Set[/color] concat_null_yields_null [COLOR=blue]on[/color]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ESquared]

navigator703, it appears that you already have your answer, but my mind went in a different direction. So I present this alternate option to you in case it helps you now or in a future endeavor.

select
   occpt_id,
   specocc.*
from
   t_occpt
   cross join (
      select
         o1.cafe_id,
         o1.tnnt_id,
         o1.park_id,
         o1.mortg_ofc_id,
         o1.atm_id,
         o1.brn_id
      from t_occpt o1
   ) specocc

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[mutley1]

George,

Have a purple thing for that (seeing as no-one has donated to your post so far!). I like that one.

Thx,

M.