CODEWITHSUNDEEP
sqlsql-server-2008
Racer SQL
Racer SQL

Reputation: 217

how to select, with 3 queries, into 3 columns

How can I use this:

select name from [linkedserver3].master.sys.databases
select name from [linkedserver4].master.sys.databases
select name from [linkedserver5].master.sys.databases

to make this:

pivot?

Is there a way to use a pivot to do this?

I was trying to create a temp table, but with syntax errors (obviously):

create table #temp
    (SQL3 NVARCHAR(200),
     SQL4 NVARCHAR(200),
     SQL5 NVARCHAR(200)
     )

GO

INSERT INTO #temp   (SQL3)
select name from [linkedserver3].master.sys.databases,NULL,NULL
GO

INSERT INTO #temp   (SQL4)
NULL,select name from [linkedserver4].master.sys.databases,NULL
GO

INSERT INTO #TEMP (SQL5)
NULL,NULL,select name from [linkedserver5].master.sys.databases
GO

DROP TABLE #TEMP

Is there a way to PIVOT and then UNPIVOT?

Upvotes: 1

Views: 95

Answers (1)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can number rows for each select and then do a full join:

;WITH cte1 AS(SELECT name, ROW_NUMBER() OVER(ORDER BY name) rn FROM [linkedserver3].master.sys.databases),
      cte2 AS(SELECT name, ROW_NUMBER() OVER(ORDER BY name) rn FROM [linkedserver4].master.sys.databases),
      cte3 AS(SELECT name, ROW_NUMBER() OVER(ORDER BY name) rn FROM [linkedserver5].master.sys.databases)
SELECT * FROM cte1 c1
FULL JOIN cte2 c2 ON c1.rn = c2.rn
FULL JOIN cte3 c3 ON c1.rn = c3.rn OR c2.rn = c3.rn

Upvotes: 3

Related Questions