Friday, June 26, 2009

PIVOT Multiple Columns

In one of my previous post I showed you how to UNPIVOT multiple columns.  On similar lines I also wanted to write on “How to PIVOT multiple columns?”, so this post was due for some time.  Actually I was looking for some efficient way of doing it.  Limitation of PIVOT operator is, it supports pivoting only on a single column.  But you can always have multiple PIVOT operators in the FROM clause. I was trying to create a PIVOT query with multiple columns with multiple PIVOT operators. But at the end of it I found that our old fashioned CASE expression is performing much better than a multiple PIVOT operator query. 

Even though I’m writing this post on how to write a multiple PIVOT operator query, my suggestion is use CASE expressions instead for getting better performance.  Though personally I like to avoid CASE also.   Normally I like to do it in Reporting Services, by creating a Matrix report.  Now a days almost all Reporting Tools provides you an option of creating Matrix report.  And good thing about Matrix report is unlike PIVOT operator you don’t need to hard code any column value.

If you try to write a PIVOT query with 2 PIVOT operators, and use same column in FOR clause you will get an error : Invalid column name NameOfColumn.

Or if you use same column, but by declaring it again and using a different alias name, you still get an error : The column name ValueOfColumn specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

So what’s the solution?  Solution is, declare the same column again, change the values in the column by some constant(you can add some constant, or you can concat some identifier ) and assign a new alias name to column.

Lets see the following example, I have used the AdventureWorks database of SQL Server 2005.

USE AdventureWorks
GO
SET ANSI_WARNINGS OFF
SELECT
CustomerId,
        SUM([Q2001]) AS Qty2001,
        SUM([Q2002]) AS Qty2002,
        SUM([V2001]) AS Val2001,
        SUM([V2002]) AS Val2002
FROM (
        SELECT     H.CustomerId,
                SUM(D.OrderQty) AS TotalQty,
                SUM(D.LineTotal) AS TotalVal,
                'Q'+CONVERT(VARCHAR(4),H.OrderDate,120)  AS QYear,
                'V'+CONVERT(VARCHAR(4),H.OrderDate,120)  AS VYear
        FROM Sales.SalesOrderDetail AS D INNER JOIN
        Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId
        WHERE D.ProductId=771
        AND H.OrderDate >='20010101'
        AND H.OrderDate <'20030101'
        GROUP BY H.CustomerId,
                CONVERT(VARCHAR(4),H.OrderDate,120)
    )Main
PIVOT
    (
        SUM(TotalQty)
        FOR QYear IN ([Q2001],[Q2002])
    ) PQ
PIVOT
    (
        SUM(TotalVal)
        FOR VYear IN ([V2001],[V2002])
    ) PV
GROUP BY CustomerId
ORDER BY CustomerId
GO

The query returns total quantity and line amount for year 2001 and 2002 for the product id 771 for all customers.  If look at the query carefully in Main sub query, CONVERT(VARCHAR(4),H.OrderDate,120) this convert statement will take out the Year part from the OrderDate column.  I have declared the same column twice, at first I concatenated Q to the Year, and at second time I concatenated the V.  Just execute the Main sub query, so it will be easy to understand for you.

SELECT     H.CustomerId,
                SUM(D.OrderQty) AS TotalQty,
                SUM(D.LineTotal) AS TotalVal,
                'Q'+CONVERT(VARCHAR(4),H.OrderDate,120)  AS QYear,
                'V'+CONVERT(VARCHAR(4),H.OrderDate,120)  AS VYear 
FROM Sales.SalesOrderDetail AS D INNER JOIN 
Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId 
WHERE D.ProductId=771 
                AND H.OrderDate >='20010101' 
                AND H.OrderDate <'20030101' 
GROUP BY H.CustomerId,
                CONVERT(VARCHAR(4),H.OrderDate,120)

Now we have 2 columns, with different values, and we can use them in different PIVOT with same effect, and that’s what I have done in my 1st query.

Here is a CASE expression version of same query, which gives much better performance if you scale it for large amount data.

USE AdventureWorks
GO
SELECT     H.CustomerId,
               SUM(CASE YEAR(H.OrderDate)
                      WHEN 2001
                      THEN D.OrderQty
                      END) AS Qty2001,
               SUM(CASE YEAR(H.OrderDate)
                      WHEN 2002
                      THEN D.OrderQty
                      END) AS Qty2002,
              SUM(CASE YEAR(H.OrderDate)
                     WHEN 2001
                     THEN D.LineTotal
                     END) AS Val2001,
              SUM(CASE YEAR(H.OrderDate)
                     WHEN 2002
                     THEN D.LineTotal
                     END) AS Val2002
FROM Sales.SalesOrderDetail AS D INNER JOIN
Sales.SalesOrderHeader AS H ON D.SalesOrderId = H.SalesOrderId
WHERE D.ProductId=771
          AND H.OrderDate >='20010101'
          AND H.OrderDate <'20030101'
GROUP BY H.CustomerId
ORDER BY H.CustomerId
GO

You can test the performance of both queries.  If you want to scale it for larger data you can remove the WHERE conditions added by me.  Total execution time for CASE query is almost half to that of PIVOT query. 

Thursday, June 18, 2009

TCP Port of SQL Server

By default the default TCP Port for SQL Server is 1433.  It can be changed for security purpose to minimize the potential threat of a hacker trying to access through the default port number.  But that is whole together a different story.

Every now and then somebody posts this question on forums- how can I tell what port my SQL Server instance is running on?  As sometimes while accessing SQL Server remotely you may need to add the TCP Port in the connection string.

There are more than 1 way to find out the answer.  From SQL Server Error Logs, Configuration Manager, Command Prompt, Registry Editor.   But today I will share a simple T-SQL script with you, from which you can easily find out the TCP port SQL Server instance is running on.

First lets see in regedit where to look for the Port number. 
The location of port number for SQL Server 2005 -

For default instance : HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\TCP

For Named Instance :
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ [Instance Name]\MSSQLServer\SuperSocketNetLib\Tcp

If you don’t know how to open the regedit :
1. Click on Start and then on Run.
2. In run type regedit and click on Ok.

After checking out the port number manually now lets see the query I created.  It uses a non-documented extended stored procedure xp_regread.  Basically it is helpful in reading from the registry via Transact-SQL.  Since it is a non-documented extended stored procedure you won’t find any reference about xp_regread in Books Online. If you want to know more about it, best thing you can do is Google for xp_regread.  So here is the script which will give you the TCP port SQL Server is running on :

DECLARE @TcpPort VARCHAR(5)
        ,@RegKey VARCHAR(100)

IF @@SERVICENAME !='MSSQLSERVER'
    BEGIN
        SET @RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @@SERVICENAME + '\MSSQLServer\SuperSocketNetLib\Tcp'
    END
    ELSE
    BEGIN

        SET @RegKey = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP'
    END

EXEC master..xp_regread
    @rootkey = 'HKEY_LOCAL_MACHINE'
    ,@key = @RegKey
    ,@value_name = 'TcpPort'
    ,@value = @TcpPort OUTPUT

SELECT @TcpPort AS PortNumber
        ,@@SERVERNAME AS ServerName
        ,@@SERVICENAME AS ServiceName

You will get the output something like this :

Port Number

Other simple way of finding out the TCP port is from the Configuration Manager.
1. Start >> All Program >> Microsoft SQL Server 2005 >> Configuration Tools >> SQL Server Configuration Manager .
2. In the configuration manager click on SQL Server 2005 Network Configuration.
3. Then click on the Instance name for which you want to find out the Port Number.
4. Double click on TCP/IP.
5. Finally on IP Addresses, and you will see something like :

port

Friday, June 12, 2009

Alternating Row Background Color in Reports

Now lets see how to get alternating row background colors in Reports. This is one of the 1st typical report enhancement you might be doing once you started working on Reporting Services.  See the following report : 

report

This can be achieved very easily in SSRS with the help of IIF and RowNumber function for a simple report with no groupings.   You can use a background color expression as:

= IIF(RowNumber(Nothing) Mod 2, "White","Gainsboro")

It becomes little complicated in cases of Matrix and when some groupings are involved.  In that case use following expression for the details row within a group:

= IIF(RunningValue(Fields!Some_Field.Value,Count,"Group_Name")
Mod 2, "White","Gainsboro")

Here instead of RowNumber function I have used he RunningValue function.  Just remember to replace Some_Field with actual column name and Group_Name with actual Group Name in your report.

Thursday, June 4, 2009

Multiplying Column Values

Well we all know how to add the values in a column using SUM and GROUP BY.  But what about multiplying the column values?  And also how to calculate running multiplication on a column similar to Running Totals?

Sometime back I came across this interesting question on MSDN SQL Server forums - How to multiply all values in a table column?

I don't know the reason of such requirement, but very interesting problem though.  Of course there is no such built-in function in SQL Server to do it for you.  But after thinking on it for few minutes, I realize that if you still remember the basics of Mathematics, it is not that difficult to do in T-SQL.

If you remember
A * B = ANTILOG ( LOG (A) + LOG(B) )

So looking at it again, it is very straight forward using LOG, ANTILOG and SUM in SQL Server. (For doing ANTILOG you have EXP function in SQL Server.) Let me show you how :

Lets create some sample data first.

-- Create sample table
CREATE TABLE Scores
(
ID INT IDENTITY,
Category VARCHAR(1),
)
GO
-- Load sample data into table
INSERT INTO Scores(Category, Score)
SELECT 'A', 4 UNION ALL
SELECT
'A', 5 UNION ALL
SELECT 'A', 2 UNION ALL
SELECT
'B', 5 UNION ALL
SELECT
'B', 5 UNION ALL
SELECT
'B', 2 UNION ALL
SELECT
'B', 3
GO
-- test the sample data
SELECT Id, Category, Score
FROM Scores
GO

The sample data :
ID CATEGORY SCORE
1 A 4
2 A 5
3 A 2
4 B 5
5 B 5
6 B 2
7 B 3

Multiplying Column Values:  And here is the query to calculate the Total Product of Score Column for each Category.

SELECT Category, SUM(Score) as TotalSum,EXP(SUM(LOG(Score))) as TotalProduct
FROM Scores
GROUP BY Category

Result :

Category

TotalSum

TotalProduct

A

11

40

B

15

150

Second column you have normal total SUM for the Score column and in 3rd column you have the total product of the Score column for each Category.

Calculating The Running Multiplication:  After multiplying the column values, next question is how to calculate the Running Product similar to Running totals? See the following table -

ID

Category

Score

RunningProduct

1

A

4

4

2

A

5

20

3

A

2

40

4

B

5

5

5

B

5

25

6

B

2

50

7

B

3

150

The logic is similar to what I just mentioned for Multiplying Rows and adding the Running Total logic to query.

SELECT O.Id, O.Category, O.Score
        ,(  SELECT EXP ( SUM ( LOG (I.Score) ) )
             FROM Scores I
             WHERE I.Category = O.Category
             AND I.Id <= O.Id
         ) as RunningProduct
FROM Scores O

But remember this solution only works for positive numbers, if column contains negative values then you will need to modify the query using ABS function and taking into account number of negative values.  Well but at least you will get a start from this post.