October 3, 2014 at 8:52 am
Hello
Last question...
Could I compare in case statement instead:
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
When col_two value ='c' then 1 ELSE NULL END AS C_' + CAST(@col_three AS varchar)
col_two value ='d' then 1 ELSE NULL END AS C_' + CAST(@col_three AS varchar) ... and so on ..,.
regards
October 3, 2014 at 9:12 am
ZZartin (9/30/2014)
The basic idea would be something like,
CREATE TABLE #test_table (COL_ONE varchar(30), COL_TWO varchar(30), COL_THREE int)
...
-- Cursor version
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)
GO
-- Variable assignment version
DECLARE @sql VARCHAR(8000)
SELECT @sql = ISNULL(@sql,'') + ', CASE WHEN COL_THREE = ' + CAST(col_three AS varchar) + ' THEN 1 ELSE NULL END AS Hist_' + CAST(col_three AS varchar)
FROM (SELECT DISTINCT COL_THREE FROM #test_table) d ORDER BY COL_THREE ASC
EXEC('SELECT COL_ONE, COL_TWO ' + @sql + ' FROM #test_table')
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2014 at 1:01 am
Hello
I would like t ouse the same procedure but compare two columns
On this one I can only check if the value of col_three is 'x' then...
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
But i can not say:
If the value of col_two is 'c' then 1 else null end as hist_' + cast(col_three as varchar)
or if the value of col_two is 'a' then null end as hist_ '.......
regards
October 8, 2014 at 9:31 am
It seems to me. I have to change my basic idea and go to another way to resolve the problem.
October 22, 2014 at 12:57 am
Hello
And finally I understood the query.
Now, How can I insert the result into new table, or append to another table.
Is the result anywhere in memory?
Regards
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply