Getting repetitive column names by adding a prefix to the repeated column name in SQL Server 2005

Question

How can I write a stored procedure in SQL Server 2005 so that i can display the repeated column names by having a prefix added to it?

Example: If I have 'Others' as the column name belonging to a multiple categories mapped to another table having columns as 'MyColumn','YourColumn'. I need to join these two tables so that my output should be 'M_Others' and 'Y_Others'. I can use a case but I am not sure of any other repeated columns in the table. How to write that dynamically to know the repetitions ?

Thanks In Advance

Answer 1

You should use aliases in the projection of the query: (bogus example, showing the usage)

SELECT c.CustomerID AS Customers_CustomerID, o.CustomerID AS Orders_CustomerID
FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID

Answer 2

You can't dynamically change the column names without using dynamic SQL.

You have to explicitly alias them. There is no way to change "A_Others" or "B_Others" in this query:

SELECT
    A.Others AS A_Others,
    B.Others AS B_Others
FROM
    TableA A
    JOIN
    TableB B ON A.KeyCol = B.KeyCol

Answer 3

select n.id_pk, (case when groupcount.n_count > 1 then substring(m.name, 1, 1) + '_' + n.name else n.name end) from test_table1 m
left join test_table2 n on m.id_pk = n.id_fk
left join (select name, count(name) as n_count from test_table2 group by name) groupcount on n.name = groupcount.name

Answer 4

If the repeated columns contain the same data (i.e. they are the join fields), you should not be sending both in the query anyway as this is a poor practice and is wasteful of both server and network resources. You should not use select * in queries on production especially if there are joins. If you are properly writing SQL code, you would alias as you go along when there are two columns with the same name that mean different things (for instance if you joined twice to the person table, once to get the doctor name and once to get the patient name). Doing this dynamically from system tables would not only be inefficient but could end up giving you a big security hole depending on how badly you wrote the code. You want to save five minutes or less in development by permanently affecting performance for every user and possibly negatively impacing data security. This is what database people refer to as a bad thing.