Select only specific columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select only specific columns

Author

Topic

sqlfish
Starting Member

9 Posts

Posted - 2010-06-04 : 10:26:22

Hi there,

I'm new to SQL Server and have a problem. I would like to select only columns of a table which have a specifc number of NULL values. So I tried something like this:

SELECT
CASE
WHEN (true) THEN columnName
END
FROM tableName

This works fine but in the new table all column names are missing. I'm afraid that this is not working:

SELECT
CASE
WHEN (true) THEN columnName AS columnName
END
FROM tableName

Has anyone an idea how to solve this problem? I guess there should be simple solution.
Thanks

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 10:33:20

SELECT
CASE
WHEN (true) THEN columnName END AS columnName FROM tableName

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sqlfish
Starting Member

9 Posts

Posted - 2010-06-04 : 10:37:42

Thanks Idera, sometimes it can be so easy. ;-)

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 10:44:09

quote:
Originally posted by sqlfish

Thanks Idera, sometimes it can be so easy. ;-)

Yup happens to everyone sometimes.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 11:02:48

Note whenever there is a function,calculation or case expression, column name wont be displayed until you alias it

Madhivanan

Failing to plan is Planning to fail

sqlfish
Starting Member

9 Posts

Posted - 2010-06-07 : 09:15:53

I have one more question regarding this topic. The problem with the column names is solved but all columns are selected and the ones which don't satisfy the expression get NULL values in each row. Any ideas?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-07 : 09:20:18

Now it is about time to give example data, wanted output in relation to the sample data and what you have coded so far.

No, you're never too old to Yak'n'Roll if you're too young to die.

sqlfish
Starting Member

9 Posts

Posted - 2010-06-07 : 09:37:06

Ok, here some sample code:

CREATE TABLE #Test (
bloodflow int,
heartrate int
)

INSERT INTO #Test (bloodflow, heartrate)
VALUES (200,50)

INSERT INTO #Test (bloodflow, heartrate)
VALUES (300,60)

DECLARE @var real
SET @var = 100
SELECT
CASE
WHEN (SELECT MIN(bloodflow) from #Test) > @var THEN bloodflow
END AS bloodflow,
CASE
WHEN (SELECT MIN(heartrate) from #Test) > @var THEN heartrate
END AS heartrate
FROM
#Test

So I start with a table like this:
200|50
300|60

and I get
200|NULL
300|NULL

I just want to get the first column.

Sachin.Nand

2937 Posts

Posted - 2010-06-07 : 09:46:30

What you mean by "first column"?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sqlfish
Starting Member

9 Posts

Posted - 2010-06-07 : 09:50:48

I don't want

bloodflow|heartrate
200 |null
300 |null

just

bloodflow
200
300

The heartrate column shouldn't be selected.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-07 : 09:58:51

quote:
Originally posted by sqlfish

I don't want

bloodflow|heartrate
200 |null
300 |null

just

bloodflow
200
300

The heartrate column shouldn't be selected.

What if there is value for heartrate?

Madhivanan

Failing to plan is Planning to fail

sqlfish
Starting Member

9 Posts

Posted - 2010-06-07 : 10:09:48

Perhaps my intention is not clear and I do something totally wrong. Regarding the example I only want to select the columns of the table which have a min value > 100. Starting from

bloodflow|heartrate
200 |50
300 |60

I only want to select the column bloodflow because in column heartrate there is no value higher than 100.
So I only want

bloodflow
200
300

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-07 : 10:24:53

quote:
Originally posted by sqlfish

Perhaps my intention is not clear and I do something totally wrong. Regarding the example I only want to select the columns of the table which have a min value > 100. Starting from

bloodflow|heartrate
200 |50
300 |60

I only want to select the column bloodflow because in column heartrate there is no value higher than 100.
So I only want

bloodflow
200
300

What do you want to return for this?

bloodflow|heartrate
200 |150
300 |60

Madhivanan

Failing to plan is Planning to fail

sqlfish
Starting Member

9 Posts

Posted - 2010-06-07 : 10:28:13

Then I would like to get both columns back:

bloodflow|heartrate
200 |150
300 |60

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-07 : 10:32:40

If exists(select * from #table where heartrate>1000)
select bloodflow,heartrate from #table
else
select bloodflow from #table

Madhivanan

Failing to plan is Planning to fail

sqlfish
Starting Member

9 Posts

Posted - 2010-06-07 : 10:44:30

Thanks but that was just a toy example. I have not only 2 columns but many more. So this won't work for me. I thought it would be possible to use a 'select case' statement like the one I posted. So I could check each column and when the condition is satisfied the column will be selected, if not it is not selected.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-07 : 10:52:26

quote:
Originally posted by sqlfish

Thanks but that was just a toy example. I have not only 2 columns but many more. So this won't work for me. I thought it would be possible to use a 'select case' statement like the one I posted. So I could check each column and when the condition is satisfied the column will be selected, if not it is not selected.

Your method wont work. You need to simulate what I posted

Madhivanan

Failing to plan is Planning to fail

sqlfish
Starting Member

9 Posts

Posted - 2010-06-08 : 09:39:40

Ok, thanks anyway.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources