How to get all 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 2000 Forums

Transact-SQL (2000)

How to get all combinations?

Author

Topic

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2006-04-12 : 14:03:35

Hi Folks,

Based on the following sample table ...

create table #vals (ItemID int, ItemRptAbbr varchar(500), AnsValue varchar(255))

insert into #vals values (1, 'Qst1', 'yes')
insert into #vals values (1, 'Qst1', 'no')
insert into #vals values (22, 'Qst2', 'yes')
insert into #vals values (22, 'Qst2', 'no')
insert into #vals values (9999, 'Qst3', 'yes')
insert into #vals values (9999, 'Qst3', 'no')

... how can I get all possible answer combinations and display them in a pivoted form like this

Qst1 Qst2 Qst3
yes yes yes
yes no no
yes yes no
yes no yes
yes NULL NULL
yes NULL yes
yes NULL no
yes yes NULL
yes no NULL

I'm working with SQL Server 2000.

Thanks - wg

nr
SQLTeam MVY

12543 Posts

Posted - 2006-04-12 : 16:32:57

Do you want no and null for Qst1 as well?
something like

select Qst1, Qst2, Qst3
(select distinct AnsValue from #vals where ItemRptAbbr = 'Qst1' union select null) Qst1
cross join
(select distinct AnsValue from #vals where ItemRptAbbr = 'Qst2' union select null) Qst2
cross join
(select distinct AnsValue from #vals where ItemRptAbbr = 'Qst2' union select null) Qst2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wgpubs
Yak Posting Veteran

67 Posts

Posted - 2006-04-12 : 16:43:33

Yes ... I do want NULL and NO for question one as well.

also, I tried your solution ... but getting several errors.

thanks - wg

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-12 : 18:07:00

modified from nr's code

select Qst1.AnsValue, Qst2.AnsValue, Qst3.AnsValue
from
(
	select distinct AnsValue from #vals where ItemRptAbbr = 'Qst1' union select null
) Qst1
cross join
(	
	select distinct AnsValue from #vals where ItemRptAbbr = 'Qst2' union select null
) Qst2
cross join
(
	select distinct AnsValue from #vals where ItemRptAbbr = 'Qst2' union select null
) Qst3

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources