Dynamically Transpose 1 Row of Data-CodePudding

I have an existing table called Users, in a SQL Server Database
I wish to be able to generate a table that shows all the Column Headings in the Column 1, and 1 row of sample data in Column 2. Perhaps the Last row?
I need the query to be dynamic, so I can change the table name and capture all COLUMN_NAMES without having to hard-code them

	Table: Users
	UserID	User_Name	Country	Employed
1	E24141	Elon Mosk	US	Y
2	E24142	Bill Jates	US	N
3	E24142	Jeff Lezos	US	N

Query1: Get List of Columns
COLUMN_NAME	SAMPLE
UserID	E24142
User_Name	Jeff Lezos
Country	US
Employed	N

So far I have figured out how to create column 1, to get a list of Column Names as Rows.

SELECT COLUMN_NAME 
FROM ENT_Layer.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Users' AND COLUMN_NAME LIKE '%'

However, I can't find a way to add COLUMN 2, which is the 'Sample data'. I have read about PIVOT & UNPIVOT but I'm not sure that is what I need to do. I suspect I need to dynamically be able to select a names list of Column Names, which i can achieve with the code below, but am unsure of the next step.

Any advice?

DECLARE @Columns as VARCHAR(MAX)

SELECT @Columns =
    COALESCE(@Columns   ', ','')   QUOTENAME(COLUMN_NAME)
FROM (
    SELECT COLUMN_NAME
    FROM ENT_Layer.INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Users' AND COLUMN_NAME LIKE '%'
) AS B

Print @Columns

CodePudding user response：

On second read, if you are looking for just ONE SAMPLE row

Select B.* 
 From  (Select top 1 * from AnyTableOrQuery Order by SomeCol Desc) A
 Cross Apply  (
                 Select [Key]
                       ,Value
                 From OpenJson(  (Select A.* For JSON Path,Without_Array_Wrapper,INCLUDE_NULL_VALUES )  ) 
              ) B

The INCLUDE_NULL_VALUES is optional