June 5, 2012 at 9:33 am
Hi,
I have scenario where table field names are in short terms.
Field actual name is extracted using another table.
Please provide an sql query to view data such that short terms should be replaced with actual name.
e.g.,
Table1
[Id] [Nm]
1 ABC
2 WER
Table2
[Col1] [col2]
Id Identification number
Nm Name
Expected results:
[Identification number] [Name]
1 ABC
2 WER
June 5, 2012 at 9:46 am
hunmunnar (6/5/2012)
Hi,I have scenario where table field names are in short terms.
Field actual name is extracted using another table.
Please provide an sql query to view data such that short terms should be replaced with actual name.
e.g.,
Table1
[Id] [Nm]
1 ABC
2 WER
Table2
[Col1] [col2]
Id Identification number
Nm Name
Expected results:
[Identification number] [Name]
1 ABC
2 WER
Hi and welcome to SSC! That sounds like a terrible design but it can be dealt with. If at all possible change the column names to be what they should be. Column names in sql server can be super huge (there is some actual limit but it so much longer than actually useful I don't bother to memorize it).
For the immediate task I can help but you first need to help me by posting ddl, sample data and desired output. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2012 at 12:04 pm
I agree with Sean here. I can't think of a good reason not to have the columns named what they're supposed to be and not doing that is going to slow the database down and make it harder to maintain. If the issue is the space you want in one of the column names you can do this:
create table Table1 (
[Identification Number] INT NOT NULL,
[Name] Char(3) NOT NULL
);
That being said, I know we don't always have the ability to change the schema so I think this is what you're trying to do. It's not pretty and hasn't been thoroughly tested but should at least be a good start for what you're looking for.
DECLARE @sql VARCHAR(MAX);
SELECT @sql = 'SELECT ' +
(SELECT '[' + Col2 + '] = ' + Col1 + ','
FROM Table2
FOR XML PATH(''))
SELECT @sql = LEFT(@sql,len(@sql)-1) + ' from Table1'
exec(@sql)
June 5, 2012 at 12:15 pm
cfradenburg (6/5/2012)
I agree with Sean here. I can't think of a good reason not to have the columns named what they're supposed to be and not doing that is going to slow the database down and make it harder to maintain. If the issue is the space you want in one of the column names you can do this:
create table Table1 ([Identification Number] INT NOT NULL,
[Name] Char(3) NOT NULL
);
That being said, I know we don't always have the ability to change the schema so I think this is what you're trying to do. It's not pretty and hasn't been thoroughly tested but should at least be a good start for what you're looking for.
DECLARE @sql VARCHAR(MAX);SELECT @sql = 'SELECT ' +
(SELECT '[' + Col2 + '] = ' + Col1 + ','
FROM Table2
FOR XML PATH(''))
SELECT @sql = LEFT(@sql,len(@sql)-1) + ' from Table1'
exec(@sql)
You should use QUOTENAME() instead of hand-coding the delimiters, because QUOTENAME() will automatically handle delimiters embedded in the column names and help to prevent SQL injection attacks.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 5, 2012 at 1:05 pm
drew.allen (6/5/2012)
You should use QUOTENAME() instead of hand-coding the delimiters, because QUOTENAME() will automatically handle delimiters embedded in the column names and help to prevent SQL injection attacks.
Good point. Thanks.
June 6, 2012 at 4:48 am
Thanks for sharing idea.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply