Help combining dynamic column pivot tables - SQL does not like duplicate column names

Question

I have a query which joins a fixed column table with 3 dynamic pivots into a single view (or table). Rather than post all of that stuff. Here is the crux of the problem, simplified:

select * from

(select ID, col1, col2, col3 from table1) as t1

left outer join on t1.ID = t2.ID

(select * from table2) as t2 <-- a pivot with dynamic columns

left outer join on t1.ID = t3.ID

(select * from table2) as t3 <-- a pivot with dynamic columns

left outer join on t1.ID = t4.ID

(select * from table2) as t4 <-- a pivot with dynamic columns

As you can see, I am using the wildcard because (due to being based on pivot data) every column except ID may change, and I require ALL the columns. Output per table may be any number of columns AND the column names may change frequently. The ID column is showing up in the results 4 times; and SQL is complaining it does not like duplicate column names. The results display wonderfully in the query window, but will not allow me to create a table or view because of the duplicate ID columns.

How might I use the wildcard, but show ID column just once? Other suggestions not involving specifying static column names and that can be used with the * wildcard?

Answer 1

I guess that at some point, you created the column list for the columns to be generated in those tables. I guess you could find use for them now to replace the stars.

Else? Make sure that each of these tables have a unique name for the ID.

Keep in mind that a dynamic pivot is a non-relational operation and relational databases are not designed to support them. So if you insist, it will be painful and ugly.

Maybe you should backtrack and first assemble all the data you need in relational format, and as the last step produce the dynamic pivot. If you should do that in SQL Server at all. It is after all a presentational device, so the presentation layer may be a better venue.

Answer 2

Hi @Jay Jackson ，

Welcome to Microsoft Q&A!

Agreed with Erland, you could combine all necessary columns from different tables into one query and produce the dynamic pivot at last.

It is also recommended for you to post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

In addition, you could refer below query to produce all columns from different 4 tables and display only one ID column from table1.

DECLARE @SQL NVARCHAR(MAX)  
DECLARE @SQL1 NVARCHAR(MAX)  
DECLARE @SQL2 NVARCHAR(MAX)  
      
SELECT @SQL = STUFF(( SELECT ', t2.' + QUOTENAME(C.COLUMN_NAME)   
FROM INFORMATION_SCHEMA.COLUMNS C   
WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME = 'table2' and C.COLUMN_NAME<>'ID'  
ORDER BY C.ORDINAL_POSITION FOR XML PATH('')),1,2,'')  
  
SELECT @SQL1 = STUFF(( SELECT ', t3.' + QUOTENAME(C.COLUMN_NAME)   
FROM INFORMATION_SCHEMA.COLUMNS C   
WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME = 'table3' and C.COLUMN_NAME<>'ID'  
ORDER BY C.ORDINAL_POSITION FOR XML PATH('')),1,2,'')  
  
SELECT @SQL2 = STUFF(( SELECT ', t4.' + QUOTENAME(C.COLUMN_NAME)   
FROM INFORMATION_SCHEMA.COLUMNS C   
WHERE C.TABLE_SCHEMA = 'dbo' AND C.TABLE_NAME = 'table4' and C.COLUMN_NAME<>'ID'  
ORDER BY C.ORDINAL_POSITION FOR XML PATH('')),1,2,'')  
      
SELECT @SQL = 'SELECT t1.ID,t1.col1,t1.col2,t1.col3,' + @SQL +','+ @SQL1 +','+ @SQL2+  
' FROM table1 t1  
left join table2 t2 on t1.id=t2.id  
left join table3 t3 on t1.id=t3.id  
left join table4 t4 on t1.id=t4.id  
'  
--print  @SQL  
EXECUTE(@SQL)  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.