Help on query - produce permutation combination
-
Hi There,
I need a help on query to produce permutation combination.
declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI')
declare @t1 as table (tab varchar(100))
insert into @t1 values ('I')
insert into @t1 values ('U')
from the above I need following output (attached output),
Thanks in advance
-
squvi.87 (6/4/2015)
Hi There,I need a help on query to produce permutation combination.
declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI')
declare @t1 as table (tab varchar(100))
insert into @t1 values ('I')
insert into @t1 values ('U')
from the above I need following output (attached output),
Thanks in advance
Quick suggestion, use dynamic SQL
😎
USE tempdb;GO
SET NOCOUNT ON;
DECLARE @TABLE_STR NVARCHAR(MAX) = N'';
declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI') ;
IF OBJECT_ID(N'dbo.t1') IS NOT NULL DROP TABLE dbo.t1;
CREATE TABLE dbo.t1 (tab varchar(100))
insert into dbo.t1 values ('I'),('U') ;
SELECT @TABLE_STR = '
IF OBJECT_ID(N''tempdb..#PERMUTE'') IS NOT NULL DROP TABLE #PERMUTE;
CREATE TABLE #PERMUTE ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ' +
( SELECT
N',[' + T.tab + N'] CHAR(1) NOT NULL'
FROM @t2 T
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)')
+ N' );
INSERT INTO #PERMUTE
SELECT
T1.tab
,T2.tab
,T3.tab
,T4.tab
FROM t1 T1,t1 T2,t1 T3,t1 T4
ORDER BY T1.tab
,T2.tab
,T3.tab
,T4.tab
SELECT
*
FROM #PERMUTE;
';
EXEC (@TABLE_STR);
Results
ID V VL 1099 VOI----------- ---- ---- ---- ----
1 I I I I
2 I I I U
3 I I U I
4 I I U U
5 I U I I
6 I U I U
7 I U U I
8 I U U U
9 U I I I
10 U I I U
11 U I U I
12 U I U U
13 U U I I
14 U U I U
15 U U U I
16 U U U U
-
Quick suggestion, use dynamic SQL
😎
USE tempdb;GO
SET NOCOUNT ON;
DECLARE @TABLE_STR NVARCHAR(MAX) = N'';
declare @t2 as table (tab varchar(100))
insert into @t2 values ('V')
insert into @t2 values ('VL')
insert into @t2 values ('1099')
insert into @t2 values ('VOI') ;
IF OBJECT_ID(N'dbo.t1') IS NOT NULL DROP TABLE dbo.t1;
CREATE TABLE dbo.t1 (tab varchar(100))
insert into dbo.t1 values ('I'),('U') ;
SELECT @TABLE_STR = '
IF OBJECT_ID(N''tempdb..#PERMUTE'') IS NOT NULL DROP TABLE #PERMUTE;
CREATE TABLE #PERMUTE ( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ' +
( SELECT
N',[' + T.tab + N'] CHAR(1) NOT NULL'
FROM @t2 T
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)')
+ N' );
INSERT INTO #PERMUTE
SELECT
T1.tab
,T2.tab
,T3.tab
,T4.tab
FROM t1 T1,t1 T2,t1 T3,t1 T4
ORDER BY T1.tab
,T2.tab
,T3.tab
,T4.tab
SELECT
*
FROM #PERMUTE;
';
EXEC (@TABLE_STR);
Results
ID V VL 1099 VOI----------- ---- ---- ---- ----
1 I I I I
2 I I I U
3 I I U I
4 I I U U
5 I U I I
6 I U I U
7 I U U I
8 I U U U
9 U I I I
10 U I I U
11 U I U I
12 U I U U
13 U U I I
14 U U I U
15 U U U I
16 U U U U
Works fine.. new learning from you
-
Dynamic SQL isn't even necessary. In fact, the table of field names isn't really needed either...
Try this on for size:
DECLARE @t1 AS TABLE (
tab varchar(100)
);
INSERT INTO @t1 VALUES ('I');
INSERT INTO @t1 VALUES ('U');
SELECT T.tab AS V, U.tab AS VL, V.tab AS [1099], W.tab AS VOI
FROM @t1 AS T, @t1 AS U, @t1 AS V, @t1 AS W
ORDER BY T.tab, U.tab, V.tab, W.tab;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply