September 11, 2008 at 1:21 pm
Is it possible to select column "names" from a table based on data values. I have a table with an ID column and 20 columns of type bit. I want to select the column names for a single ID if the value of the bit column is "true".
September 11, 2008 at 2:29 pm
You could do that with dynamic SQL. Unpivot the table to turn the columns into rows, then select those with the value you want, then turn that into a list of column names in a dynamic SQL command.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 12, 2008 at 10:55 am
That worked. Thanks for the help.
September 12, 2008 at 11:10 am
You can also do this with XML: Select all of the columns into an XML column/variable then extract the element(column) names of all of the matching elements(columns).
And of course this is a snap in SQL2008 using Sparse Column sets.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply