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:
Post a Comment