Pivots with Dynamic Columns in SQL Server 2005 

Wednesday, January 5, 2011 12:25:00 AM


 

Pivots in SQL Server 2005 can rotate a table, i.e. they can turn rows into columns. PIVOTs are frequently used in reports, and they are reasonably simple to work with. However, I've seen quite a few questions about this operator. Most questions were about the column list in the PIVOT statement. This list is fixed, but many times the new columns are determined by the report at a later stage. This problem is easily solved when we mix pivots with dynamic SQL, so here is a very simple example about how to dynamically generate the pivot statement:

PIVOT allows you to turn data rows into columns. For example, if you have a table like this (I use only three months here for simplicity):

 

CREATE TABLE Sales ([Month] VARCHAR(20) ,SaleAmount INT)

INSERT INTO Sales VALUES ('January', 100)
INSERT INTO Sales VALUES ('February', 200)
INSERT INTO Sales VALUES ('March', 300)

SELECT * FROM SALES
 

Month             SaleAmount
----------------  -----------
January           100
February          200
March             300 

 

Suppose we wanted to convert the above into this:

 
January     February    March
----------- ----------  ----------
100         200         300

We can do this using the PIVOT operator, as follows:

SELECT  [January]
      , [February]
      , [March]
FROM    ( SELECT    [Month]
                  , SaleAmount
          FROM      Sales
        ) p PIVOT ( SUM(SaleAmount)
                    FOR [Month] 
                      IN ([January],[February],[March])
                  ) AS pvt

However, in the above example, I have the column names fixed as the first three months. If I want to create a result in which the columns are dynamic (for example, they are read from a table), then I need to construct the above query dynamically. To demonstrate this let’s look at the following example:

In the first table I have the column names I want to use:

CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, 'Country')
INSERT INTO Table1 VALUES(2, 'Month')
INSERT INTO Table1 VALUES(3, 'Day')

In the second table I have the data. This consists of a row identifier (tID), a column ID (ColID) that refers to the column type in Table1, and a value:

CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))

INSERT INTO Table2 VALUES (1,1, 'US')
INSERT INTO Table2 VALUES (1,2, 'July')
INSERT INTO Table2 VALUES (1,3, '4')
INSERT INTO Table2 VALUES (2,1, 'US')
INSERT INTO Table2 VALUES (2,2, 'Sep')
INSERT INTO Table2 VALUES (2,3, '11')
INSERT INTO Table2 VALUES (3,1, 'US')
INSERT INTO Table2 VALUES (3,2, 'Dec')
INSERT INTO Table2 VALUES (3,3, '25')

Now I would like to retrieve data from these two tables, in the following format:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec 

In other words I want to turn the data rows in Table2 into columns. If I had a fixed set of columns for the result, i.e. the columns Country, Day, and Month were fixed, I could use SQL Server 2005’s PIVOT operator in a query like:

SELECT  tID
      , [Country]
      , [Day]
      , [Month]
FROM    ( SELECT    t2.tID
                  , t1.ColName
                  , t2.Txt
          FROM      Table1 AS t1
                    JOIN Table2 
                       AS t2 ON t1.ColId = t2.ColID
        ) p PIVOT ( MAX([Txt])
                    FOR ColName IN ( [Country], [Day],
                                     [Month] ) ) AS pvt
ORDER BY tID ;

However I need to construct this query dynamically, because the column names Country, Day, and Month are specified in a table, and can be changed independently from my query. In our case these columns are given in Table1.

In the first step to generate the final pivot query I need to create the list of columns, in this case [Country], [Day], [Month].

Since there is no string concatenation aggregator in SQL (a concatenation aggregator would not be deterministic without some order restriction), and since the column names are stored in rows of a table, I need to flatten these columns into a single row or variable. There are various solutions to achieve this. One solution would be to use a query like:

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = COALESCE(@cols + ',[' + colName + ']',
                         '[' + colName + ']')
FROM    Table1
ORDER BY colName

This query works both on SQL Server 2000 and 2005. It is efficient, but some may not like it because it uses the same variable (@cols) on both sides of an assignment. Another solution that works on SQL Server 2005 only is to use XML PATH.

DECLARE @cols NVARCHAR(2000)
SELECT  @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT
                                '],[' + t2.ColName
                        FROM    Table1 AS t2
                        ORDER BY '],[' + t2.ColName
                        FOR XML PATH('')
                      ), 1, 2, '') + ']'

