Comma Delimited List Day (SQL)

Two hand to forehead . . . "Smack" issues.

Neither support or contribute to best practices but manager or boss says. "Hey we are in the making folks happy business"

Issue 1:
Someone asks for a report estimate, you say "No big deal" you get into the report and see that they want everything rolled up into a comma delimited list from individual values in the table. So you think about how, without scripting something on the code end, you can present the data via SQL.

This is how in sql 2005 - 2008 and back to no big deal

Thanks to MSDN for providing simple code:
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist

(If you are still using SQL 2000 for some reason (like say the economy collapsed and the company thought updating tech was at the bottom of the priority list)the link above tells you how to do it there as well)

SELECT
t1.TeamID,
MemberList = substring((SELECT ( ', ' + FirstName )
                        FROM TeamInfo t2
                        WHERE t1.TeamID = t2.TeamID
                        ORDER BY
                           TeamID,
                           FirstName
                        FOR XML PATH( '' )
                       ), 3, 1000 )FROM TeamInfo t1
GROUP BY TeamID

Issue 2:

This is a big one, so some developer for a big Membership database at one point in history was asked to create a form and store the data, they take a select list, or checkbox list variable and decide that they just throw that thing into the database as is creating a field that stores comma delimited data.

A few years later, 5 or so, some executive who needs to look at numbers decides that they want to count the answers and report against them in a fairly complex way. So what do you do.

Thanks to Rob Volk on SQL Team and Joe Celko : here's Rob's original post

Well you need a tally table, which, in this example is simply a table with a single column "N" that has integer values 1-8000

then do something similar to the following

SELECT office_id, manager,
NullIf(SubString(',' + cast(product_list as varchar(255)) + ',' , N , CharIndex(',' , ',' + cast(product_list as varchar(255)) + ',' , N) - N) , '') AS product
FROM Tally, office_product
WHERE N <= Len(',' + cast(product_list as varchar(255)) + ',') AND SubString(',' + cast(product_list as varchar(255)) + ',' , N - 1, 1) = ','  AND CharIndex(',' , ',' + cast(product_list as varchar(255)) + ',' , N) - N > 0

So now comma delimited do and undo, done!

Mix with the pivot results earlier and really there isn't much that you can't do.

Pivots Rule!! (Crosstab and Dynamic make meta data easy)

So today I'm pulling together something I found incredibly awesome and useful

Lot's of our sites are using database that for better or worse are starting to store data as metadata.
I'm not a particular fan because though generating the things that utilize this is easy, reporting against it is a whole other story. So I got into pivots.

So here's a simple example, of course this works great when you know the values in the table. But I'll show the starting point.

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

But I wanted to do something that worked on something dynamic:
I started here with Jeff Smith's excellent Dynamic SQL Crosstab Stored procedure
(don't loose this as this will work great with older SQL servers)

So I created this Stored Procedure using the new Pivot function in SQL server.

CREATE procedure [dbo].[GetPivot]
( @Select varchar(8000),
  @PivotCol varchar(100),
  @Summaries varchar(100),
  @OtherCols varchar(8000) = Null)
AS
set nocount on
set ansi_warnings off
declare @Vals varchar(8000);
declare @sql varchar(8000);
declare @sqlView varchar(8000);
set @Vals = '';
set @OtherCols= isNull(@OtherCols + ',','')
set @sql = 'select distinct convert(varchar(100),' + @PivotCol + ') as TPivot FROM (' + @Select + ') A'
create table #temp  (TPivot varchar(100))
insert into #temp
exec (@sql)
select @Vals = @Vals + ', [' + TPivot +']'
from #Temp
drop table #Temp
select @Vals = RIGHT ( @vals , len(@vals)-1)
set @SQLView = 'SELECT ' + @otherCols + @vals + ' FROM (' + @Select + ') up  PIVOT (' + @Summaries + ' FOR ' + @PivotCol+ ' IN (' + @vals + ')) AS pvt'
--Print @SQLView
exec (@SQLView)
set nocount off

So in the case above you would do this

exec GetPivot
'SELECT    [Month]
              , SaleAmount
      FROM      Sales',
[Month],
'sum(SaleAmount)',
'Sales'

I wanted to throw in order by because our questions came in a particular sequence:
So here's one with order by:

CREATE procedure [dbo].[GetPivotWithOrder]
( @Select varchar(8000),
   @PivotCol varchar(100),
   @Summaries varchar(100),
   @OtherCols varchar(100) = Null,
 @OrderBy varchar(800) = Null)
AS
set nocount on
set ansi_warnings off
declare @Vals varchar(8000);
declare @sql varchar(8000);
declare @sqlView varchar(8000);
declare @OrderByText varchar(8000);
declare @SelectForPivot varchar(8000);
set @Vals = '';
set @OtherCols= isNull(@OtherCols + ',','')
set @OrderByText= isNull('Order By ' + @OrderBy,'')
set @SelectForPivot = replace(@Select, @OrderBy + ',','')
set @OrderBy= isNull(', ' + @OrderBy,'')
set @sql = 'select distinct convert(varchar(100),' + @PivotCol + ') as TPivot ' + @OrderBy + ' FROM (' + @Select + ' ) A ' + @OrderByText
Print @sql
create table #temp  (TPivot varchar(100), orderBy int)
insert into #temp
exec (@sql)
select @Vals = @Vals + ', [' + TPivot +']'
from #Temp
order by orderBy
drop table #Temp
select @Vals = RIGHT ( @vals , len(@vals)-1)
set @SQLView = 'SELECT ' + @otherCols + @vals + ' FROM (' + @SelectForPivot + ') up  PIVOT (' + @Summaries + ' FOR ' + @PivotCol+ ' IN (' + @vals + ')) AS pvt'
Print @SQLView
exec (@SQLView)
set nocount off

The commented out line --Print (@SQLView) will generate the actual pivot code from the dynamic information letting you create views or simple pivots

This has led to many quick solutions to difficult problems for a particular site and may lead to greater contributions in the future.

Converting from Word to Wiki

The short way

How to move a Word Doc to a wiki

1. go to ckeditor thanks to Frederico Knabben
2. Paste as Word Doc to get HTML
3. Go To Html2Wiki thanks to David Iberri
4. Select appropriate Wiki tool
5. Convert, Copy and Paste format as appropriate

Getting an ID from insert

Here is an example of getting an id from an insert. For scalability put it in a transaction block.

           Dim myCommand As SqlCommand = conn.CreateCommand()


           myCommand.CommandText = "INSERT INTO  SBKBSearchLog  (UserID) " & _
                                               "VALUES (@UserID); SELECT SCOPE_IDENTITY() ;"
           myCommand.Parameters.Add("@UserID", SqlDbType.Int).Value = userID

           Dim searchID As Integer = myCommand.ExecuteScalar

Nailing it Down

So in my move for a stronger position in the direction of Architect, I have become the Tools librarian at our company. In my exploration I've seen a number of sites that were created to do just this sort of thing. As if many developers in passing have made attempts to consolidate information somewhere.

About three years ago I began to get frustrated with the numerous sharepoint sites and wiki sites dedicated to organizing information that I did exactly the same thing.

Every time I got frustrated with the lack of information on tips I've done before I would put this information somewhere. It just wasn't the same somewhere. I have a Google site, a Google journal, the wiki site from work and apparently this blog. Well I'm done, I'm nailing it down.

I'm here on the blogger for now. Look here for info on ColdFusion, .Net and maybe some Gaming comments. I've read a friends blog and it seems he didn't stop being who he was just because he's a professional and I don't intend to either.