Read Top 10 ROWS from a table in one database and insert the concatenated results into a single column on another database

Question

I am trying to create a metadata table on one database providing information about the tables on a database. The goal is to show example data (top 10) for the each row in a table within a single column on the metadata table.

Example:

Data Source on SQL Server "Source System"

╔════╦═══════════════════╗
║ ID ║   Name            ║
╠════╬═══════════════════╣
║ 1  ║  John             ║
║ 2  ║  Jane             ║
║ 3  ║  Joe              ║
║ 4  ║  Jimmy            ║
╚════╩═══════════════════╝

---

Metadata Table on SQL Server "Metadata"

╔═══════╦═════════════════════════╗
║ Col   ║         Example         ║
╠═══════╬═════════════════════════╣
║ ID    ║  1, 2, 3, 4             ║
║ Name  ║  John, Jane, Joe, Jimmy ║
╚═══════╩═════════════════════════╝

Any suggestions? Pivot seems to move the rows to columns where as I need many rows to just one column.

Answer 1

LiveDemo

CREATE TABLE #tab(
   ID   INTEGER  NOT NULL PRIMARY KEY 
  ,Name VARCHAR(7) NOT NULL
);
INSERT INTO #tab(ID,Name) VALUES (1,'John');
INSERT INTO #tab(ID,Name) VALUES (2,'Jane');
INSERT INTO #tab(ID,Name) VALUES (3,'Joe');
INSERT INTO #tab(ID,Name) VALUES (4,'Jimmy');


SELECT 
    [Col] = 'Id'
   ,[Example] = STUFF((SELECT CONCAT(',', ID)
               FROM #tab
               FOR XML PATH('')), 1, 1, '')
INTO #Metadata
UNION ALL
SELECT
    [Col] = 'Name'
   ,[Example] = STUFF((SELECT CONCAT(',', Name)
               FROM #tab
               FOR XML PATH('')), 1, 1, '');

SELECT *
FROM #Metadata;

EDIT:

LiveDemo2

No more hardcoded values. Fully dynamic SQL:

CREATE TABLE #tab(
   ID   INTEGER  NOT NULL PRIMARY KEY 
  ,Name VARCHAR(7) NOT NULL
  ,Age INT NOT NULL
);
INSERT INTO #tab(ID,Name, Age) VALUES (1,'John', 14);
INSERT INTO #tab(ID,Name, Age) VALUES (2,'Jane', 23);
INSERT INTO #tab(ID,Name, Age) VALUES (3,'Joe', 34);
INSERT INTO #tab(ID,Name, Age) VALUES (4,'Jimmy', 54);


DECLARE 
  @col_name   NVARCHAR(128) = NULL,
  @table_name NVARCHAR(128) = '#tab',
  @counter    INT           = 1;

SELECT COLUMN_NAME, ORDINAL_POSITION
INTO #columns
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE @table_name + '%';

SELECT @col_name = COLUMN_NAME
FROM #columns
WHERE ORDINAL_POSITION = @counter;

DECLARE @sql NVARCHAR(MAX) = 
N'SELECT 
    [Col] = ''<col_name>''
   ,[Example] = STUFF((SELECT TOP 10 CONCAT('','', <col_name>)
               FROM <table_name>
               FOR XML PATH('''')), 1, 1, '''')';

SET @sql = REPLACE(@sql, '<col_name>', @col_name);
SET @sql = REPLACE(@sql, '<table_name>', @table_name);

SET @counter += 1;


WHILE @counter <= (SELECT COUNT(*) FROM #columns)
BEGIN

  SELECT @col_name = COLUMN_NAME
  FROM #columns
  WHERE ORDINAL_POSITION = @counter;

  SET @sql += 
  N'UNION ALL
    SELECT
    [Col] = ''<col_name>''
   ,[Example] = STUFF((SELECT TOP 10 CONCAT('','', <col_name>)
               FROM <table_name>
               FOR XML PATH('''')), 1, 1, '''')';

  SET @sql = REPLACE(@sql, '<col_name>', @col_name);
  SET @sql = REPLACE(@sql, '<table_name>', @table_name);


  SET @counter += 1;

END

CREATE TABLE #Metadata(Col NVARCHAR(128), Example NVARCHAR(MAX));


INSERT INTO #Metadata(col, Example)
EXEC [dbo].[sp_executesql]
      @sql;

SELECT *
FROM #Metadata;