January 31, 2017 at 5:06 pm
I have the following dataset.
DECLARE @T2 TABLE
(
ID INT IDENTITY(1,1)
,Col1 CHAR(1)
,Col2 CHAR(2)
);
INSERT INTO @T2(Col1, Col2)VALUES('A', 'A1'),('A', 'A2'),('A', 'A3'),('B', 'A1'),('B', 'B1'),('B', 'B2'),('C', 'A1'),('C', 'A2'),('C', 'C1'),('C', 'C2');
INSERT INTO @T2
(Col1, Col2)
VALUES
('A', 'A1')
,('A', 'A2')
,('A', 'A3')
,('B', 'A1')
,('B', 'B1')
,('B', 'B2')
,('C', 'A1')
,('C', 'A2')
,('C', 'C1')
,('C', 'C2');
I want results as follows. Is it possible without looping?
Col1 Col2
A A1
B B1
C A2
Basically, each Col1 value only listed once and each Col2 value listed only once.
January 31, 2017 at 6:59 pm
MK Morris - Tuesday, January 31, 2017 5:06 PMI have the following dataset.
DECLARE @T2 TABLE
(
ID INT IDENTITY(1,1)
,Col1 CHAR(1)
,Col2 CHAR(2)
);INSERT INTO @T2(Col1, Col2)VALUES('A', 'A1'),('A', 'A2'),('A', 'A3'),('B', 'A1'),('B', 'B1'),('B', 'B2'),('C', 'A1'),('C', 'A2'),('C', 'C1'),('C', 'C2');
INSERT INTO @T2
(Col1, Col2)
VALUES
('A', 'A1')
,('A', 'A2')
,('A', 'A3')
,('B', 'A1')
,('B', 'B1')
,('B', 'B2')
,('C', 'A1')
,('C', 'A2')
,('C', 'C1')
,('C', 'C2');
I want results as follows. Is it possible without looping?
Col1 Col2
A A1
B B1
C A2
Basically, each Col1 value only listed once and each Col2 value listed only once.
I see what you're asking, but what determines which Col2 gets paired with Col1? What if there's an uneven number of distinct Col1 values and Col2 values?
January 31, 2017 at 9:39 pm
Ed Wagner - Tuesday, January 31, 2017 6:59 PMMK Morris - Tuesday, January 31, 2017 5:06 PMI have the following dataset.
DECLARE @T2 TABLE
(
ID INT IDENTITY(1,1)
,Col1 CHAR(1)
,Col2 CHAR(2)
);INSERT INTO @T2(Col1, Col2)VALUES('A', 'A1'),('A', 'A2'),('A', 'A3'),('B', 'A1'),('B', 'B1'),('B', 'B2'),('C', 'A1'),('C', 'A2'),('C', 'C1'),('C', 'C2');
INSERT INTO @T2
(Col1, Col2)
VALUES
('A', 'A1')
,('A', 'A2')
,('A', 'A3')
,('B', 'A1')
,('B', 'B1')
,('B', 'B2')
,('C', 'A1')
,('C', 'A2')
,('C', 'C1')
,('C', 'C2');
I want results as follows. Is it possible without looping?
Col1 Col2
A A1
B B1
C A2
Basically, each Col1 value only listed once and each Col2 value listed only once.I see what you're asking, but what determines which Col2 gets paired with Col1? What if there's an uneven number of distinct Col1 values and Col2 values?
Is this what your asking?
First, A1 is selected for A for no other reason than it is the first Col2 value available. Since A1 has already been listed, B1 is returned for B. Then last, A2 is returned for C since it is the next value listed for C that has not already been returned.
The best way I can think to describe what I'm after is this - I'm trying to get a distinct list of Col1 values, each paired with the first possible Col2 value that has not already been listed.
February 1, 2017 at 9:05 am
Actually, the problem is a little different from what I original posted. The following is more accurate and includes a solution using a loop. I wanted to see if there is a way to achieve this without looping.
DECLARE @Row INT = 1;
DECLARE @TotalRows INT = 0;
DECLARE @Col1 CHAR(1);
DECLARE @T1 TABLE
(
ID INT IDENTITY(1,1)
,Col1 CHAR(1)
);
INSERT INTO @T1
(Col1)
VALUES
('A')
,('A')
,('B')
,('C')
,('C');
SET @TotalRows = @@ROWCOUNT;
DECLARE @T2 TABLE
(
ID INT IDENTITY(1,1)
,Col1 CHAR(1)
,Col2 CHAR(2)
);
INSERT INTO @T2
(Col1, Col2)
VALUES
('A', 'A1')
,('A', 'A2')
,('A', 'A3')
,('B', 'A1')
,('B', 'B1')
,('B', 'B2')
,('C', 'A1')
,('C', 'A2')
,('C', 'C1')
,('C', 'C2');
DECLARE @T3 TABLE
(
ID INT IDENTITY(1,1)
,Col1 CHAR(1)
,Col2Selected CHAR(2)
);
--Uncomment to see table contents
--SELECT
-- ID
-- ,Col1
--FROM @T1
--ORDER BY ID;
--SELECT
-- ID
-- ,Col1
-- ,Col2
--FROM @T2
--ORDER BY ID;
WHILE @Row <= @TotalRows
BEGIN
SELECT @Col1 = Col1 FROM @T1 WHERE ID = @Row;
INSERT INTO @T3
(Col1, Col2Selected)
SELECT TOP 1 Col1, Col2 FROM @T2 WHERE Col1 = @Col1 AND Col2 NOT IN (SELECT Col2Selected FROM @T3) ORDER BY Col2;
SET @Row = @Row + 1;
END
SELECT * FROM @T3;
February 10, 2017 at 2:09 pm
Just want to know if the following gets the correct result, and if not, what the problem is.
DECLARE @T2 TABLE
(
ID INT IDENTITY(1,1)
,Col1 CHAR(1)
,Col2 CHAR(2)
);
INSERT INTO @T2(Col1, Col2)VALUES('A', 'A1'),('A', 'A2'),('A', 'A3'),('B', 'A1'),('B', 'B1'),('B', 'B2'),('C', 'A1'),('C', 'A2'),('C', 'C1'),('C', 'C2');
INSERT INTO @T2
(Col1, Col2)
VALUES
('A', 'A1')
,('A', 'A2')
,('A', 'A3')
,('B', 'A1')
,('B', 'B1')
,('B', 'B2')
,('C', 'A1')
,('C', 'A2')
,('C', 'C1')
,('C', 'C2');
SELECT DISTINCT Col1, Col2
FROM @T2
ORDER BY Col1, Col2;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply