Archive | Introduction RSS feed for this section

The Joys of @@IDENTITY, Scope_Identity and IDENT_CURRENT

31 Mar

For several years I have happily relied on @@IDENTITY to return the latest identity of a row when inserting data. However, I have started using the OUTPUT clause and have discovered the entertainment to be had in getting the latest identity when writing to two or more tables with what can appear to be one command. Continue reading

UnPivot – Basic Example

23 Dec

In a previous posting I described a simple example of Pivot – a much used feature which I personally have problems recalling the syntax of (hence the article in the first place). It’s lesser known relative in UnPivot, which is designed to do the opposite – it transforms columns of data into rows.

In the following example I have a few details about properties – the number of Bedrooms, Garages, Gardens and Attic Spaces. I’ve used this as an example because the column names will make it more apparent how the query and the results are connected.

I want to transform the data from showing the property features as column names to actually have those column names listed as data, along with the values that these columns contain:


First of all – the test data:

CREATE TABLE #Properties 
      PropertyID INT , 
      Attic_Spaces INT , 
      Bedrooms INT , 
      Garages INT , 
      Gardens INT 
    ) ; 

INSERT  INTO #Properties 
        ( PropertyID, Attic_Spaces, Bedrooms, Garages, Gardens ) 
VALUES  ( 101, 1, 2, 0, 1 ), 
        ( 102, 2, 1, 1, 2 ), 
        ( 103, 1, 2, 1, 2 ), 
        ( 104, 1, 3, 2, 2 ) ; 

FROM    #Properties ;   

The basic construct of an UnPivot is quite simple:

SELECT PropertyID, 
	   FROM #Properties p 
	   UNPIVOT ( NumberOf FOR PropertyFeature IN ( Attic_Spaces, Bedrooms, Garages, Gardens ) ) AS unpvt ; 

This will select PropertyID from the #Properties table. Then it will select the columns ‘PropertyFeature’ and ‘NumberOf’ from the UNPIVOT part of the query. In this section I have asked it to create a column called ‘PropertyFeature’, which is taken from the columns called ‘Attic_Spaces’, ‘Bedrooms’, ‘Garages’ and ‘Gardens’. The value that is held within those ‘original’ columns (within the #Properties table) will be stored in the column called ‘NumberOf’ within the UNPIVOT section.

The alias for the #Properties table isn’t required, it’s just a habit of mine. However, the alias for the UNPIVOT (the ‘AS unpvt’) is mandatory even though you see no reference to it.

This may not be the most accurate description of what it is really doing, but it is how my brain gets around the syntax and that’s good enough for me, for now.

Why Now?

18 Sep

I take notes all day long as I work with SQL Server. Short scribbles, brief explanations, small novels – all committed to paper or some electronic format as I go through my professional career. The theory is sound and generally quite useful.

Then I find I have so many notes that I have trouble actually finding the stuff. Or I move jobs and leave various documents behind, or buried in the corner of a spare room as evidence of a previous life. It gets to the point that I know I have a note somewhere that may be useful but can’t find it; Google doesn’t work with the pile of A4 binders festering in the corner.

So, what to do about it? One of the most useful tools I find are the blogs of those people that not only can understand a subject but are able to describe it in a way that mere mortals can understand – a talent indeed. This gave me the idea of creating my own blog. Not necessarily to pass on the knowledge (although if it does that then I’ll consider it a bonus) but more to force me to make sensible, presentable notes that I myself can turn to when required.

Sometimes (probably quite a lot of the time, actually) my posts will consist of links for various subjects of interest to myself, generally based on whatever I’m working on at the time. Credit will be given to any pieces that I’ve used for my own articles. Half of the skill in learning something is knowing where to look and I’ll endeavour to show how I’ve pieced something together, because that can be quite useful too and any other material I’ve used also deserves credit.

Anyway, enough waffle. Articles may be sporadic but hopefully will also be of use to others. Time will tell.

Image Credit :