Have Geek Brain

Retrieve duplicate column names by prefixing a duplicate column name in SQL Server 2005

How can I write a stored procedure in SQL Server 2005 so that I can display duplicate column names by prefixing it?

Example. If I have "Other" as the name of a column belonging to multiple categories mapped to another table containing columns "MyColumn", "YourColumn". I need to join these two tables so that my output is "M_Others" and "Y_Others". I can use case, but I'm not sure about any other duplicate columns in the table. How do I write this dynamically to know the repetitions?

Thanks in advance

0


a source to share


4 answers


You must use aliases in the query projection: (dummy example showing usage)



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


a source


You cannot dynamically change column names without using dynamic SQL.

You must specify them explicitly. There is no way to change "A_Others" or "B_Others" in this request:



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


a source


If the duplicate columns contain the same data (i.e. they are join fields), you shouldn't submit both queries anyway, as this is bad practice and is wasteful for both the server and network resources. You shouldn't use select * in production queries, especially if there are joins. If you write your SQL code correctly, you will be aliased when you go ahead when there are two columns with the same name that mean different things (for example if you joined a person's table twice, once to get the doctor's name and once to get the patient's name). Doing this dynamically from the system tables would not only be inefficient, but it would end up giving you a big security hole depending on how hard you wrote the code.You want to save five minutes or less in development, continually impacting performance for each user and potentially negatively impacting data security. This is what the people in the database call bad.

+1


a source


select n.id_pk, (case where 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 by 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

0


a source






More articles:


All Articles