0

I have a set of columns and data types generated by the following query:

SELECT COLUMN_NAME, DATA_TYPE
INTO #table_checks
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable'

e.g. (using publicly available UK postcode and census data):

COLUMN_NAME DATA_TYPE
pcd7    varchar
pcd8    varchar
pcds    varchar
dointr  int
doterm  int
usertype    int
oa11    varchar
lsoa11cd    varchar
msoa11cd    varchar
ladcd   varchar
lsoa11nm    varchar
msoa11nm    varchar
ladnm   varchar
ladnmw  varchar
FID int

I want to create a column "TOPROW_VALUE" that just gives the first row's value for each column (as an example of the data contained in the column). I was thinking of doing:

SELECT TOP 1 * FROM dbo.mytable

which gives e.g.:

pcd7    pcd8    pcds    dointr  doterm  usertype    oa11    lsoa11cd    msoa11cd    ladcd   lsoa11nm    msoa11nm    ladnm   ladnmw  FID
B29 4NB B29  4NB    B29 4NB 198704  198812  1   E00048118   E01009504   E02001935   E08000025   Birmingham 109D Birmingham 109  Birmingham  NULL    66199

And then transposing/pivoting the result so that it has the fields "COLUMN_NAME" and "TOPROW_VALUE", then joining to #table_checks e.g.

COLUMN_NAME DATA_TYPE   TOPROW_VALUE
pcd7    varchar B29 4NB
pcd8    varchar B29  4NB
pcds    varchar B29 4NB
dointr  int 198704
doterm  int 198812
usertype    int 1
oa11    varchar E00048118
lsoa11cd    varchar E01009504
msoa11cd    varchar E02001935
ladcd   varchar E08000025
lsoa11nm    varchar Birmingham 109D
msoa11nm    varchar Birmingham 109
ladnm   varchar Birmingham
ladnmw  varchar NULL
FID int 66199

However, I want to be able to quickly reuse this code for ~60 different tables with different column names, most of which are much wider than this example. Currently I'm just manually copying and transposing in Excel, but surely there must be an easier and automatable way?

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Mel
  • 700
  • 6
  • 31
  • Does [this](https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) help you? The answer is for a slightly different question, but it contains a generic version with dynamic sql to transpose a table. – DerMaddi Feb 26 '21 at 14:14
  • I tried the various answers here (modified for my own table) but I either get an empty result or an error because I don't have CREATE PROCEDURE permissions on this database. – Mel Feb 26 '21 at 14:26

0 Answers0