Get Column Names where value =

[NickyJay]

i Guys, wonder if anyone can help!

I think its probably really simple, but cant for the life of me see how to do it - i'm pretty new to sql server too, we're useing 2005.

Basically i want to run a query that will pull out all column names from a table where the value (either 1 or 0) = 1. I only want the column names where the value matches.

Can anyone help?

Thanks!

Nic

 

[mrdenny]

In order to do something along those lines you'll have to use dynamic SQL. It will be a very costly query to run against the SQL Server.

If you don't want to use dynamic SQL (and it is always recommended to not use dynamic SQL), then you'll need to write a seperate query for each column in the table.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[r937]

if there are multiple columns that can be either 0 or 1, should these perhaps be multiple rows in a one-to-many table? then the query would be trivial

i'm suggesting that perhaps your table needs to be normalized

r937.com | rudy.ca

 

[NickyJay]

Hiya, thanks for responding guys

Wish i had the luxury to normalize the table but unfortunatly it has to be the way it is as a program we have relies on the output from this table

where would i start with dynamic sql?

thanks!

Nic

 

[r937]

how many columns?

r937.com | rudy.ca

 

[NickyJay]

there are 57 columns!

 

[r937]

okay, let's imagine that this table has a primary key and then 57 columns consisting of 1s and 0s

[tt]pkey c01 c02 c03 ... c56 c57
todd 1 0 1 ... 0 1
fred 1 1 0 ... 1 1
biff 0 0 1 ... 0 0
zack 0 1 0 ... 1 0[/tt]

you said you wanted only those columns where the value is 1

what exactly is the output supposed to look like?

r937.com | rudy.ca

 

[NickyJay]

the output i need is just a list of the column names where the value is one, the rest is going to be formatted with php...

ie my query results need to be something like:

c1,c3,c6,c11,c12,c14,c17...

 

[r937]

so you want only one row of output? why did you skip c02?

r937.com | rudy.ca

 

[chrissie1]

I think this is a formatting issue and you should do it all in PHP just get the complete row and do the rest in PHP. I even think it will be quicker.

Christiaan Baes
Belgium

"My old site" - Me

 

[mrdenny]

Agreed. This is definetly looking like a UI issue.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com