JSL for SQL

GM

Good morning,

Attached is a script I wrote that accomplishes what I need.

Basically it creates pairs out of sequential (left to right) cells, going from (1,2), (1,3), ..., (1, k), (2, 3), ..., (k-1, k)

Then it stacks them and labels them.

Does anyone know how to accomplish the same in SQL.

Thanks in advance!

Sincerely,

MG

JSLsample.jsl

vince_faller

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))), 
	
);