column names

column names

Iam looking for generic solution where i have n columns.
May be we need a permanent table for this kind.

Create table #temp
(
COL1 bit not null,
COL2 bit not null,
COL3 bit not null,
COL4 bit not null,
)
insert into #temp (COL1,COL2,COL3,COL4) values(1,0,1,1)

How can i achive a resultset with the concatenation of column names whose
value is 1
I always select only single row from this table.

COL1+'-'+COL3 +'-'+COL4

since COL1 and COL3 are set to 1.
thanks

column names

correction:
the last line should be
since COL1 and COL3 and COL4 are set to 1
Thanks

Quote:> Iam looking for generic solution where i have n columns.
> May be we need a permanent table for this kind.

> Create table #temp
> (
> COL1 bit not null,
> COL2 bit not null,
> COL3 bit not null,
> COL4 bit not null,
> )
> insert into #temp (COL1,COL2,COL3,COL4) values(1,0,1,1)

> How can i achive a resultset with the concatenation of column names whose
> value is 1
> I always select only single row from this table.

> COL1+'-'+COL3 +'-'+COL4

> since COL1 and COL3 are set to 1.
> thanks

column names

Hi

Try something like:

SELECT LEFT( A, DATALENGTH(A)-1 )
FROM ( SELECT CASE WHEN Col1 = 1 THEN 'Col-' ELSE '' END +
CASE WHEN Col2 = 1 THEN 'Col2-' ELSE '' END +
CASE WHEN Col3 = 1 THEN 'Col3-' ELSE '' END AS A FROM #temp ) T

John

> correction:
> the last line should be
> since COL1 and COL3 and COL4 are set to 1
> Thanks

> > Iam looking for generic solution where i have n columns.
> > May be we need a permanent table for this kind.

> > Create table #temp
> > (
> > COL1 bit not null,
> > COL2 bit not null,
> > COL3 bit not null,
> > COL4 bit not null,
> > )
> > insert into #temp (COL1,COL2,COL3,COL4) values(1,0,1,1)

> > How can i achive a resultset with the concatenation of column names
whose
> > value is 1
> > I always select only single row from this table.

> > COL1+'-'+COL3 +'-'+COL4

> > since COL1 and COL3 are set to 1.
> > thanks

column names

SELECT COALESCE(NULLIF(CAST(col1 AS VARCHAR) + '-', '0-'), '') +
COALESCE(NULLIF(CAST(col2 AS VARCHAR) + '-', '0-'), '') +
COALESCE(NULLIF(CAST(col3 AS VARCHAR) + '-', '0-'), '') +
COALESCE(NULLIF(CAST(col4 AS VARCHAR) + '-', '0-'), '')
FROM #temp;

--
- Anith

Hi,

Based on previous advice from those kind enough to assist me in previous
postings, I have decided to name my table columns using lowercase letters
and numbers and underscores as word seperators. ie.

. my_first_name

Rather than:

. myFirstName

Either way conversion to a more human readable on-screen form will be no
problem. HOWEVER, I want my databases to be both OS and database software
independant. Is there any reason why I couldn't use the second convention?

I understand the Windows ignores case and Linux does not. But surely that
only applies to database filenames?

I will be developing using MS SQL Server 7, and MySQL / Postgres on the
Linux platform.

Thanks,

Lee.