Drill Down SQL Query

SQL Server does not provide any drill down queries such as Category > Sub Category. There are reporting tools for it but it cannot be done in SQL Query alone without help from theird party software. There is SQL Group By ROLLUP, CUBE and “Group Sets”. It is a way to get rid of UNIONs and give you ‘total’ figures which gives you “some” level of drilldown (rather drill up) and can be very useful when you are interested in find total numbers by other than the group clause but it does not exactly give you drill down that a…

Read More

How to give names to group sets in SQL

You have a group set query in SQL that you are using to give sum total of certain numbers. The problem is where the row calculates the sum total, the cell values in the corresponding colums say ‘NULL’. How to change ‘NULL’ to something more meaninful, that is ‘Total’, ‘Sum’, ‘Sales in United States’, etc It is really easy. Use ISNULL() function.

This query is standard query that will give you No of Leaves by each employee in all months. Now we are also interested in total leave in the year, for that we will use group sets

Read More

The type of column “xxx” conflicts with the type of other columns specified in the UNPIVOT list.

When pivoting data in SQL Server, all columns must be of same time, otherwise you will get this error. You might get this error if you have imperfect case statement or one column is of integer type while the other is of type varchar. Interesting I was getting this error even after casting my columns into varchar(10) but still the error. Turned out just casting wouldn’t fix the problem either. My problem code was

  The above code was giving The type of column “PercentValue” conflicts with the type of other columns specified in the UNPIVOT list becaust i…

Read More

SQL Server PIVOT tutorial

Pivot can be a little tricky to use. I will explain with the help of example

Here is the output

Now lets pivot this table

Here is the output

Lets understand how the PIVOT Query works In the first part you select a query that you want to pivot. In this example, my query is

so this is the data that pivot would be applied on. If you omit some fields in select query, pivot data will change accordingly. In the next part, you use an aggregate function, typically SUM() or COUNT(). In my example…

Read More

Get No of Sundays in a month SQL Server

The following stored procedure returns number of sundays in given month. It takes date as parameter. Month and year is extract from the date.

Test Run

return 4

Read More

SQL Condition in WHERE Clause

How would you set a condition in where clause in SQL Server? For example you have a parameter @ProjectID. If @ProjectID is 1, then select all employee with EmployeeNo 1000. If @ProjectID=2 then select all employee with employeeNo betweeen 1000 and 2000. SQL Case Statement might not work here so you will have to implment it otherwise. Try this query Declare @ProjectID int set @ProjectID=1 select … WHERE((@ProjectID=1 and EmployeeNo < 1000) OR (@ProjectID=2 and EmployeeNo >= 1000) OR (@ProjectID=0)) Hope it helps.

Read More

Add a linked Server in SSMS

I was having issue with running a linked server query and getting the following error Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1 The metadata could not be determined because every code path results in an error; see previous errors for some of these. I played with various functions to fixed the problem, namely sp_serveroption, sp_addlinkedsrvlogin but nothing fixed the error. Eventually I found that we can Linked Server in SQL Server Management Studio using a GUI. I tried that and it fixed the error for me. This is how to add linked server in SSMS 1. In…

Read More

How to read long text in sql server

Sometime you might want to read the entire text in a particular table column. If you execute the command select longColumn from MyTable You will see that you can only read the first 50 characters are so. If you are doing this in SQL Server Management studio, simply right click on the column or cell and select copy. Then past is word or any text editor of your choice. It quite easy but can some give you trouble.

Read More

Running query against remote server

If you want to run a SQL Query against a remote server for example a different machine on the same network, you may want to use the flowing code. First link the server, then use OPENQUERY to run the query against the linked server. The two sql commands do not need to be executed in one batch. Example Computer Name : MY-PC Remote Server Name: SQLExpress1 Present Server Name: SQLExpress2

Example 2 – Remote Query

Do not forget single quotes around the query Do not forget “Select * from” before OpenQuery statement.

Read More

SQL Server Management Studio 2008 on Windows Server 2008

How to start SQL Server Management Studio on Windows Server 2008 As you might have come through this nausense, you install SQL Server Management Studio 2008 (SSMS) on Windows Server 2008 and you can not start it. There is no shortcut menu created in the start menu. I look for it even inside the newly created folder but could not find it. Had to reinstall SQL Server Management Studio because It though it was not properly installed. Then I looked for it. Click in the “search for program” textbox above start menu and type “studio”. There it was. I found…

Read More