column names

column names

Post by Suni » Wed, 18 Jun 2003 20:22:49



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

Post by Suni » Wed, 18 Jun 2003 20:32:41


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

Post by John Bel » Wed, 18 Jun 2003 21:02:24


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

Post by Anith Se » Wed, 18 Jun 2003 21:16:58


How about:

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
( Please reply to newsgroups only )

 
 
 

1. Column Name Naming.

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.

2. SELECT Takes Long Time to Execute]

3. HHHHelp (Table Names and Column Names)

4. Checking if Network is there...

5. Result Column Names to include table name!

6. Plexus stuff ??!!

7. table names and column names w/ type?

8. Event Monitoring - A Curious Problem

9. Table names into column names

10. When comparing a field name with a column name of more than one word

11. HELP: Finding index names in sysindexes using a column name

12. Naming Standards for column names in Views

13. A query with column names and field names in the results