Tony Rogerson's ramblings on SQL Server

Ok, so I’ve done this one a few times the other way in but what about if you need to create a result set like below…

Source Data

Result Required

FOR XML only gets you so far, well – 1 row to be exact, so how do we break it out for the multiple rows per grp?

The trick is to use a sub-query on the SELECT and wrap your FOR XML logic into that…

declare @tb table (
    agrp int,
    aval int
    )
insert @tb values( 1, 1 )
insert @tb values( 1, 2 )
insert @tb values( 1, 3 )
insert @tb values( 2, 1 )
insert @tb values( 2, 2 )
insert @tb values( 2, 3 )
insert @tb values( 2, 4 )

select r.agrp,
       collapsed = left( r.collapsed, len( r.collapsed ) - 1 )
from (
    select a.*,
           collapsed =    (    select cast( aval as varchar(max) ) + ',' as [text()]
                            from @tb b
                            where b.agrp = a.agrp
                            for xml path( '' )
                            )
    from (
        select distinct agrp
        from @tb ) as a
    ) as r

The majority of time I have my head in technical content and sites, I followed a link given out at yesterdays data mining event that takes you to the UK sql server home page which has a lot of good marketing and general information which are really good backgrounders when you are trying to get to grips with SQL Server "the incredibly broad product" - the url is: http://www.microsoft.com/uk/sqlserver/default.mspx.

David Hobbs-Mallyon and Ellie Hargreaves who are the SQL Server Product Managers here in the UK give a 12 minute video Overview on SQL Server 2008.

Update: there are some good technical stuff off that site too (http://www.microsoft.com/uk/sqlserver/itpro.mspx).

Unfortunetly Dave didn't notice that on camera it looks like his shirt is hanging out - what do you think?

I'm at the Business Intelligence and Data Mining seminar organised by Microsoft taken by Rafal Lukawiecki in London; Rafal is a great speaker - very articulate on the subject and entertaining, anyway here is the recording from last years Data Mining session he did here in London:

In this one-day seminar, Rafal Lukawiecki aims to show IT Professionals how data mining can be used in IT infrastructure to support real business scenarios demystifying the perception that Data Mining is complex, untested or only for specialists. This has become possible since Microsoft has taken the technology to new levels making it accessible to all. In four easy-to-understand yet packed with practical information sessions you will learn about what Data Mining and Business Intelligence can do for you, how to deploy and manage it, how to use it, and how to make it available to other parts of your IT environment. 

http://www.microsoft.com/emea/spotlight/event.aspx?id=99

Slides from Martin Bell talk on Table Valued Parameters at the Manchester User Group 26th Feb 2009

UG20090226 Table Valued Parameters.zip

UG20090226 TVP Demo Scripts.zip

Demo SQL from Tony Rogerson's presentation

UG20090226 DEMOSCRIPTS.zip

Next Thurs (19th) March London meeting will be Ed Vassie on Managing SQL Server patches and Christian Bolton on SQL Server and Storage; next Friday (20th) we have a full day seminar on Integration Services taken by leading expert Allan Mitchell in Harpenden. Coming on 26th March Rafal Lukawiecki on Going from Data to Decisions with Microsoft solutions and not to forget SQL Bits community conference.

:: 19th March, Evening -> London
http://sqlserverfaq.com/events/153/Managing-SQL-Server-Storage-Internals.aspx

Peer networking - absolutely great place to meet fellow SQL professionals, find people looking for jobs or to find really great candidates; SQL Nuggets - a number of short sharp tips or demo's around SQL Server.

Ed Vassie will spend an hour on Managing SQL Server patches.
Christian Bolton will spend 45 minutes on the SQL Server Storage.

:: 20th March, Full Day Seminar -> Harpenden
http://sqlserverfaq.com/events/151/SSIS-Performance-Tuning-1-Day-Seminar-Underneath-the-covers-of-Pipeline-Design-and-Environment-Choices.aspx

Allan Mitchell will take a full day seminar on Integration Services - £250 ex VAT; there are a few places left - delegates limited to 15.

This one day course is designed for those people who have seen how fast SQL Server Integration Services can transfer data but want more. We will show you how to understand performance not only from within SSIS but also from without. We take a good look under the covers to see what SSIS thinks and we will also see the effects of design decisions you make.

SSIS out of the box is wonderfully fast but with understanding that speed can be turned up. In today’s world batch windows are becoming smaller and data quantity ever larger so having a truly performing ETL solution is an extremely sound investment.

:: 26th March, Rafal Lukawiecki on Going from Data to Decisions with Microsoft solutions
http://sqlserverfaq.com/events/160/Rafal-Lukawiecki-on-Going-from-Data-to-Decisions-with-Microsoft-solutions.aspx

Business intelligence is even more important during these tough economic times. People have to make really important decisions and critical strategic moves.

In this one-day seminar, Rafal Lukawiecki aims to show IT Managers and BI practitioners how to improve decision making and getting the valuable business insight by using Microsoft solutions. This seminar will look at an end-to-end approach to building and delivering a Microsoft BI solution, from automated data integration and consolidation to delivering insight in reports, scorecards, and Excel.  To make this seminar easier and more pleasant to follow we structured it so that the first half focuses on the goals of BI, performance management, and the knowledge worker. The second half will stress the underlying technologies and the development and deployment processes.

:: 28th March, SQL Bits
http://sqlbits.com

SQLBits IV will be taking place on March 28th 2009 in Manchester. SQLBits is the UK's largest SQL Server conference, organised by the SQL Server community for the SQL Server community; it features a mix of both internationally-recognised and local speakers covering all aspects of SQL Server from development to DBA to BI, and yet remains free to attend. All you have to do is register and turn up on the day!