September 28, 2014 at 7:56 am
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
September 28, 2014 at 11:33 am
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
Change is inevitable... Change for the better is not.
September 28, 2014 at 1:38 pm
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
September 29, 2014 at 8:20 am
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
September 29, 2014 at 11:29 am
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
September 29, 2014 at 11:51 am
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.
September 30, 2014 at 10:26 am
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
September 30, 2014 at 11:12 am
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.
September 30, 2014 at 11:18 am
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.
September 30, 2014 at 11:53 am
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
September 30, 2014 at 2:17 pm
Thanks a lot for your help.
Very hard for me as beginner but , I can learn a lot from your example
Regards
October 1, 2014 at 6:19 am
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
October 1, 2014 at 7:33 am
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.
October 1, 2014 at 9:43 am
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
October 1, 2014 at 10:01 am
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.....
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply