I'm not sure this is possible as a single query. It doesn't look like you
can avoid an iterative solution although you could turn it into a
table-valued function. I'm cross-posting to
microsoft.public.sqlserver.programming to see if anyone can come up with
better than this.
(
http://groups.google.com/groups?selm...195b%40posting.
google.com)
CREATE TABLE T(colA VARCHAR(10), colB VARCHAR(10) NOT NULL, PRIMARY KEY
(colA, colB))
INSERT INTO T (colA, colB) VALUES ('94015', '01065')
INSERT INTO T (colA, colB) VALUES ('94016', '01065')
INSERT INTO T (colA, colB) VALUES ('94015', '01085')
INSERT INTO T (colA, colB) VALUES ('94015', '01086')
INSERT INTO T (colA, colB) VALUES ('33383', '00912')
INSERT INTO T (colA, colB) VALUES ('32601', '00912')
/* Additional row makes it a single group: */
INSERT INTO T (colA, colB) VALUES ('32601', '01065')
GO
CREATE FUNCTION TGroupings ()
RETURNS @t TABLE (colA VARCHAR(10) NOT NULL, colB VARCHAR(10) NOT NULL, grp
INTEGER NULL, PRIMARY KEY (colA,colB))
BEGIN
INSERT INTO @t (colA, colB)
SELECT colA, colB
FROM T
DECLARE @grp INTEGER
UPDATE @t
SET @grp = grp = COALESCE(@grp,0) + 1
WHILE @@ROWCOUNT>0
UPDATE T
SET grp =
(SELECT MIN(X.grp)
FROM @t AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)
FROM @t AS T
WHERE EXISTS
(SELECT *
FROM @t AS X
WHERE (T.colB = X.colB OR T.colA = X.colA) AND X.grp<T.grp)
UPDATE T
SET grp =
(SELECT COUNT(DISTINCT grp)
FROM @t AS X
WHERE grp <= T.grp)
FROM @t AS T
RETURN
END
GO
SELECT * FROM TGroupings()
This is the result with your original test-data:
colA colB grp
---------- ---------- -----------
32601 00912 1
33383 00912 1
94015 01065 2
94015 01085 2
94015 01086 2
94016 01065 2
(6 row(s) affected)
And this is it with my extra row added:
colA colB grp
---------- ---------- -----------
32601 00912 1
32601 01065 1
33383 00912 1
94015 01065 1
94015 01085 1
94015 01086 1
94016 01065 1
(7 row(s) affected)
--
David Portas
SQL Server MVP
--
"cjm" <cj****@optonline.net> wrote in message
news:62**************************@posting.google.c om...
"David Portas" <RE****************************@acm.org> wrote in message
news:<jN********************@giganews.com>...
Is a given colB value allowed to belong to more than one group? If so
then John's solution looks good but I wasn't clear on this point from your
sample data.
Here's another solution that may or may not give the result you want
(thanks for the DDL and sample data John). I've added an extra row of sample
data:
...
There is no column called GRP in the table T and I don't want to alter
the table or create a temp table or otherwise UPDATE table T. How
would this be rewritten to return the result set as a query?
You made an important observation that a given colB value should
belong to only ONE group and I want to see the results of your code
with the record you added to the example. Sorry if this is a simple
conversion but I'm still learning.