CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
GeoVIP
GeoVIP

Reputation: 1564

How to select different data for each column in table

Have query like: 

declare @type int = 1

select cf.id, cf.name 
from dbo.tbl1 cf
inner join dbo.tbl2 ct on cf.id = ct.field_id and ct.[type_id] = @type

Response is:

ID   NAME
1   number
2   city
4   location
5   username

I want add one field in response which I must get for each name from select something like:

when name = number then select number from myTbl
when name = city then select name from region_tbl
when name = location then select name from location_tbl
when name = username then select user from user_tbl

and response must be like :

ID   NAME      Field
1   number     1        
2   city       Paris
4   location   some_location
5   username   admin

Is it possible to do something like this ?

Or i can add in table one more column and response will be like :

ID   NAME     command
1   number    select number from myTbl
2   city      select name from region_tbl
4   location  select name from location_tbl
5   username  select user from user_tbl

and dynamically execute commands, response must be like :

ID   NAME      command
1   number     1        
2   city       Paris
4   location   some_location
5   username   admin

Upvotes: 1

Views: 55

Answers (1)

Mahesh
Mahesh

Reputation: 8892

You can use the CASE WHEN THEN for getting the desired result with the use of subquery

If your datatype is different for each table column then you might have to cast the subquery result to one uniform type like VARCHAR

   select cf.id, cf.name 
   thirdColumn = CASE 
                  WHEN cf.name = 'number' THEN CAST((select TOP 1 number from myTbl) AS VARCHAR)
                  WHEN cf.name = 'city' THEN CAST((select TOP 1 name from region_tbl) AS VARCHAR)
                  WHEN cf.name = 'location' THEN CAST((select TOP 1 name from location_tbl) AS VARCHAR)
                  WHEN cf.name = 'username' THEN CAST((select TOP 1 user from user_tbl) AS VARCHAR)
  from dbo.tbl1 cf
  inner join dbo.tbl2 ct on cf.id = ct.field_id and ct.[type_id] = @type

NOTE - The sub-queries without using TOP or WHERE condition can cause you to give the multiple row's as return that can cause the error.So adjust the sub-queries to always return 1 row.

Upvotes: 2

Related Questions