Column based query

Hello all,
This one evades but I don't think it's possible. I thought I would
send it out the brain trust.
I have 4 columns that are bits. Is it possible to make a query that
will return the columns where the column is true?
Here is an example:
Col1 Col2 Col3 Col4
t t f f
t f f t
Is it possible to return two separate resultsets that gives me col1,
col in the first query
col 1 and col4 in the second?
"axwack" <axwack@.gmail.com> wrote in message
news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
> Hello all,
> This one evades but I don't think it's possible. I thought I would
> send it out the brain trust.
> I have 4 columns that are bits. Is it possible to make a query that
> will return the columns where the column is true?
> Here is an example:
> Col1 Col2 Col3 Col4
> t t f f
> t f f t
> Is it possible to return two separate resultsets that gives me col1,
> col in the first query
> col 1 and col4 in the second?
Not sure exactly what you're asking for here, but based on my initial
reading it sounds like you want the names of the columns returned. Does
this do what you want? Note that I'll leave all the naming convention and
ANSI (nee ISO) quibbles for Celko to take up with you:
CREATE TABLE #bits (col1 bit,
col2 bit,
col3 bit,
col4 bit);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 1, 0, 0);
INSERT INTO #bits (col1, col2, col3, col4)
VALUES (1, 0, 0, 1);
SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
FROM #bits;
DROP TABLE #bits;
|||On Dec 24, 10:41Xpm, "Mike C#" <x...@.xyz.com> wrote:
> "axwack" <axw...@.gmail.com> wrote in message
> news:5b590e1c-beec-4a84-a38b-c43c67abdf46@.e6g2000prf.googlegroups.com...
>
>
>
>
> Not sure exactly what you're asking for here, but based on my initial
> reading it sounds like you want the names of the columns returned. XDoes
> this do what you want? XNote that I'll leave all the naming convention and
> ANSI (nee ISO) quibbles for Celko to take up with you:
> CREATE TABLE #bits (col1 bit,
> X col2 bit,
> X col3 bit,
> X col4 bit);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 1, 0, 0);
> INSERT INTO #bits (col1, col2, col3, col4)
> VALUES (1, 0, 0, 1);
> SELECT CASE col1 WHEN 1 THEN 'col1 ' ELSE '' END +
> X CASE col2 WHEN 1 THEN 'col2 ' ELSE '' END +
> X CASE col3 WHEN 1 THEN 'col3 ' ELSE '' END +
> X CASE col4 WHEN 1 THEN 'col4 ' ELSE '' END
> FROM #bits;
> DROP TABLE #bits;
Hi that will work...I thought you could get the database to return the
coumns but this actually suits my needs because it is for gui
generation.
|||"axwack" <axwack@.gmail.com> wrote in message
news:c715a12b-8b96-44d5-b14a-f489e774bc48@.j20g2000hsi.googlegroups.com...
> Hi that will work...I thought you could get the database to return the
> coumns but this actually suits my needs because it is for gui
> generation.
Not sure what you mean by "get the database to return the columns", but I
have a strong feeling that you're working here with a table that's not
properly normalized. I'm still not 100% sure on what you're trying to
accomplish, but if you're trying to do what I believe you are try looking at
normalization... you may find a better way to accomplish these tasks.