SQL Server - count unique nodes

Asked By Eric on 11-Jun-10 09:56 AM
Hi,

I have a table that looks like this:

Aside1	Aside2	Aside3	Aside4	Aside5	Aside6	Aside7
S0009	S1416
S0009	S5205	S5217
S0009	S5205
S0009	S5552
SWITCH-ASD	S0009	S0004	S0010	S1306
SWITCH-ASD	S0009	S0004	S0010
SWITCH-ASD	S0009	S0004	S0077	S3139
SWITCH-ASD	S0009	S0004	S0077	S3526
SWITCH-ASD	S0009	S0004	S0077
SWITCH-ASD	S0009	S0004	S1507
SWITCH-ASD	S0009	S0004	S1662
SWITCH-ASD	S0009	S0004	S2458
SWITCH-ASD	S0009	S0004	S3518
SWITCH-ASD	S0009	S0004
SWITCH-ASD	S0009	S0015	S0014	S4918
SWITCH-ASD	S0009	S0015	S0014
SWITCH-ASD	S0009	S0015	S1511	S2620
SWITCH-ASD	S0009	S0015	S1511
SWITCH-ASD	S0009	S0015	S1797
SWITCH-ASD	S0009	S0015	S2504
SWITCH-ASD	S0009	S0015	S3136
SWITCH-ASD	S0009	S0015
SWITCH-ASD	S0009	S0659	S1503
SWITCH-ASD	S0009	S0659	S2020	S7700
SWITCH-ASD	S0009	S0659	S2020
SWITCH-ASD	S0009	S0659	S3119
SWITCH-ASD	S0009	S0659
SWITCH-ASD	S0009	S3110
SWITCH-ASD	S0009	S3446
SWITCH-ASD	S0009	S3454
SWITCH-ASD	S0009

This is what you get after a select command on node S0009 existing in any of
the columns.

Now I need to know how many unique nodes there are behind S0009.
Manually counting, this should be: 28.

I have been puzzling all afternoon here, but I cannot figure it out.
Maybe it is not possible but probably my knowledge of SQL is not enough.

Anyone who could give me a hand on this?
Any help is highly appreciated.

rg,
Eric




John Bell replied to Eric on 12-Jun-10 04:03 PM
Eric

Always post DDL and example data as insert statements such as

CREATE TABLE Asides ( Aside1	varchar(20), Aside2	varchar(20),
Aside3	varchar(20), Aside4	varchar(20), Aside5	varchar(20),
Aside6	varchar(20), Aside7 varchar(20) )

INSERT INTO Asides ( Aside1,	Aside2,	Aside3,	Aside4,	Aside5 )
VALUES ('S0009', 'S1416', NULL, NULL, NULL ),
('S0009', 'S5205', 'S5217', NULL, NULL),
('S0009', 'S5205', NULL, NULL, NULL ),
('S0009', 'S5552', NULL, NULL, NULL ),
('SWITCH-ASD', 'S0009', 'S0004', 'S0010', 'S1306'),
('SWITCH-ASD', 'S0009', 'S0004', 'S0010', NULL ),
('SWITCH-ASD', 'S0009', 'S0004', 'S0077', 'S3139'),
('SWITCH-ASD', 'S0009', 'S0004', 'S0077', 'S3526'),
('SWITCH-ASD', 'S0009', 'S0004', 'S0077', NULL ),
('SWITCH-ASD', 'S0009', 'S0004', 'S1507', NULL ),
('SWITCH-ASD', 'S0009', 'S0004', 'S1662', NULL ),
('SWITCH-ASD', 'S0009', 'S0004', 'S2458', NULL ),
('SWITCH-ASD', 'S0009', 'S0004', 'S3518', NULL ),
('SWITCH-ASD', 'S0009', 'S0004', NULL , NULL ),
('SWITCH-ASD', 'S0009', 'S0015', 'S0014', 'S4918'),
('SWITCH-ASD', 'S0009', 'S0015', 'S0014', NULL ),
('SWITCH-ASD', 'S0009', 'S0015', 'S1511', 'S2620'),
('SWITCH-ASD', 'S0009', 'S0015', 'S1511', NULL ),
('SWITCH-ASD', 'S0009', 'S0015', 'S1797', NULL ),
('SWITCH-ASD', 'S0009', 'S0015', 'S2504', NULL ),
('SWITCH-ASD', 'S0009', 'S0015', 'S3136', NULL ),
('SWITCH-ASD', 'S0009', 'S0015', NULL , NULL ),
('SWITCH-ASD', 'S0009', 'S0659', 'S1503', NULL ),
('SWITCH-ASD', 'S0009', 'S0659', 'S2020', 'S7700'),
('SWITCH-ASD', 'S0009', 'S0659', 'S2020', NULL ),
('SWITCH-ASD', 'S0009', 'S0659', 'S3119', NULL ),
('SWITCH-ASD', 'S0009', 'S0659', NULL , NULL ),
('SWITCH-ASD', 'S0009', 'S3110', NULL , NULL ),
('SWITCH-ASD', 'S0009', 'S3446', NULL , NULL ),
('SWITCH-ASD', 'S0009', 'S3454', NULL , NULL ),
('SWITCH-ASD', 'S0009', NULL , NULL , NULL )

I initially believed from your description the following is what you
want:
Eric replied to John Bell on 14-Jun-10 11:07 AM
Thank you for you answer.
I may have counted wrong, so your solutions might be correct.

rg,
Eric