cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
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
vince_faller
Super User (Alumni)

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
vince_faller
Super User (Alumni)

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
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