If you transpose the tables you can join to itself on t1.row < t2.row.
This script worked for me but assumes 1 row (which I doubt is true, but you could select a single row), I also had to add an ID column to transpose upon and a row_Number. Row number is sorted by the column name so if that doesn't work, I don't know.
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('[Test].[dbo].[example]') and
C.name <> 'ID'
for xml path('')), 1, 1, '')
set @query
= 'with t1 AS (select ID, ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, value
from [Test].[dbo].[example]
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv),
t2 AS (select ID, ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, value
from [Test].[dbo].[example]
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv)
SELECT CAST(t1.value AS varchar(14)) + ''_'' + CAST(t2.value AS varchar(14)) AS [concatted]
FROM t1 INNER JOIN t2 ON t1.Row# < t2.Row#
ORDER BY t1.Row# ASC, t2.Row# ASC'
Is there a reason you're doing it in SQL though and not in JMP? Because I find this a LOT easier in JMP (and I feel more robust as well).
nck = nchoosekmatrix(ncols(dt), 2);
mat = dt << Get As Matrix;
nmat = shape(mat[nck], nrows(nck), 2);
dt_new = New Table("Concatted",
New Column("Value1", <<Set Values(nmat[0, 1])),
New Column("Value2", <<Set Values(nmat[0, 2])),
New Column("Concat", <<Formula(char(:Value1)||"_"||char(:Value2))),
);