This second query (I’ve seen this posted by Peter Larsson) has the advantage that it does not use the @cols variable on the right hand side. I like this solution more, since this can be extended as a general string concatenation aggregate in more complex queries.

Both of the above queries generate, from Table1, the string: ‘[Country],[Day], [Month]’. This column list is used twice in the pivot query that we aim to construct. Once it is use in the list of columns that we want to retrieve, and once it is used as the list of values that should become columns. Having constructed this list of columns above, we can just concatenate it with the missing parts of the pivot query like:

DECLARE @query NVARCHAR(4000)
SET @query = N'SELECT tID, '+
@cols +'
FROM
(SELECT  t2.tID
      , t1.ColName
      , t2.Txt
FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( '+
@cols +' )
) AS pvt
ORDER BY tID;'

Executing this with

EXECUTE(@query)

will give us the expected result: a table that is pivoted and shows columns that were specified in a table:

tID         Country    Day        Month
----------- ---------- ---------- ----------
1           US         4          July
2           US         11         Sep
3           US         25         Dec

SQL SERVER – Convert Text to Numbers (Integer) – CAST and CONVERT 

Tuesday, January 4, 2011 11:54:00 PM

Few of the questions I receive very frequently. I have collect them in spreadsheet and try to answer them frequently.

How to convert text to integer in SQL?
If table column is VARCHAR and has all the numeric values in it, it can be retrieved as Integer using CAST or CONVERT function.

How to use CAST or CONVERT?
SELECT CAST(YourVarcharCol AS INT) FROM Table
SELECT CONVERT(INT, YourVarcharCol) FROM Table

Will CAST or CONVERT thrown an error when column values converted from alpha-numeric characters to numeric?
YES.

Will CAST or CONVERT retrieve only numbers when column values converted from alpha-numeric characters to numeric?
NO.

How to parse/retrieve only numbers from column values contains alpha-numeric characters?
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

What are the online references for CAST and CONVERT?
CAST and CONVERT

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Sharepoint Google map 

Tuesday, January 4, 2011 1:05:00 AM

Project Description
this Sharepoint 2007 solution allow user to add a "google map" custom field on a contact list displaying contact's address in a map.

How to install this solution ?
This solution can be installed as any other Sharepoint solution :

  • stsadm -o addsolution -filename CompletPathToSolution.wsp
  • stsadm -o deploysolution -name GoogleMapsField.wsp -allowgac -immediate -url http://xxxxx (url of your web application)


To work correctly a key provided by google is mandatory. You can generate this key for your site from this url : http://code.google.com/apis/maps/signup.html
Note : Only a free and public user is allowed by the license.

After you have to add this key into your application web.config file in the AppSettings section :

  • <add key="GoogleMapsKey" value="xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" />


And the last step : IISRESET

That's all ! you're now able to add a new type of field into you contact List.
To use this control with another list type than Contact list. You just have to add a field named 'Address' on your list.

GoogleMap.jpg
ScreenShot025.jpg



 

The Acai Berry Diet Exposed: Miracle Diet or Scam? 

Tuesday, December 28, 2010 7:33:00 PM

Acai Berry is the latest weight loss craze that taking America by storm. This so called 'Miracle Formula' that you take as a supplement to promote rapid weight loss has been getting a lot of international attention lately. As you may have seen, the claims of losing over 12 lbs in 30 days with an Acai Berry are all over the internet on various blogs and success stories from people who have apparently used these pills. Here at News24 Health Reports, we are a tad bit skeptical and aren't sure that we've seen enough proof that this 'Miracle Formula' works for significant weight loss. Since we have been seeing so much publicity about this "Super Diet," we thought it was only right that we uncover the truth and hopefully expose these amazing claims.

It was inevitable that I was to be the guinea pig, as I am getting married in 5 weeks and wanted to lose 15 pounds for my wedding. In order to do the Acai Berry diet, I needed to order the product. There are ton's of Acai berry products on the market, and choosing the right Acai for our test was crucial. After much research, we chose Quick Slim because they have been around for over 10 years and were one of the first companies to bring Acai Berry to the market. 1 bottle = 30 days supply of the product which would be perfect for our test. Quick Slim is the most credible suppliers on the market, and did not try and force me into signing up for addditional offers. The last reason we chose Quick Slim is because it contains the freshest and purest acai on the market. With this important information in hand, we are now be able to get the most accurate results for our test.

 

 

Page 2 of 2 << < 1 2