Select case statement

Question

Select case statement

glarry

Mr or Mrs. 500

Points: 572
More actions
September 28, 2014 at 7:56 am

#291108

Is it possible to use the result of case statement as Alias?
Select date, case when age>20 then age End as Test+Age value

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

glarry (9/28/2014)
Is it possible to use the result of case statement as Alias?
Select date, case when age>20 then age End as Test+Age value

No. Not unless you have some form of dynamic SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Not certain what you are asking for but I´ll throw in some explanation. Column names cannot be changed during the execution, changing the column name must then be an action of a dynamic sql statement. Changing the column designation of a value is a different thing, and a case statement is perfect for that

😎

USE tempdb;

GO

SET NOCOUNT ON;

;WITH SAMPLE_DATA (ROW_ID,GRP_ID,VAL) AS

( SELECT * FROM (VALUES

(1,1,100)

,(1,2,101)

,(1,3,102)

,(1,1,103)

,(1,2,104)

,(1,3,105)

) AS X(ROW_ID,GRP_ID,VAL)

)

SELECT

SD.ROW_ID

,SD.GRP_ID

,SD.VAL

,CASE WHEN SD.GRP_ID = 1 THEN SD.VAL END AS GROUP_1_VAL

,CASE WHEN SD.GRP_ID = 2 THEN SD.VAL END AS GROUP_2_VAL

,CASE WHEN SD.GRP_ID = 3 THEN SD.VAL END AS GROUP_3_VAL

,CASE WHEN SD.GRP_ID <> 3 THEN SD.VAL END AS ALL_BUT_3_VAL

FROM SAMPLE_DATA SD;

Results

ROW_ID GRP_ID VAL GROUP_1_VAL GROUP_2_VAL GROUP_3_VAL ALL_BUT_3_VAL

----------- ----------- ----------- ----------- ----------- ----------- -------------

1 1 100 100 NULL NULL 100

1 2 101 NULL 101 NULL 101

1 3 102 NULL NULL 102 NULL

1 1 103 103 NULL NULL 103

1 2 104 NULL 104 NULL 104

1 3 105 NULL NULL 105 NULL

glarry Mr or Mrs. 500 Points: 572 More actions · Answer 3

Thanks a lot for the answer.

I will try to explain my problem

I have a table like:

Col_1 Col_2 Col_3

test1 c 1

test1 b 500

test2 a 2

…..

Col_1Col_2Hist_1Hist_2Hist_3……Hist_500

test1 c 1

test1 b 1

test2 a 1

….

I hope , I explained it better now

Regards

glarry Mr or Mrs. 500 Points: 572 More actions · Answer 4

Thanks a lot for the answer.

I will try to explain my problem

I have a table like:

Col_1.....Col_2......Col_3

test1.......c............1

test1.......b............500

test2.......a............2

…..

Col_1.....Col_2.....Hist_1.....Hist_2......Hist_3......……Hist_500

test1.......c............1

test1.......b..............................................................1

test2.......a...........................1

….

I hope , I explained it better now

Regards

ScottPletcher SSC Guru Points: 100947 More actions · Answer 5

You can use CROSS APPLY to effectively assign an alias to an expression and then use it in any SQL clause:

SELECT test + ...,

FROM ...

CROSS APPLY (

SELECT CASE WHEN age > 20 THEN age END AS test

) AS assign_alias_1

WHERE test = ...

ORDER BY test

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

glarry Mr or Mrs. 500 Points: 572 More actions · Answer 6

Sorry I make a mistake.

This is the write question:

I have a table like:

Col_1.....Col_2......Col_3

test1.......c............1

test1.......b............500

test2.......a............2

…..

I would like to obtain this one

Col_1.....Col_2.....Hist_1.....Hist_2......Hist_3......……Hist_500

test1.......c............1

test1.......b..............................................................1

test2.......a...........................1

….

I hope , I explained it better now

Regards

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 7

That's not terribly hard to set up but your result set will have a huge number of columns very fast if there's any large range of data you're looking at.

glarry Mr or Mrs. 500 Points: 572 More actions · Answer 8

I know, but That is the only way to export the data and show the data . I make the same table with vb.net and it's work well.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 9

The basic idea would be something like,

CREATE TABLE #test_table (COL_ONE varchar(30), COL_TWO varchar(30), COL_THREE int)

INSERT INTO #test_table

SELECT 'test1', 'c', 1

UNION

SELECT 'test1', 'b', 500

UNION

SELECT 'test2', 'a', 2

DECLARE @sql varchar(max),

@col_three int

DECLARE temp_con CURSOR FOR SELECT DISTINCT COL_THREE FROM #test_table ORDER BY COL_THREE ASC

OPEN TEMP_CON

SET @sql = 'SELECT COL_ONE, COL_TWO '

FETCH NEXT FROM temp_con INTO @col_three

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = @sql + ', CASE WHEN COL_THREE = ' + CAST(@col_three AS varchar) + ' THEN 1 ELSE NULL END AS Hist_' + CAST(@col_three AS varchar)

FETCH NEXT FROM temp_con INTO @col_three

END

CLOSE temp_con

DEALLOCATE temp_con

SET @sql = @sql + ' FROM #test_table'

EXEC (@sql)

DROP TABLE #test_table

glarry Mr or Mrs. 500 Points: 572 More actions · Answer 10

Thanks a lot for your help.

Very hard for me as beginner but , I can learn a lot from your example

Regards

glarry Mr or Mrs. 500 Points: 572 More actions · Answer 11

Hello

Last question........ I hope....

Is it possible to loop the query several time with different variable or array list and union all in the same temporary table?

For example:

1 loop -----DECLARE temp_con CURSOR FOR SELECT DISTINCT COL_THREE FROM #test_table ORDER BY COL_THREE ASC

2 loop -----DECLARE temp_con CURSOR FOR SELECT DISTINCT COL_FOUR FROM #test_table ORDER BY COL_FOUR ASC

3 loop -----DECLARE temp_con CURSOR FOR SELECT DISTINCT COL_FIVE FROM #test_table ORDER BY COL_FIVE ASC

and so on..

Regards

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 12

Yes potentially you could do that, all that loop is doing is building a select statement, but once again keep in mind the more you add in the more columns you'll get in your output.

glarry Mr or Mrs. 500 Points: 572 More actions · Answer 13

No the number of column will be max 500, the data from each variable is the same.So i want to append (union) at the end of each Run

Regards

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 14

Okay yeah there's nothing stopping you from doing that, if you took that idea and used it to build however many select statements you were going to run that for then joined all the statements with a union.

Something like SET @sql = @sql_one + ' UNION ALL ' + @sql_two + ' UNION ALL ' + @sql_three etc.....