Logic for picking values from columns

Hi
Can anyone please help me ceate a logic (and SQL syntax) for this. I
have a table which has the 1st column as some Primary key and the rest
of the columns has integer values stored in them (there may be any no.
of columns with the integer values).
Now, I want to read the values in each record one by one for diff
columns starting from the 1st column with integer values, and pick the
column name of 1st non-zero integer.
Please see if someone can help with this.
Thanks
SGSG
CREATE TABLE #Test
(
rowid INT NOT NULL,
col1 INT,
col2 INT,
col3 INT
.....
)
So far everything is ok ,now I'm not sure understood you
DECLARE @.var VARCHAR(50)
SET @.var=''
SELECT @.var=@.var+ COALESCE(col1,0)+','+COALESCE(col2,0)+...... FROM #Test
SELECT @.var
--Or did you mean to get all values under one (the first one column)?
SELECT col1 FROM #Test
UNION ALL
SELECT col2 FROM #Test
UNION ALL
........
If it does not help please post desired output.
"SG" <shekhar.gupta@.gmail.com> wrote in message
news:1137998791.360110.248740@.g47g2000cwa.googlegroups.com...
> Hi
> Can anyone please help me ceate a logic (and SQL syntax) for this. I
> have a table which has the 1st column as some Primary key and the rest
> of the columns has integer values stored in them (there may be any no.
> of columns with the integer values).
> Now, I want to read the values in each record one by one for diff
> columns starting from the 1st column with integer values, and pick the
> column name of 1st non-zero integer.
> Please see if someone can help with this.
> Thanks
> SG
>|||SG wrote:
> Hi
> Can anyone please help me ceate a logic (and SQL syntax) for this. I
> have a table which has the 1st column as some Primary key and the rest
> of the columns has integer values stored in them (there may be any no.
> of columns with the integer values).
> Now, I want to read the values in each record one by one for diff
> columns starting from the 1st column with integer values, and pick the
> column name of 1st non-zero integer.
> Please see if someone can help with this.
> Thanks
> SG
"Pick the column name" I assume means you want to return the first
column name for each row? If I'm wrong then please post DDL, sample
data and show your required end result so that we don't have to guess
again.
Try:
SELECT
CASE
WHEN col1<>0 THEN 'col1'
WHEN col2<>0 THEN 'col2'
WHEN col3<>0 THEN 'col3'
.. etc
END AS col
FROM your_table ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks Uri / David,
I have got the idea how to do this. I am sorry about not posting the
table structure and the code, will take care abt this in future.
Regards,
SG