SQL Server - Column Values - Asked By Sreelatha Devi on 28-Feb-12 01:21 AM

Hi,
Here the example of the tables :

Example:
Table A: Columns are :
S.No     Sd_name    desc_name.
1       aaa       Human Resources
2       bbb       Finance
3       ccc        Health Insurance

Table Human Resources: Columns are
__________________________________________________________________
Domain     Address      Ph no      Designation     
Payroll     Hyderabad    11111      Sr.HR
admin     Chennai      22222      Jr. Hr
____________________________________________________________________
Table Finance: Columns are:
____________________________________________________________________
Domain                  Address     Ph.No      Designation                 E-mail
Insurance                Kolkata     33333      Finace consultant     abc@gmail.com
credits process     mumbai      4444        Sr.Consultant        aaa@gmail.com

Through desc_name column name i need to get all column values of Human resources , finace , health insurance
How to write a query for this?


Web Star replied to Sreelatha Devi on 28-Feb-12 01:50 AM
First of all if your table having different number of column than its not logical to get all columns of all table in single result set as you needed.
If you want to get all column of all table as different result set than that is meaningfull and for that you need to get use one loop in stored proc
step 1:Loop start from 1 to row count of your table A
step 2: get the desc_name of current row and than stored it in array with current index
step 3: end your loop here
now all the table name you have in an array and you will write select query with combination of all table in array in loop or without loop its matter is what you want to use it.

hope this helps you

D Company replied to Sreelatha Devi on 28-Feb-12 01:57 AM
The simplest way of doing this is add one more column in all table as id and make it common column for atleast two table than inner join these tables as bellos

Select comns from table1 inner join  table2 on commoncollmnoftable1=commoncollmnoftable2
inner join table3 on commoncollmnoftable2=commoncollmnoftable3

which will give u all the columns from all tables,and with no redendancu of data

Hope it helps
Regards
Chintan Vaghela replied to Sreelatha Devi on 28-Feb-12 02:58 AM

Hello,

First distinct the your select query result and then pass each field value through this SP.

/****** Script for SelectTopNRows command from SSMS  ******/

SET ANSI_NULLS ON

 

GO

 

SET QUOTED_IDENTIFIER ON

 

GO

 

CREATE PROCEDURE Dynamic_SP

 

    @Table_Name sysname

 

AS

 

BEGIN

 

    SET NOCOUNT ON;

   

   

 

    DECLARE @DynamicSQL nvarchar(4000)

 

    SET @DynamicSQL = N'SELECT * FROM ' + @Table_Name

 

    EXECUTE sp_executesql @DynamicSQL

 

END

 

GO

 

Hope this is helpful !

Thanks