Select unique column names from a list of joined tables
This script generate dynamic SQL for any table with similar PK name.
Query:
SET NOCOUNT ON
IF OBJECT_ID (N'dbo.A') IS NOT NULL
DROP TABLE dbo.A
IF OBJECT_ID (N'dbo.B') IS NOT NULL
DROP TABLE dbo.B
IF OBJECT_ID (N'dbo.C') IS NOT NULL
DROP TABLE dbo.C
CREATE TABLE dbo.A (PK_field INT PRIMARY KEY, column1 INT, column2 INT)
CREATE TABLE dbo.B (PK_field INT PRIMARY KEY, column3 INT, column4 INT)
CREATE TABLE dbo.C (PK_field INT PRIMARY KEY, column5 INT, [column 6] INT)
INSERT INTO dbo.A (PK_field, column1, column2)
VALUES (1, 1, 2), (2, 1, 2)
INSERT INTO dbo.B (PK_field, column3, column4)
VALUES (2, 3, 4)
INSERT INTO dbo.C (PK_field, column5, [column 6])
VALUES (1, 5, 6), (3, 5, 6)
DECLARE @SQL NVARCHAR(MAX)
;WITH cte AS
(
SELECT
column_name = '[' + c.name + ']'
, table_name = '[' + s.name + '].[' + o.name + ']'
FROM sys.columns c WITH (NOLOCK)
JOIN sys.objects o WITH (NOLOCK) ON c.[object_id] = o.[object_id]
JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id]
WHERE o.name IN ('A', 'B', 'C')
AND s.name = 'dbo'
AND o.[type] = 'U'
), unicol AS (
SELECT TOP 1 column_name
FROM cte
GROUP BY cte.column_name
HAVING COUNT(cte.column_name) > 1
), cols AS
(
SELECT DISTINCT column_name
FROM cte
), tbl AS
(
SELECT DISTINCT table_name
FROM cte
), rs AS
(
SELECT
tbl.table_name
, column_name = ISNULL(cte.column_name, cols.column_name + ' = NULL')
FROM cols
CROSS JOIN tbl
LEFT JOIN cte ON cols.column_name = cte.column_name AND cte.table_name = tbl.table_name
), rs2 AS (
SELECT uni = ' UNION ALL' + CHAR(13) + 'SELECT ' + STUFF((
SELECT ', ' + rs.column_name
FROM rs
WHERE tbl.table_name = rs.table_name
GROUP BY rs.column_name
ORDER BY rs.column_name
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') +
' FROM ' + table_name
FROM tbl
)
SELECT @SQL = 'SELECT
' + STUFF((
SELECT CHAR(13) + ', ' + ISNULL(unicol.column_name, cols.column_name + ' = MAX(' + cols.column_name + ')')
FROM cols
LEFT JOIN unicol ON cols.column_name = unicol.column_name
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ' ')
+ '
FROM
(' + STUFF((
SELECT CHAR(10) + uni
FROM rs2
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 11, '') + CHAR(13) +
') t
GROUP BY ' + (SELECT column_name FROM unicol)
PRINT @SQL
EXECUTE sys.sp_executesql @SQL
Output:
SELECT
[column 6] = MAX([column 6])
, [column1] = MAX([column1])
, [column2] = MAX([column2])
, [column3] = MAX([column3])
, [column4] = MAX([column4])
, [column5] = MAX([column5])
, [PK_field]
FROM (
SELECT [column 6] = NULL, [column1], [column2], [column3] = NULL, [column4] = NULL, [column5] = NULL, [PK_field] FROM [dbo].[A]
UNION ALL
SELECT [column 6] = NULL, [column1] = NULL, [column2] = NULL, [column3], [column4], [column5] = NULL, [PK_field] FROM [dbo].[B]
UNION ALL
SELECT [column 6], [column1] = NULL, [column2] = NULL, [column3] = NULL, [column4] = NULL, [column5], [PK_field] FROM [dbo].[C]
) t
GROUP BY [PK_field]
Results:
column 6 column1 column2 column3 column4 column5 PK_field
----------- ----------- ----------- ----------- ----------- ----------- -----------
6 1 2 NULL NULL 5 1
NULL 1 2 3 4 NULL 2
6 NULL NULL NULL NULL 5 3
Update in script:
DECLARE @SQL NVARCHAR(2000) -> NVARCHAR(MAX)
Output for your DDL:
SELECT
[blaiseKey_code]
, [bDEM_BOP_q1stParentBornNZ] = MAX([bDEM_BOP_q1stParentBornNZ])
, [bDEM_BOP_q2ndParentBornNZ] = MAX([bDEM_BOP_q2ndParentBornNZ])
, [bDEM_BOP_qHowManyParentBornNZ] = MAX([bDEM_BOP_qHowManyParentBornNZ])
, [bDEM_BOP_qHowManyRaised] = MAX([bDEM_BOP_qHowManyRaised])
, [bDEM_WOR_q2Jobs1HrsIntro] = MAX([bDEM_WOR_q2Jobs1HrsIntro])
, [bDEM_WOR_q2Jobs2HrsIntro] = MAX([bDEM_WOR_q2Jobs2HrsIntro])
, [bDEM_WOR_q2JobsNoHrsIntro] = MAX([bDEM_WOR_q2JobsNoHrsIntro])
, [bDEM_WOR_qEmployArrangement] = MAX([bDEM_WOR_qEmployArrangement])
, [bDEM_WOR_qFeelAboutJob] = MAX([bDEM_WOR_qFeelAboutJob])
, [bDEM_WOR_qJobsNum] = MAX([bDEM_WOR_qJobsNum])
, [bDEM_WOR_qJobsNumNR] = MAX([bDEM_WOR_qJobsNumNR])
, [bDEM_WOR_qMainTasks] = MAX([bDEM_WOR_qMainTasks])
, [bDEM_WOR_qMainTasksNR] = MAX([bDEM_WOR_qMainTasksNR])
, [bDEM_WOR_qOccupation] = MAX([bDEM_WOR_qOccupation])
, [bDEM_WOR_qOccupationNR] = MAX([bDEM_WOR_qOccupationNR])
, [bDEM_WOR_qPaidWorkIntro] = MAX([bDEM_WOR_qPaidWorkIntro])
, [bDEM_WOR_qPermEmployee] = MAX([bDEM_WOR_qPermEmployee])
, [bDEM_WOR_tabDEM_T2_fTotMins] = MAX([bDEM_WOR_tabDEM_T2_fTotMins])
, [fCountryName] = MAX([fCountryName])
, [q3MthsStudy] = MAX([q3MthsStudy])
, [qAge] = MAX([qAge])
, [qAge15OrOver] = MAX([qAge15OrOver])
, [qAgeNR] = MAX([qAgeNR])
, [qAgeRange] = MAX([qAgeRange])
, [qArriveNZMth] = MAX([qArriveNZMth])
, [qArriveNZYr] = MAX([qArriveNZYr])
, [qArriveNZYrNR] = MAX([qArriveNZYrNR])
, [qAwayFromWork] = MAX([qAwayFromWork])
, [qBornInNZ] = MAX([qBornInNZ])
, [qCouldStartLastWk] = MAX([qCouldStartLastWk])
, [qCountryOfBirth] = MAX([qCountryOfBirth])
, [qDidPaidWork] = MAX([qDidPaidWork])
, [qDOB] = MAX([qDOB])
, [qDOBNR] = MAX([qDOBNR])
, [qFamilyBusWork] = MAX([qFamilyBusWork])
, [qHasJobToStart] = MAX([qHasJobToStart])
, [qHighestQual] = MAX([qHighestQual])
, [qHighestQualOth] = MAX([qHighestQualOth])
, [qHighestQualOthNR] = MAX([qHighestQualOthNR])
, [qHighestQualYr] = MAX([qHighestQualYr])
, [qHighestQualYrNR] = MAX([qHighestQualYrNR])
, [qIncTotalAmt] = MAX([qIncTotalAmt])
, [qJobSearchA] = MAX([qJobSearchA])
, [qJobSearchB] = MAX([qJobSearchB])
, [qJobSearchC] = MAX([qJobSearchC])
, [qJobSearchD] = MAX([qJobSearchD])
, [qJobSearchE] = MAX([qJobSearchE])
, [qJobSearchF] = MAX([qJobSearchF])
, [qJobSearchG] = MAX([qJobSearchG])
, [qJobSearchH] = MAX([qJobSearchH])
, [qJobSearchI] = MAX([qJobSearchI])
, [qJobSearchOth] = MAX([qJobSearchOth])
, [qJobSearchOthNR] = MAX([qJobSearchOthNR])
, [qLookedForWork] = MAX([qLookedForWork])
, [qMaoriDescent] = MAX([qMaoriDescent])
, [qNotEligible] = MAX([qNotEligible])
, [qPostSchQual] = MAX([qPostSchQual])
, [qSchQual] = MAX([qSchQual])
, [qSchQualOth] = MAX([qSchQualOth])
, [qSchQualOthNR] = MAX([qSchQualOthNR])
, [qSchQualYr] = MAX([qSchQualYr])
, [qSchQualYrNR] = MAX([qSchQualYrNR])
, [qSex] = MAX([qSex])
, [qThingsWorthwhileScale] = MAX([qThingsWorthwhileScale])
, [qWorkIntro] = MAX([qWorkIntro])
FROM
(
SELECT [bDEM_BOP_q1stParentBornNZ], [bDEM_BOP_q2ndParentBornNZ], [bDEM_BOP_qHowManyParentBornNZ], [bDEM_BOP_qHowManyRaised], [bDEM_WOR_q2Jobs1HrsIntro], [bDEM_WOR_q2Jobs2HrsIntro], [bDEM_WOR_q2JobsNoHrsIntro], [bDEM_WOR_qEmployArrangement], [bDEM_WOR_qFeelAboutJob], [bDEM_WOR_qJobsNum], [bDEM_WOR_qJobsNumNR], [bDEM_WOR_qMainTasks], [bDEM_WOR_qMainTasksNR], [bDEM_WOR_qOccupation], [bDEM_WOR_qOccupationNR], [bDEM_WOR_qPaidWorkIntro], [bDEM_WOR_qPermEmployee], [bDEM_WOR_tabDEM_T2_fTotMins], [blaiseKey_code], [fCountryName], [q3MthsStudy], [qAge], [qAge15OrOver], [qAgeNR], [qAgeRange], [qArriveNZMth], [qArriveNZYr], [qArriveNZYrNR], [qAwayFromWork], [qBornInNZ], [qCouldStartLastWk], [qCountryOfBirth], [qDidPaidWork], [qDOB], [qDOBNR], [qFamilyBusWork], [qHasJobToStart], [qHighestQual], [qHighestQualOth], [qHighestQualOthNR], [qHighestQualYr], [qHighestQualYrNR], [qIncTotalAmt], [qJobSearchA], [qJobSearchB], [qJobSearchC], [qJobSearchD], [qJobSearchE], [qJobSearchF], [qJobSearchG], [qJobSearchH], [qJobSearchI], [qJobSearchOth], [qJobSearchOthNR], [qLookedForWork], [qMaoriDescent], [qNotEligible], [qPostSchQual], [qSchQual], [qSchQualOth], [qSchQualOthNR], [qSchQualYr], [qSchQualYrNR], [qSex], [qThingsWorthwhileScale] = NULL, [qWorkIntro] FROM [dbo].[G_bDEM]
UNION ALL
SELECT [bDEM_BOP_q1stParentBornNZ] = NULL, [bDEM_BOP_q2ndParentBornNZ] = NULL, [bDEM_BOP_qHowManyParentBornNZ] = NULL, [bDEM_BOP_qHowManyRaised] = NULL, [bDEM_WOR_q2Jobs1HrsIntro] = NULL, [bDEM_WOR_q2Jobs2HrsIntro] = NULL, [bDEM_WOR_q2JobsNoHrsIntro] = NULL, [bDEM_WOR_qEmployArrangement] = NULL, [bDEM_WOR_qFeelAboutJob] = NULL, [bDEM_WOR_qJobsNum] = NULL, [bDEM_WOR_qJobsNumNR] = NULL, [bDEM_WOR_qMainTasks] = NULL, [bDEM_WOR_qMainTasksNR] = NULL, [bDEM_WOR_qOccupation] = NULL, [bDEM_WOR_qOccupationNR] = NULL, [bDEM_WOR_qPaidWorkIntro] = NULL, [bDEM_WOR_qPermEmployee] = NULL, [bDEM_WOR_tabDEM_T2_fTotMins] = NULL, [blaiseKey_code], [fCountryName] = NULL, [q3MthsStudy] = NULL, [qAge] = NULL, [qAge15OrOver] = NULL, [qAgeNR] = NULL, [qAgeRange] = NULL, [qArriveNZMth] = NULL, [qArriveNZYr] = NULL, [qArriveNZYrNR] = NULL, [qAwayFromWork] = NULL, [qBornInNZ] = NULL, [qCouldStartLastWk] = NULL, [qCountryOfBirth] = NULL, [qDidPaidWork] = NULL, [qDOB] = NULL, [qDOBNR] = NULL, [qFamilyBusWork] = NULL, [qHasJobToStart] = NULL, [qHighestQual] = NULL, [qHighestQualOth] = NULL, [qHighestQualOthNR] = NULL, [qHighestQualYr] = NULL, [qHighestQualYrNR] = NULL, [qIncTotalAmt] = NULL, [qJobSearchA] = NULL, [qJobSearchB] = NULL, [qJobSearchC] = NULL, [qJobSearchD] = NULL, [qJobSearchE] = NULL, [qJobSearchF] = NULL, [qJobSearchG] = NULL, [qJobSearchH] = NULL, [qJobSearchI] = NULL, [qJobSearchOth] = NULL, [qJobSearchOthNR] = NULL, [qLookedForWork] = NULL, [qMaoriDescent] = NULL, [qNotEligible] = NULL, [qPostSchQual] = NULL, [qSchQual] = NULL, [qSchQualOth] = NULL, [qSchQualOthNR] = NULL, [qSchQualYr] = NULL, [qSchQualYrNR] = NULL, [qSex] = NULL, [qThingsWorthwhileScale], [qWorkIntro] = NULL FROM [dbo].[G_bLWW]
) t
GROUP BY [blaiseKey_code]
Getting unique column names to allow a creation of a view?
It's because both the tables have empNumber column and you are using * to select. There shouldnt be any ambiguity/collisions in column names in the select. Assuming the only common column name is empNumber.
create view vw_Employee as
(
select * from Employees e1 inner join EmpInfo e2
using (empNumber)
)
I'd recommend you to explicitly list all the column names you need.
create view vw_Employee as
(
select e1.empNumber, . . .
from Employees e1 inner join EmpInfo e2
on e2.empNumber = e1.empNumber
)
EDIT:
Based on your comment, try:
create view vw_Employee
as
(
select e1.empNumber,
e1.firstName,
e1.lastName,
e1.ssn,
e1.job,
e1.projectId,
e2.[address],
e2.[state],
e2.zip,
from Employees e1
inner join EmpInfo e2 on e2.empNumber = e1.empNumber
)
Select unique values from the same column in two tables
You can use UNION ALL , group it and use the HAVING clause :
SELECT ID FROM (
SELECT ID FROM Table_1
UNION ALL
SELECT ID FROM Table_2)
GROUP BY ID
HAVING COUNT(*) = 1
Distinct with join that has same column name
To get the distinct list of names you can use union
select name from dogs
union
select name from cats
or if your database supports FULL OUTER JOIN then
select distinct coalesce(d.name,c.name)
from dogs d
full outer join cats c on d.name = c.name
Join Only Unique Values From 2 Tables
Are you trying something like this:
select t1.column2,t2.column2
from table1 t1
inner join
table2 t2 on t1.column2= t2.column2
where t1.column1 in ('c1value2', 'c1value3')
group by t1.column2,t2.column2 ;
create table table1 (
column1 varchar(50),
column2 varchar(50) );
insert into table1 values ( 'c1value1', 'c2value1-1'),
( 'c1value2', 'c2value1-2'),
( 'c1value3', 'c2value1-3');
create table table2 (
column1 varchar(50),
column2 varchar(50) );
insert into table2 values ( 'c1value1', 'c2value1-1'),
( 'c1value1', 'c2value2-1'),
( 'c1value1', 'c2value3-1'),
( 'c1value2', 'c2value1-2'),
( 'c1value3', 'c2value1-3');
Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/70
Edit: If you want the unique values of column2 of both tables based on column1 of table1, you can use a subquery for selecting the distinct values for column1 and use it in the where condition.
select t1.column2,t2.column2
from table1 t1
inner join
table2 t2 on t1.column2= t2.column2
where t1.column1 in (select distinct column1 from table1)
group by t1.column2,t2.column2 ;
Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/72
Related Topics
Strip Non-Numeric Characters from a String
From Keyword Not Found Where Expected (Oracle SQL)
Inserting Multiple Rows with One Insert Command
SQL Server:Return Column Names Based on a Record's Value
Getting an Error When Executing a Dynamic SQL Within a Function (SQL Server)
This SQL 'Order By' Is Not Working Properly
F# Type Provider for SQL in a Class
Interview - Detect/Remove Duplicate Entries
How to Create a Dates Table in Redshift
Multiple Rows into a Single Row and Combine Column SQL
Query JSONb Column Containing Array of JSON Objects
Gaps Between Primary Key Id in SQL Table
Duplicate Groups of Records to Fill Multiple Date Gaps in Google Bigquery