July 27, 2011 at 6:34 am
I have a table like this one:
CREATE TABLE [dbo].[CH](
[exid1] [int] NULL,
[exid2] [int] NULL,
[exid3] [int] NULL
) ON [PRIMARY]
-----------------------
INSERT INTO [Test].[dbo].[CH] ([exid1],[exid2],[exid3]) VALUES('0','0','3')
INSERT INTO [Test].[dbo].[CH] ([exid1],[exid2],[exid3]) VALUES('0','1','2')
The columns have numbers 0 - 5. Two inserts given.
I want to show all the combinations for the exid as combo:
example results:
exid1 | exid2 | exid3 | combo
0 | 0 | 3 | 003,030,300
0 | 1 | 2 | 012,021,102,120,201,210
So some will have 6 combos and some will only have 3.
Is there a way to do this in sql?
I am trying to find out when my id combination will run out each one is connected to the other in this fashion.
July 27, 2011 at 7:04 am
Maybe the cartisian product:
WITH Numbers(N)
AS
(
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
)
SELECT
N1.N AS exid1
,N2.N AS exid2
,N3.N AS exid3
FROM Numbers N1
CROSS JOIN Numbers N2
CROSS JOIN Numbers N3
July 27, 2011 at 7:10 am
If you MUST use the table structure defined above then you'll need something to define a unique record (PK)
I've added it for demonstration purposes.
DECLARE @CH TABLE
(nDex INT IDENTITY(1,1) PRIMARY KEY
,exid1 INT
,exid2 INT
,exid3 INT)
-----------------------
INSERT INTO @CH ([exid1],[exid2],[exid3]) VALUES('0','0','3')
INSERT INTO @CH ([exid1],[exid2],[exid3]) VALUES('0','1','2')
INSERT INTO @CH ([exid1],[exid2],[exid3]) VALUES('0','9','9')
;WITH exIDs
AS (SELECT nDex, 1 AS pos, exid1 AS val FROM @CH -- we first get your data into a single string of values
UNION ALL
SELECT nDex, 2, exid2 FROM @CH
UNION ALL
SELECT nDex, 3, exid3 FROM @CH)
SELECT DISTINCT
a.val, b.val, c.val
FROM
exIDs AS a
CROSS JOIN exIDs as b
CROSS JOIN exIDs as c
WHERE
a.nDex = b.nDex
AND
a.nDex = c.nDex
AND
a.pos <> b.pos --- now keep a value from appearing more than once in a set
AND
a.pos <> c.pos
AND
b.pos <> c.pos
If you are not restricted to the structure you posted, then a version of Ken's solution should work for you. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 27, 2011 at 7:11 am
Thats great for the combinations but i do not know what they go to.
some select or what ever would show:
exid1 | exid2 | exid3 | combo
0 | 0 | 3 | 003,030,300
July 27, 2011 at 7:23 am
bopeavy (7/27/2011)
Thats great for the combinations but i do not know what they go to.some select or what ever would show:
exid1 | exid2 | exid3 | combo
0 | 0 | 3 | 003,030,300
Bo: Having also seen your question about detecting patterns using SQL, I would like to suggest that you spend a little more time thinking about the definition of your problem, how to state it with precision. I still don't know what you are asking for in the way of results, because I can't see how your example above squares with the expected results you first posted.
Just asking for "combinations" or "patterns" is too imprecise to code to. I read somewhere that a problem correctly stated is a problem half-solved. Do yourself a favor and spend more time thinking about what should and should not be included as a result set and why. If you can explain the entire problem to everyone here on the first try, you will get your solution much quicker. 🙂
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 27, 2011 at 7:30 am
Dixie
I understand what your saying and it still the same as for the other was a different problem all together. I will revise a little.
I have a table like this one:
CREATE TABLE [dbo].[CH](
[exid1] [int] NULL,
[exid2] [int] NULL,
[exid3] [int] NULL
) ON [PRIMARY]
-----------------------
INSERT INTO [Test].[dbo].[CH] ([exid1],[exid2],[exid3]) VALUES('0','0','3')
INSERT INTO [Test].[dbo].[CH] ([exid1],[exid2],[exid3]) VALUES('0','1','2')
The columns have numbers 0 - 5. Two inserts given.
I want to show all the combinations for the exid as combo:
example results:
exid1 | exid2 | exid3 | combo
0 | 0 | 3 | 003,030,300
0 | 1 | 2 | 012,021,102,120,201,210
So some will have 6 combos and some will only have 3.
Is there a way to do this in sql?
I am trying to find out when my id combination will run out each one is connected to the other in this fashion.
July 27, 2011 at 7:43 am
Maybe this one?
SELECT
exid1, exid2, exid3,
COALESCE([1], '') + COALESCE(',' + [2], '') + COALESCE(',' + [3], '') + COALESCE(',' + [4], '') + COALESCE(',' + [5], '') + COALESCE(',' + [6], '') combo
FROM
dbo.CH
CROSS APPLY
(
SELECT
ROW_NUMBER() OVER (ORDER BY (SELECT 0)) rown,
CAST(id1 AS CHAR(1)) + CAST(id2 AS CHAR(1)) + CAST(id3 AS CHAR(1)) combi
FROM
(
SELECT exid1 id1, exid2 id2, exid3 id3 UNION
SELECT exid1, exid3, exid2 UNION
SELECT exid2, exid1, exid3 UNION
SELECT exid2, exid3, exid1 UNION
SELECT exid3, exid1, exid2 UNION
SELECT exid3, exid2, exid1
) U
) X
PIVOT
(
MAX(combi) FOR rown IN ([1], [2], [3], [4], [5], [6])
) P
July 27, 2011 at 7:46 am
Peter Brinkhaus
Thanks exactly what I was looking for...:-D
July 27, 2011 at 7:47 am
bopeavy (7/27/2011)
I have a table like this one:
CREATE TABLE [dbo].[CH](
[exid1] [int] NULL,
[exid2] [int] NULL,
[exid3] [int] NULL
) ON [PRIMARY]
-----------------------
INSERT INTO [Test].[dbo].[CH] ([exid1],[exid2],[exid3]) VALUES('0','0','3')
INSERT INTO [Test].[dbo].[CH] ([exid1],[exid2],[exid3]) VALUES('0','1','2')
The columns have numbers 0 - 5. Two inserts given.
I want to show all the combinations:
example results:
0 1 2 , 0 2 1 , 1 0 2 , 1 2 0 , 2 0 1 , 2 1 0
0 0 1 , 0 3 0 , 3 0 0
So some will have 6 combos and some will only have 3.
Is there a way to do this in sql?
I don't understand this line in the original post:
0 0 1 , 0 3 0 , 3 0 0
because you couldn't get that combination from eith of the two rows you provided as samples.
Also, I assume this ...
bopeavy (7/27/2011)
Thats great for the combinations but i do not know what they go to.
... means you want the orignal line (or PK that matter) would appear in the result set. In that case, simply add it to the result set and use basic concatenation to get the output your asking for.
This will get you close ....
DECLARE @CH TABLE
(nDex INT IDENTITY(1,1) PRIMARY KEY
,exid1 INT
,exid2 INT
,exid3 INT)
-----------------------
INSERT INTO @CH ([exid1],[exid2],[exid3]) VALUES('0','0','3')
INSERT INTO @CH ([exid1],[exid2],[exid3]) VALUES('0','1','2')
INSERT INTO @CH ([exid1],[exid2],[exid3]) VALUES('0','9','9')
;WITH exIDs
AS (SELECT nDex, 1 AS pos, exid1 AS val FROM @CH -- we first get your data into a single string of values
UNION ALL
SELECT nDex, 2, exid2 FROM @CH
UNION ALL
SELECT nDex, 3, exid3 FROM @CH)
SELECT DISTINCT
a.nDex, x.exid1, x.exid2, x.exid3
,CAST(a.val AS VARCHAR)
+ CAST(b.val AS VARCHAR)
+ CAST(c.val AS VARCHAR)
FROM
exIDs AS a
CROSS JOIN exIDs as b
CROSS JOIN exIDs as c
INNER JOIN @CH as x
ON a.nDex = x.nDex
WHERE
a.nDex = b.nDex
AND
a.nDex = c.nDex
AND
a.pos <> b.pos --- now keep a value from appearing more than once in a set
AND
a.pos <> c.pos
AND
b.pos <> c.pos
The rest of what you want is display related. The results ARE THERE in the original answer, you just have to slow down and look at it. :w00t:
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 27, 2011 at 7:50 am
Jason
I don't understand this line in the original post:
0 0 1 , 0 3 0 , 3 0 0
Your are correct it was a typo I am sorry, Dixie pointed that out.
July 27, 2011 at 7:57 am
Thanks CELKO.
As Peter had already answered my question and Dixie and Jason pointed out the errors of my first thought process.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply