"Array" in WHERE clause - 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

General SQL Server Forums

New to SQL Server Programming

"Array" in WHERE clause

Author

Topic

sh0wtym3
Starting Member

9 Posts

Posted - 2010-04-28 : 15:34:09

I am trying to construct a SQL statement like such

SELECT * FROM Homes WHERE PropertyType = 'arrayhere'

The "arrayhere" values can be zero to many.

They will be populated by values from a table called PropertyTypes.

The PropertyTypes table will have multiple columns labeled:
"Single Family Home", "Condominium", "Waterfront", etc etc

Basically if there is a "1" in a PropertyTypes column, I want the name of that column to be included in the "arrayhere" section. If there is a "0", skip it.

Is there a way to accomplish this via SQL?

Sachin.Nand

2937 Posts

Posted - 2010-04-28 : 16:00:29

FYI there is no concept of arrays in SQL.
But you can pass a comma seperated values to a query generated like this.


declare @tbl as table(col1 int,col2 int,col3 int)
insert into @tbl
select 1,0,1 union all
select 1,0,1 

select * from @tbl

declare @str as varchar(max)=''
select @str=@str + ','+columns from
(
select columns,MAX(case when col=1 then 1 end)
as status from
(select * from @tbl)u
unpivot
(col for columns in(col1,col2,col3))v
group by columns
)t where status is not null

select STUFF(@str,1,1,'')

--select * from yourtable where column in (select STUFF(@str,1,1,''))

PBUH

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-28 : 16:53:07

How about this:

SELECT * 
FROM Homes 
WHERE PropertyType IN (
	SELECT CASE WHEN "Single Family Home" = 1 
		THEN 'Single Family Home' 
		ELSE NULL END
	FROM PropertyTypes
	UNION 
	SELECT CASE WHEN "Condominium" = 1 
		THEN 'Condominium' 
		ELSE NULL END
	FROM PropertyTypes
	UNION 
	SELECT CASE WHEN "Waterfront" = 1 
		THEN 'Waterfront' 
		ELSE NULL END
	FROM PropertyTypes)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-29 : 03:29:02

If you properly normalised the PropertyTypes to have structures like


PropertyName           value
Single Family Home        1
Condominium               1
Waterfront                1

It would have been easy

SELECT * FROM Homes WHERE PropertyType in (select PropertyName from PropertyTypes where value=1)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources