Combinations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Combinations

Author

Topic

mahimam_2004
Starting Member

40 Posts

Posted - 2007-04-14 : 13:23:57

Hi,
I have the table:
exexposureRoute: with columns
caseId, exRouteID
200 70
300 71
200 72
200 73
.. ..

Route table:
exRoteID name
70 a
71 b
72 c
73 d
74 e

and so on

Outcome table

OutomceID name

1 Outcome1

2 Outcome2

..

In my result set i want like this:

Routes Outcome1 Outcome2 Outcome3

a

b

c

d

e

f

g

h

a/c

a/c/b

b/c/e

a/e

a/b

a/d

a/b/d

a/b/e

a/d/e

a/b/d/e

b/e

b/d

b/d/e

d/e

I need to get only those combinations not all the Possible combinations.
in the above a/b means i want the casecount for which cases both a and b exists for each outcome type.
For one case there mey be possibilty of more than one route.

How to write the query to get those routes combinations case counts.

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-14 : 14:42:25

You need a CROSSTAB query.

Peter Larsson
Helsingborg, Sweden