Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
GM
GM
Level III

JSL for SQL

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
vince_faller
Super User

Re: JSL for SQL

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))), 
	
);
Vince Faller - Predictum

View solution in original post

2 REPLIES 2
Highlighted
vince_faller
Super User

Re: JSL for SQL

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))), 
	
);
Vince Faller - Predictum

View solution in original post

Highlighted
GM
GM
Level III

Re: JSL for SQL

Thanks @vince_faller,

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

Much appreciate for showing the nchoosekmatrix function!

Sincerely,

MG

Article Labels

    There are no labels assigned to this post.