SQL Server 2008

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 reporting tool will give. It merely calculates overall sums/totals.

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

The problem here is the last column will give us the required number of leav1es in the year but it would say what it is in the EmployeeID column. In this case we know that it is total no of leaves in an year by that employee, so lets call it ‘Total’. We will modify the query as

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 was unpivoting like this

 

In the above query  + ‘%’ was the one that gave me the problem. By appending the string ‘%’ with varchar(10), it  return a true varchar type but my first string was only casted value. So adding + ‘ ‘ to the first string fixed the problem, which is basically a lank string. So the fixed code looked like this

which is merely adding +’ ‘ to string value.

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 it is

It is pretty clear from the expression itself. The first select query will be pivoted on “score in each subject”. The values in IN part is actually the column data that appears in the subject column. The data is moved into header fields. In this case we have select only Biology, Chemistry, Physica and Math. If there were more subjects in the Subject column, they will not be shown in the PIVOT table, because we are not displaying them.

Dynamic PIVOT Query

There is a way to select the subject values dynamically. The trick is to read the data first and store those values in a string and write a query and plug that string in.

Note that I am using distinct when pulling subject names otherwise, the same subject will be pulled twice and will result in error.

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

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.

Add a linked Server in SSMS

Add linked Server in SSMSI 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 SQL Server Management Studio go to “Server Object” on the root level on left hand side in “Objects Explorer”.
2. Expand “Linked Server” + sign to any linked server if any.
3. Right click on Linked Server and select “Add New Linked Server”
4. Select SQL Server Radio box
5. In the Security option below general, select “Be made using this security context”. Provide your remote db login name and password. Usually login name will be “sa”.
6. Select OK

Now run query against your server using the following sample code

You can try adding linked server from command line as well. Make sure to delete you linked Server by right clicking on it and delete so no one can use it!

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.

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.

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 found.

You have trouble installing SQL Server Management Studio on Windows Server 2008, the problem is likely you do not know how to invoke SSMS. It should already be installed.