May WARDY IT Community Dinner

The WARDY IT Community Dinner is a great opportunity to network with other IT professionals in order to share knowledge, information and experiences. The May WARDY IT Community Dinner is being held on Monday the 21st of May. To RSVP to be at this event and find out what other people are doing and how they are doing it send an email to peter @ wardyit.com.

Note: The WARDY IT Community Dinner is typically held on the second Monday of the Month, however due to MDEC and the APAC SharePoint Conference the May Community Dinner is one week later.

WHERE Sticks | Chalk Hotel | 735 Stanley Street | Woolloongabba | http://www.chalkhotel.com.au
WHEN Monday 21st May | 6:30PM
RSVP Friday 18th May
Please note that you are responsible for the cost of your own dining.

QUT SQL Server Presentation

If you are a QUT student and interested in SQL Server then why not pop along to the SQL Server Talk @ QUT on Friday the 4th of May at 3:00PM. At this SQL Server Talk I will be presenting on the importance of SQL Server in the market place as well as some of the new features in SQL Server 2005. Not only will you get to find out more about SQL Server and the Brisbane SQL Server community there will also be free pizza and a SQL Server 2005 Evaluation Edition DVD for everyone in attendance plus an opportunity to win some great swag. To find out more about this presentation and to register your attendance refer to Peter Ward presents SQL Server 2005 to QUT students.

April QLD SQL Server User Group

This month we are in for a real treat as we have Danny Tambs from Microsoft Consulting Services presenting a Mystery Session. He intends to present on either Partial Database Availability or Performance Tuning. Based on the whitepaper SQL Server 2005 Waits and Queues that Danny worked on this month’s User Group is one not to be missed.

WHERE: Microsoft Brisbane Office, Level 9, Waterfront Place, 1 Eagle Street
http://www.microsoft.com/australia/technet/usergroups.aspx
WHEN: Thursday, 26th April | 5:30PM for a 6:00PM start

Add this event to your Outlook Calendar

If you will be attending, please RSVP via email to peter @ wardyit.com to help us plan refreshments (normally pizzas and drinks).

Unpivot Comma Delimited Column

I recently came across a scenario where the description in a column was being stored in a comma delimited column as illustrated by the Products table below. This table needed to be unpivoted so that the columns where rotated into column values. The following example illustrates how this can be achieved using an auxiliary numbers table.

CREATE TABLE Products

(

        Category INT,

        ProductDescription VARCHAR(1000)

)

INSERT Products SELECT 1, ‘Fruit, Vegetables’

INSERT Products SELECT 2, ‘Cooking’

INSERT Products SELECT 2, ‘Cereal, Deserts, Spices’

INSERT Products SELECT 3, ‘Cat Food, Dog Food, Pet Care’

— ~~~ START Generate Auxiliary Table ~~~

CREATE TABLE dbo.Nums

    (

        i INT NOT NULL IDENTITY

    )

INSERT dbo.Nums DEFAULT VALUES

WHILE SCOPE_IDENTITY() < 500

INSERT dbo.Nums DEFAULT VALUES

— ~~~ END Generate Auxiliary Table ~~~

SELECT Category,

    CASE

        WHEN SUBSTRING(‘,’ + ProductDescription + ‘,’, i, 1) = ‘,’

            THEN LTRIM(RTRIM(SUBSTRING(‘,’ + ProductDescription + ‘,’, i + 1,

                CHARINDEX(‘,’, ‘,’ + ProductDescription + ‘,’, i + 1) i 1 )))

    END

FROM    Nums cross join Products

WHERE    i BETWEEN 1 AND LEN(‘,’ + ProductDescription + ‘,’) 1

AND        SUBSTRING(‘,’ + ProductDescription + ‘,’, i, 1) = ‘,’