Dynamic Group by without Dynamic SQL

Good After All,

I learned some cool SQL magic the other day! I wanted to dynaamiclly drop columns and group the statement in a store procedure without using dynamic sql.  Dropping the unwanted columns was easy, but the group by statement was not that simple to figure out right away.  In tihs example I will count the number of players and change the columns being outputted.

As always let's create the temp tables with data.  Well use the same values we did a few blogs ago to make things easier.


--Drop Table/store procedure
IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Players') ) DROP TABLE #Players

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#TempCount') ) DROP TABLE #TempCount


--Create Table
CREATE TABLE #Teams(TeamID INT,Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))
CREATE TABLE #Players(PlayerID INT,FirstName VARCHAR(20),LastName VARCHAR(20))
CREATE TABLE #Roster(TeamID INT, PlayerID INT)


--Insert Values into Table
INSERT INTO  #Teams(TeamID,Team,Founded,City,State)
    SELECT  100,'Milwaukee Brewers','1970','Milwaukee','WI'
    UNION ALL
    SELECT 200,'Chicago Cubs','1870','chicago','IL'
    UNION ALL
    SELECT 300,'Minnesota Twins','1894','Minneapolis','MN'
    UNION ALL
    SELECT  400,'Detroit Tigers','1894','Detroit','MI'

INSERT INTO #Players( PlayerID, FirstName, LastName )
   SELECT 100,'Hank','Aaron'
   UNION ALL
   SELECT 101, 'Robin','Yount'
   UNION ALL
   SELECT 102,'Ernie','Banks'
   UNION ALL
   SELECT 103,'Billy','Williams'
   UNION ALL
   SELECT 104, 'Kirby','Puckett'
   UNION ALL
   SELECT 105, 'Harmon','Killebrew'
   UNION ALL
   SELECT 106, 'Ty','Cobb'
   UNION ALL
   SELECT 107, 'Al','Kaline'
   
INSERT INTO #Roster( TeamID, PlayerID )
   SELECT 100,100
   UNION ALL
   SELECT 100,101
   UNION ALL
   SELECT 200,102
   UNION ALL
   SELECT 200,103
   UNION ALL
   SELECT 300,104
   UNION ALL
   SELECT 300, 105
   UNION ALL
   SELECT 400, 106
   UNION ALL
   SELECT 400, 107
  




Before we can start diving into the group by we need to set up some variables.  Below are the variables I have as well as defaul the value to 0 if its set to null.

--Declaring the variables
DECLARE @TeamOut BIT
DECLARE @CityOut BITD
ECLARE @StateOut BIT

--assinging values to variables
SET @TeamOut=NULL
SET @CityOut=NULL
SET @StateOut=1

--If values are passed as null then convert to 0
IF ISNULL(@TeamOut,'')          = '' SET @TeamOut=0
IF ISNULL(@CityOut,'')          = '' SET @CityOut=0
IF ISNULL(@StateOut,'')         = '' SET @StateOut=0

Now let's look at the SQL select statement.

SELECT COUNT(p.PlayerID) AS total,
       CASE WHEN @TeamOut = 1 THEN Team ELSE NULL END as Team,
       CASE WHEN @CityOut = 1 THEN City ELSE NULL END as City,
       CASE WHEN @StateOut = 1 THEN State ELSE NULL END as STATE
INTO #TempCount      
FROM #Teams t
INNER JOIN #Roster r ON t.TeamID = r.TeamID
INNER JOIN #Players p ON r.PlayerID = p.PlayerID  
GROUP BY    
   (CASE WHEN @TeamOut=1 THEN Team ELSE NULL END),
   (CASE WHEN @CityOut=1 THEN City ELSE NULL END),
   (CASE WHEN @StateOut=1 THEN State ELSE NULL END)

As you can see I use a case statement with the use of the use of the variables.  This seems to work out perfect.  Now what happens when the bit flag is set to 0 and we dont want to show it?  I thought you would ask that question.  It's pretty simple.  We just modified the temp table (#TempCount) that holds all the data.

--Removing columns we dont want to output
IF @TeamOut         = 0 ALTER TABLE #TempCount DROP COLUMN Team
IF @CityOut         = 0 ALTER TABLE #TempCount DROP COLUMN City
IF @StateOut        = 0 ALTER TABLE #TempCount DROP COLUMN STATE

Now we just do a simple Select statement from the temp table to get the results.  Here are a few different examples with just changing the bit flag.


Example 1.

SET @TeamOut=1
SET @CityOut=NULL
SET @StateOut=NULL

Example 2:

SET @TeamOut=1
SET @CityOut=NULL
SET @StateOut=1







Example 3:

SET @TeamOut=NULL
SET @CityOut=NULL
SET @StateOut=NULL

This is just a simple example for you, but you can see the value is does.  Hope this was valueable to you as it was to me.

Have a great day!

Robert

Comparing Stuff and Replace Command

Good Morning All,

I found a cool neat function in SQL Server that is pretty helpful for replacing string within text.  This function is called the Stuff (Transact-SQL).

This function is simpilar to the Replace (Transact-SQL) function but they are different.  In the replace function you replace one string with another in any place in the whole text whereas the stuff function only replaces the string if the text is a certain value based on position within the text.  Well look at an example where some of the data got insert wrong with '#' in the teams name and we want to remove the first occurrence within the text.

What will I cover in this blog?

• Setting up a table
• Show how the Replace function works
• Show how the Stuff function works

First let's set up a simple table.

--Remove table if its there
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams

--Create new table
CREATE TABLE #Teams(TeamID INT IDENTITY(10000000,1),Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))

--Insert Team 
INSERT INTO  #Teams(Team,Founded,City,State) 
    SELECT '#Milwaukee Brewers#','1970','Milwaukee','WI'
    UNION ALL
    SELECT '#Minnesota Twins#','1894','Minneapolis','MN'
    UNION ALL
    SELECT 'Chicago Cubs','1870','Chicago','IL'
    UNION ALL
    SELECT 'Detroit Tigers','1894','Detroit','MI'


Seconly, let's see how the replace command works.

SELECT Team, REPLACE(Team,'#','')AS RepaceFunction FROM #Teams

As you can see the replace command both '#' in the Milwaukee Brewers and Minneasota Twins entry.

Thirdly, let's look at the stuff command.  With the use of the case statement we are able to update the correct rows.

SELECT team,
       CASE
           WHEN  SUBSTRING(Team,1,1)='#' THEN STUFF(Team,1,1,' ')
           ELSE team
       END AS StuffFunction 
from #Teams

As you can see in this screen shot that it only removed the first '#' within the Milwaukee Brewers and Minneasota Twins string.

Bonus time!  Let's review both commands in one statement.

SELECT team,
       REPLACE(Team,'#','')AS RepaceFunction,
       CASE
           WHEN  SUBSTRING(Team,1,1)='#' THEN STUFF(Team,1,1,' ')
           ELSE team
       END AS StuffFunction 
from #Teams


Now you can see how powerful this might be in updating data in your database.  This is only one of many ways to use either the replace or stuff functions.

Have a great day everyone,

Robert

Merge Statement and Cross Apply

Good Morning All,

The other day I got to dive into learning about the Merge Statement within Sql Server 2008.  All I can say is wow!  This is one of my favorite new commands in SQL Server.  I think I will be using it a lot.  It makes inserts, updates, and deletes eaiser and more efficient.  In tihs blog I dont have the best explain to make use of this, but it will cover alot of different topics and hopefully help you out.

Before we can write the Merge command there are few other tricks I want I need to explain that I do in this merge statement.

I will explain the following:

1. Turn a comma list variable into a table result
2. Find the cartesian  product of two tables
3. Finally the merge statement

First I will take a comma list variable into a table result.  Here is the current variable I have.

DECLARE @Players VARCHAR(500)

SET @Players = 'Hank Aaron,Robin Yount,Ryan Braun,Corey Hart'

Now I want to output it like the following:

Player Name
Hank Aaron
Robin Yount
Ryan Braun
Corey Hart

To populate this table from the variable I use a function in my database called fn_split by using this select statement.


SELECT value AS PlayerName FROM dbo.fn_Split(@Players,',')

Here is the function defintion for you.  I beleive in giving credit where credit is due, but sadly can't give the person that wrote this function any credit.  I dont remember where I found this function, but I believe it was a forum I was searching on years ago. It has worked great for me.

CREATE FUNCTION dbo.fn_Split(@text VARCHAR(8000), @delimiter VARCHAR(20) = ', ')RETURNS @Strings TABLE
    (value VARCHAR(8000) )
            AS
    BEGIN
    DECLARE @index INT
        SET @index = -1
    WHILE (LEN(@text) > 0)
        BEGIN
        SET @index = CHARINDEX(@delimiter , @text)
        IF (@index = 0)
                    AND (LEN(@text) > 0)
            BEGIN
            INSERT INTO @Strings
                    VALUES (@text)
                        BREAK
            END
        IF (@index > 1)
            BEGIN
            INSERT INTO @Strings
                    VALUES (LEFT(@text, @index - 1))
            SET @text = RIGHT(@text, (LEN(@text) - @index))
            END
        ELSE
            SET @text = RIGHT(@text, (LEN(@text) - @index))
        END
    RETURN
    END
            

Let's set up our temp tables to work with..

--Remove table if its there
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#Teams') )DROP TABLE #Teams
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Roaster') )DROP TABLE #Roaster

--Create new table
CREATE TABLE #Teams(TeamID INT IDENTITY(10000000,1),Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))
CREATE TABLE #Roaster(TeamID INT,PlayerName VARCHAR(200),CreatedOn DATE, ModifiedOn DATETIME)



Let's create a team for our team table.

--Insert Team 
INSERT INTO  #Teams(Team,Founded,City,State) SELECT  'Milwaukee Brewers','1970','Milwaukee','WI'
  
Secondly, let's work on creatting our cartesian production between the variable and team table.  This is pretty simple with some SQL magic.  The statement makes use of the outer apply as well as the function.

SELECT * FROM #Teams
OUTER APPLY dbo.fn_Split(@Players,',')p

This is the output of the above select statement.

Finally we can create the merge statement.  It's pretty simple now that we have everything set up and ready to go.  We want to be able to insert and update values in the roaster table.

Here is the entire command at one time:

MERGE #Roaster r
USING (SELECT t.TeamID,p.value AS PlayerName 
            FROM #Teams t
            OUTER APPLY dbo.fn_Split(@Players,',')p
      ) tp ON r.TeamID=tp.TeamID 
         AND r.PlayerName=tp.PlayerName
WHEN NOT MATCHED THEN
       INSERT (teamid, PlayerName, CreatedOn)
       VALUES (tp.TeamID,tp.PlayerName,GETDATE())
WHEN MATCHED THEN  
       UPDATE SET ModifiedOn=GETDATE()
       ;

Here is the output for inserting values:

Here is the output for when values are updated:

Well that should do it for my blog entry.  Hopefully this helped you out and give you some insight on some new functions and features to use in SQL Sever.

Cheers,

Robert