Technical Article

Excel LET Formula for creating small values tables from a table or range

,

The Excel (365) "LET" formula below will return a formatted SQL Values Table "T1" that can be pasted into SSMS.

=LET(
Vals,Table1,Headers,Table1[#Headers],RowSpacer,CHAR(13)&CHAR(10),TabSpacer,CHAR(9),"SELECT "&RowSpacer&TabSpacer&" ["&TEXTJOIN("],[",FALSE,Headers)&"]"&RowSpacer
&"FROM"&RowSpacer&TabSpacer&"(VALUES "
&TabSpacer&TEXTJOIN(",",FALSE,IF(COLUMN(Vals)=MIN(COLUMN(Vals)),RowSpacer&TabSpacer&"('","'")
&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Vals,"'","''"),CHAR(13),"' + CHAR(13) + '"),CHAR(10),"' + CHAR(10) + '"),"""","'+ CHAR(34)+'")
&IF(COLUMN(Vals)=MAX(COLUMN(Vals)),"')","'"))&RowSpacer&TabSpacer&") "
&"t1 (["&TEXTJOIN("],[",FALSE,Headers)&"])")

This requires your data to be in a list object (Table) named Table1. The column names are taken from the header row "Table1[#Headers]". You can change these to any range though although clearly the header range should be the same width as the table/values range.

You can change the rowSpacer to whatever you want (set to CR and LF in the above "RowSpace,Char(13)&Char(10)". You can also adjust the TabSpacer (set to "CHAR(9)").

The line of the formula that does the substitutions subs out single quotes with double quotes and replaces char(13) and Char(10) with ' + CHAR(13) + '  and ' + CHAR(10) + ' respectively to allow the values to appear on one line.

This still adds double quotes around the text when you copy paste the cell result into SSMS but it still makes for an easy way of shifting a small work table from excel into SSMS. The substitute for double quotes to CHAR(34) stops SSMS adding double double quotes!!

SELECT 
 [A],,[c],[d]
FROM
(VALUES 
('Alpha','11','11','17'),
('Beta','10','19','13'),
('Gamma','10','19','12'),
('Delta','15','16','13'),
('Carriage' + CHAR(10) + 'Return','17','13','15'),
('10','10','15','14'),
('10','18','15','20'),
('19','12','20','15')
) t1 ([A],,[c],[d])
=LET(
Vals,Table1,Headers,Table1[#Headers],RowSpacer,CHAR(13)&CHAR(10),TabSpacer,CHAR(9),"SELECT "&RowSpacer&TabSpacer&" ["&TEXTJOIN("],[",FALSE,Headers)&"]"&RowSpacer
&"FROM"&RowSpacer&TabSpacer&"(VALUES "
&TabSpacer&TEXTJOIN(",",FALSE,IF(COLUMN(Vals)=MIN(COLUMN(Vals)),RowSpacer&TabSpacer&"('","'")
&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Vals,"'","''"),CHAR(13),"' + CHAR(13) + '"),CHAR(10),"' + CHAR(10) + '"),"""","'+ CHAR(34)+'")
&IF(COLUMN(Vals)=MAX(COLUMN(Vals)),"')","'"))&RowSpacer&TabSpacer&") "
&"t1 (["&TEXTJOIN("],[",FALSE,Headers)&"])")

Rate

Share

Share

Rate