JSL for SQL

GM — Thu, 06 Jun 2019 13:19:30 GMT

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,

Re: JSL for SQL

vince_faller — Thu, 06 Jun 2019 17:02:51 GMT

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

Re: JSL for SQL

GM — Thu, 06 Jun 2019 17:40:59 GMT

Thanks @vince_faller,

Your JSL approach is defnitley more slick than the double for loops.

Much appreciate for showing the nchoosekmatrix function!

Sincerely,

topic Re: JSL for SQL in Discussions

JSL for SQL

Re: JSL for SQL

Re: JSL for SQL