How to retrieve column names of table based on conditions in data [Solved]

[James Shao]

Hi guys, is there a way to retrieve, for a given record, the names of all the columns where the values of those columns for that record match a given condition? ID   A   B   C   D   E   F   G A1   1   0   1   1   0   1   0 Like if I want to retrieve, for record "A1", all the column names where the value = 1, it would return "A, C, D, F, G". Can this be done in SQL directly? Thanks!!

modified on Friday, January 15, 2010 8:56 PM

[Ashfield]

There is no easy way, you will need to write some code of your own. Gotchas that spring to mind are datatype conversion and accuracy when comparing numbers - floats for example are only approximate. Its not difficult, but does need a bit of planning and a lot of testing.

Bob Ashfield Consultants Ltd Proud to be a Code Project MVP

[Lost User]

Not the nicest looking solution, but it does the trick;

SELECT ID,
CASE WHEN A=1 THEN 'A'
ELSE ''
END +
CASE WHEN B=1 THEN 'B'
ELSE ''
END +
CASE WHEN C=1 THEN 'C'
ELSE ''
END +
CASE WHEN D=1 THEN 'D'
ELSE ''
END +
CASE WHEN E=1 THEN 'E'
ELSE ''
END
FROM [TestTable]

Good luck :)

I are Troll :suss:

[Luc Pattyn]

IMO if you need this, you really should rethink your table design. It probably should have been fewer (two or three) columns and more rows, something like ID, name, value :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

---

Happy New Year to all.
We hope 2010 soon brings us automatic PRE tags!
Until then, please insert them manually.

---

[James Shao]

Thank you Eddy, unfortunately I've got 90 columns for conditional check, so I'll have to improvise.   However, I could see myself using this code potentially in the future with tables that have fewer columns.   :) Many thanks again.

[James Shao]

Unfortunately so far I couldn't think of a better table design without making it even more complicated.   However, I am going to take an alternate route by putting them into an array first, and check for the conditions from there in a loop.   This is currently the best I can come up with.   :)

[Mycroft Holmes]

Go with Luc's suggestion rethink your data structure, this one will drive you nuts. Try this

CREATE TABLE [dbo].[Attr](
[AttrID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] int,
[AttrType] [varchar](255) NULL,
[AttrValue] [varchar](255) NULL,
[Modified] [datetime] NULL,
[ModifiedBy] [varchar](50) NULL)

Where AttrType = your column Name and AttrValue = 1/0. CustomerID is the foreign key to the entity you are applying the attributes to. You would only have 4 rows representing the ACDF colums (0 deemed to not exist)

Never underestimate the power of human stupidity RAH