Would someone help me understand the details of the error below..? This is for SQL Server 2008. I did fix it myself, and found many search hits which show the same fix, but none explain WHY this happens in a CTE. Types don’t match between the anchor and the recursive part in column “txt” of recursive query “CTE”. Here is
Tag: sql-server-2008
Select into a temporary table efficiency is faster than direct query
Case: Query below sql need 10.56 sec, and 236 rows were queried. Estimated Execution Plan: Most cost: After I try to use select into temporary table then it just need 0.03 sec Estimated Execution Plan: Then I’ve tried add below index, but it still no change. hct_mlots table create sql，total rows 88790 mf_mo table create SQL Answer I made an
Apply multiple count conditionally in SQL Server query
I have a task table which contains some DateTime type columns like completed, pending, exception. It joins user table and I want to bring userName, AllTaskCount, AllPendingTaskCount, …
Showing conditional Where Condition in SQL SERVER
I am trying to achieve something like this. SELECT Name, Age From Employee WHERE ID=1 AND CASE(WHEN backguid=’00000000-0000-0000-0000-000000000000′ THEN ([email protected] and City = @city) ELSE (…
Conditionally convert a decimal value to integer
It is showing a result like this But I want result like this when type is 1 then result should be in integer format: on else condition (@Type rather than 1) I want result like this I have tried this query Answer This will work for you. When @type is 1, the result will be 10 for both values. Otherwise
Show all varchar values with PIVOT
I need to show all varchar values with pivot but I’m not sure if that is possible? I have OCCUPATIONS table: Pivot query: Query result: Above query gives only 1 record in each column but I want to get all. Answer You ‘ll need an extra column, e.g. with ROW_NUMBER():
How to findout a number between two column in SQL Server 2008
Suppose I have table called dbo.tbl_Search Id From To ——————- 1 200 210 2 212 220 3 203 215 4 225 240 Suppose, I search for No.-205 then I would to …
Update column data using SQL query
In our SQL Server database, we are trying to update the old column data in a table. In a table there is lot of data similar to Agency Administrator. This is data is in old format where we were taking single values in a column. Now, due to an update in data structure, we are storing the data as [“Agency
Get Row value as Column Header
I have two tables as below. I want to join these two tables and need to set the column values of second table as column header as shown below. How can I achieve this? Table1 table2 The output table should be Answer You can use Dynamic Pivot as below-
How to select duplicate records for a column having Data/NULL values in other columns?
I have a table in SQL server with 2.5 M records and I want to select the unique records as in image below. Can someone help me write a select query for this? Original table and requested data: