SQLTeam.com | Weblogs | Forums

SQL to display combinations from 2 columns

Hi Gurus,
i am trying to get 2 columns to display their different occurrences/combinations.
I know that the total combinations should be 45 but it would be excellent if i could get a sql code to list these combinations.
i am attaching the 2 columns and how i'd like to get the combinations displayed.
list1
BUILDING_NAME
LOT_NO
UNIT_NO
UNIT_TYPE
FLOOR_NO
FLOOR_TYPE
HOUSE_SUFFIX
HOUSE_NO
HOUSE_TO
STREET_TYPE

list2
NULL
POPULATED

any help will be very much appreciated.
thank you.

Please explain why? Because those 2 lists are wildly unrelated

hi,
i am not looking for a relation between them. i am looking for the number of combinations which i know they are 45. however, i'd like to see them listed via using sql code.
thanks.

got it. and where do you get this magical number 45 from the data you provided?
Also could you please explain the logic behind why some have POP and some do not.

Hi,
sure, so the answer to your first question. In excel you can use the following function:
=COMBIN(10,2) this is equal to 45 which is the number of combinations i look for without repetitions.
second question. In the real data these attributes either have a value populated or not.
POP = populated
NULL = blank

I hope this answers your question.
thank you.

hi

hope this helps ..

CROSS JOIN ( gives all combinations ) .. please look it up

image

hi

please see below link