My mission is to get top 100 rows from all my tables in a database, and union them into one result output. Since the tables have varying columncount and varying types, the only way I see to do this is to put the row number as columns, and the column name in the first column. Row 1 from all tables will then be represented in column 2 and so forth.
My skills in SQL aren't good enough to figure this one out, so I hope the community can assist me in this.
Here is some code:
--Example table
create table Worker (Id int, FirstName nvarchar(max));
--Some data
insert into Worker values (1,'John'),(2,'Jane'),(3,'Elisa');
--Static pivot example
select * from (select
ROW_NUMBER() over (order by Id) as IdRows, FirstName from worker) as st
pivot
(
max(FirstName) for IdRows in ([1],[2],[3])
) as pt;
--Code to incorporate to get column name on column 1
select name from sys.columns where object_id('Worker') = object_id;
--Cleanup
drop table Worker;
I reckon the static pivot must be dynamic, which is not a problem. The above code is just to create a proof of concept, so I have something to build further on.
End result of query should be like this:
Column 1 2 3
FirstName John Erina Jane
I hope this can be solved without using cursors and temp tables, but maybe that's the way to go?
EDIT: Forgot to mention, I'm using sqlserver (mssql)
EDIT2: I'm not all that good at explaining, so here is some more code to do that job for me. This will add another column to Worker table, and a query to show the desired result. It's the query that has to be "smarter" so it can handle future added tables and columns added. (again, this is a proof of concept. The database has > 200 tables and > 1000 columns)
--Add a column
alter table Worker add LastName nvarchar(max);
--Add some data
update Worker set LastName = 'Smith' where Id = 1;
update Worker set LastName = 'Smith' where Id = 2;
update Worker set LastName = 'Smith' where Id = 3;
--Query to give desired output (top 100, but only 3 rows in this table)
select 'FirstName' as 'Column',* from (select top 100
ROW_NUMBER() over (order by Id) as IdRows, FirstName from worker) as st
pivot
(
max(FirstName) for IdRows in ([1],[2],[3])
) as pt
union
select 'LastName' as 'Column',* from (select top 100
ROW_NUMBER() over (order by Id) as IdRows, LastName from worker) as st
pivot
(
max(LastName) for IdRows in ([1],[2],[3])
) as pt
