Thursday, March 26, 2009

Creating Pivoted Joins in SQL Server 2005

You can take a vertical result set and turn it into a horizontal one, like this:

DECLARE @TBLTEMP TABLE (Make VARCHAR(32), Model VARCHAR(12), License VARCHAR(12)); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'GS45PK1'); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'KL45QZ1'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M5', 'QL4AQ91'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', '345JK12'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', 'RT5JJK2'); SELECT DISTINCT tmp.Make, [380i], [M5], [M3] FROM @TBLTEMP tmp INNER JOIN (SELECT Make, Model FROM @TBLTEMP) PivotTable PIVOT ( COUNT(Model) FOR Model IN ([380i],[M5],[M3]) ) PivotColumns ON tmp.Make = PivotColumns.Make;

The preceding query takes a count of how many licenses belong to which BMW models, and displays them horizontally. The output should be something like this:

Make 380i M5 M3 -------------------------------- ----------- ----------- ----------- BMW 2 1 2

If were were to utilize a simple select statement, it would look something like this:

DECLARE @TBLTEMP TABLE (Make VARCHAR(32), Model VARCHAR(12), License VARCHAR(12)); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'GS45PK1'); INSERT INTO @TBLTEMP VALUES ('BMW', '380i', 'KL45QZ1'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M5', 'QL4AQ91'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', '345JK12'); INSERT INTO @TBLTEMP VALUES ('BMW', 'M3', 'RT5JJK2'); SELECT Make, Model, COUNT(*) AS [Count] FROM @TBLTEMP GROUP BY Make, Model; The results being this: Make Model Count -------------------------------- ------------ ----------- BMW 380i 2 BMW M3 2 BMW M5 1

No comments: