July 7, 2016 at 2:10 am
Hi,
I am using sys.tables and sys.columns to bring a tablename+'.'+columnname as one column[MasterColumn] , but I have encounter there are few same columns are available in the different tables in the database , when I need to list the [Mastercolumn], I need to handle bring only one tablename and one column by using coalesce for duplicate columns, I am doing this because for automation.
Eg:
current Output:
[tableA].[columnA]
[tableB].[columnB]
[tableC].[columnA]
[tableD].[columnD]
Desired Output
[tableA].[columnA]
[tableB].[columnB]
coalesce([tableA].[columnA], [tableC].[columnA])
[tableD].[columnD]
.
Could someone please give some suggestions how to achieve this.
Many Thanks in Advance.
July 7, 2016 at 2:19 am
COALESCE isn't going to work - it's only any use if any of the inputs are likely to be NULL. Now, where a column name exists in more than one table, how do we choose which table appears in the result set?
John
July 7, 2016 at 2:37 am
I have working on two assumptions:
1) If there are two columns are same, one column will be null in one table, so bring only one column whichever it is not a null value. So I am using here Coalesce to avoid one column.
2) If there is data in two columns, then I need to prioritize one column based on table name, and keep the other column as history data. But I have started working on Second Assumption.
July 7, 2016 at 2:51 am
I'm lost. What does "based on table name" mean? Choose the table whose name comes first alphabetically? Please will you post your whole query so I can understand where the NULLs come in to it?
John
July 7, 2016 at 3:24 am
Hi,
I am using sys.tables and sys.columns to bring a tablename+'.'+columnname as one column[MasterColumn] , but I have encounter there are few same columns are available in the different tables in the database , when I need to list the [Mastercolumn], I need to handle bring only one tablename and one column by using coalesce for duplicate columns, I am doing this because for automation.
Eg:
current Output:
[tableA].[columnA]
[tableB].[columnB]
[tableC].[columnA]
[tableD].[columnD]
Desired Output
[tableA].[columnA]
[tableB].[columnB]
coalesce([tableA].[columnA], [tableC].[columnA])
[tableD].[columnD]
-- If two columns are duplicated in different tables, then one column value will be null in one table.
-- i.e..
TableA TableC
ColumnA ColumnA
15263 Null
-- [ Here columnA is repeated in two tables but, but value will be in only only table column]
Could someone please give some suggestions how to achieve this.
Many Thanks in Advance.
July 7, 2016 at 7:09 am
Are you looking for this function?
July 7, 2016 at 7:38 am
What is the query you are using, I'm kind of curious how you're getting nulls?
July 7, 2016 at 8:42 am
we are getting nulls because, if the columns are in two tables that mean in Application we have moved particular column from one window to other window. When we moved column from one table to another table, we didn't drop the column from the table, because it has records.
July 7, 2016 at 8:59 am
Please send us the query you are running that is generating nulls, you mentioned you were querying sys.tables and sys.columns so I'm very curious how you are getting nulls.
July 7, 2016 at 9:33 am
Part of the problem is that you are conflating table properties with row properties. When you are using sys.tables and sys.columns, you are looking at table properties, but when you talk about NULL values in this case, you are talking about row properties. While the two are related, they are not the same thing. Typically, you would need to use dynamic SQL to relate the two.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply