Complex SQL query doubt - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complex SQL query doubt

Author

Topic

Invinc4u
Starting Member

5 Posts

Posted - 2009-01-28 : 08:49:22

Hi,

I have a table that has n (n>100) columns.The column names are as follows,say:
a,b,c,d1,d2...dn

I have a mapping for this column names d1..dn in another table which gives the actual column names corresponding to them:

x1-d1
x2-d2
..
..
xn-dn

Using these two tables i have to create a new table that has the following structure:

a,b,c,x1,x2..xn

Can someone guide me how can i go about this?

Thanks,
Invinc4u.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 08:51:11

does the mapping tables contain the values of columns or column names themselves?

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-01-28 : 09:22:50

And are d1..dn of the same datatype?

Invinc4u
Starting Member

5 Posts

Posted - 2009-01-28 : 09:29:09

Hi,

The mapping table contains the column names. The column names of the final table have to be derived from the column values of the mapping table.Ok i will give a more concrete example to illustrate the problem :

Table A :

Column Names
Name
City
Occupation
id1
id2
id3

Table B

Columns

key
name

(values)
key name
id1 Father's name
id2 mother's name
id3 Brother's name

Table C
Columns

Name
City
Occupation
Father's name
Mother's name
Brother's name

This is a very basic example.The problem is that there are more than 100 columns in my table,that's why i have to do this programmatically.Any help would be appreciated.

Thanks,
Invinc4u.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:31:40

why are you storing the column names themselves and mapping them to new column? can you explain the purpose?

Invinc4u
Starting Member

5 Posts

Posted - 2009-01-28 : 09:36:29

Hi bjoerns,

No actually d1..dn are not of the same datatype.Actually the names of columns are such that they reflect the datatype for the same.For example,in the actual problem that i am trying to solve the column names d1..dn are something like :

char_col_1
char_col_2
int_col_1

So to create the final table i should get the mapping from the mapping table to get the actual column name and get the type of the new column in the new table from the old column name.

For example, if the old column name was char_col_1 and the mapping for this was company_name,then in the new table it should create a column company_name with the datatype set to varchar.

Regards,
Invinc4u.

Invinc4u
Starting Member

5 Posts

Posted - 2009-01-28 : 09:38:42

hi visakh16,

The mapping table has been created like that and being used for a long time in our transactional systems.So we cant modify them.That is why i want to create a new table as mentioned above as part of our ETL process.

Invinc4u.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources