Recursive CTE Query

A client came to me and had the similar to data set below.

Create
Table product (
productid        INT,
typeid              INT,
typeDesc        VARCHAR(15) )

INSERT INTO product VALUES (1,2,‘Male’),Product Set
(1,2,‘Female’),
(1,3,‘Red’),
(1,3,‘Green’),
(1,3,‘Blue’),
(1,4,‘Small’),
(1,4,‘Medium’),
(1,4,‘Large’),
(2,5,‘Small’),
(2,5,‘Medium’),
(2,5,‘Large’)

They wanted to make a single list of all possible choices for a given product.  For example, the first several rows for product 1 would start like this:

Large, Blue, Female
Large, Blue, Male
Large, Green, Female
Large, Green, Male
Large, Red, Female
Large, Red, Male

You would see every type combination for each product.  Product 2 would only have 3 lines.  They had tried a couple of other  joins and temp tables and they asked me to take a look because the query would have to be dynamic as the number of combinations would vary from product to product.  I was sure that I could use a CTE to resolve this.  I came across this Simple Talk article that helped me get the logic straight in my head.  Although I had been using CTE in my queries, this was the first time I really used it recursively.

The logic is to declare the CTE and the query you want and the inside the CTE use the UNION statement to then call the CTE again.  It will loop through and create the string.

WITH product_CTE ( productid, typeid, typeDesc, long_answer,length)
AS
(SELECT productid, typeid,CAST( typeDesc ASVARCHAR(8000)),CAST(ASVARCHAR(8000)), 0
FROM
product
WHERE
productid = 1

UNION ALL

        SELECT x.productid, x.typeid,CAST(x.typeDesc +‘, ‘+ cte.typeDesc ASVARCHAR(8000)),
CAST
( cte.typeDesc ASVARCHAR(8000)),length+ 1
FROM
product_CTE cte
INNER
JOIN (Select productid, typeid, typeDesc
                        FROM
product) X ON cte.productid = x.productid and cte.typeid < x.typeid)

 Select *
FROM product_CTE              
WHERE length= 2
Order BY typeDesc

Now there is a catch.  The recursion will show you all the possible combinations IE (LARGE, Blue) which is incomplete.  This is where the length comes in.  They way it makes sense to me is N-1 or the number of combinations, in this case 3 for product 1, minus 1.  This example will only give the rows for a specific productid and then you have to know that number of combinations for that product.

Anyway, I thought it was cool the way it came together.  On a side note, does anyone know of a widget I can use to post code?  It is pretty tedious in the editor and still doesn’t look good.

T-SQL Tuesday #34: Help! I Need Somebody!

Tsql TuesdayAs I am really just starting to blog on a regular basis, I thought this T-SQL Tuesday topic on someone who has helped you would be a good first topic to write about in this wonderful SQL community.

I took two database classes in college and I can still remember saying to myself–”I will NEVER be a dba.”  I was actually on track to become a network admin and was studying for my CCNA.  It still makes me laugh when I think about it.

My first job out of college was with a company called IndigeTech, which is now part of CodeX, Inc.  At the time IndigeTech was a small consulting firm and I was their internal IT guy and I had the privilege of working under the tutelage of José Castaños.   José taught me several important things

  1. How to find answers to questions I didn’t know.
  2. How to form good questions so I could get information from other people.
  3. Don’t evade work you don’t know how to do.

All of these points have played a role in my career development and I am so grateful I had challenging experiences early in my career so I would be willing to take on more challenging assignments later.  Isn’t that how we learn best–by solving a problem?

Two others I should thank are Robert Pollard, who convinced me to apply for my first DBA position, and Paul Oster, who told me I should go to our local SQL Server users group meeting.  He kept pestering me to go and I haven’t missed many since.  :)

Going on a SQL Cruise!

I have had a pretty good life on the D list as a DBA here in RVA and I am looking to bump it up a notch–you know, take it to a new level.  I have found so many different SQL Bloggers and community folk that really know their stuff.  Along the way, I came across a group that offers classes on week-long cruise!  SQL Cruise invites a wide variety of talented folks on a cruise and while at sea, they have classes and then use some of the social time to continue discussions, network, and address other questions.

I am going on a Cruise!

I was somewhat hesitant about it–I have never been on a cruise and wasn’t sure it was the right work/play combo; however, after talking to fellow Richmonder (Well, metro Richmond–way metro) Wayne Sheffield, who had gone on a cruise this year and thought it was fantastic, I decided it was worth pursuing.  I had to convince the Mrs, and now we are both headed to a cruise leaving from Miami in January.  SQL Cruise is run by Tim Ford, and while I won’t actually meet him until January, he seems like a decent fellow and he has had some pretty good talent on deck.  This Miami cruise has some folks I am less familiar with, but I am looking forward to meeting them and I have been pursuing their blogs to see what might be in store.

I am sure I will have more to say on this topic as we get a little closer, but if you are interested, check out the FAQ page and wish me well, cause I am going cruising and maybe I’ll see you onboard!

Powershell in SQL Server

I have yet to really scratch the surface in using Powershell to manage my SQL Server; however, from time to time I have used a powershell script to delete files from the server on an ongoing process.  Because I am still a Powershell novice, I get most of my scripts from the Scripting Guy.  He does a good job explaining what the variables do and mean and I invite you to check that post out.

This example is in SQL Server 2008.  Putting into SQL Server Agent is fairly straightforward.  I choose a new step and choose a Type of Powershell.  This script will delete all the files in my D:\Backups folder that are older that 5 days.