Monday, December 6, 2010

Single line result-set

I should preface this by saying that SQL Server really is not for the presentation of the data.  This could/should be done at the front end, by the countless applications available for the reporting of the data.  Having said that, more than anybody, I realize that sometimes you will have to make it pretty at the SQL layer.  Here's a real quick one for manipulating your data with self-joins, to return multiple records within a single line of results.

-- Two values, two row(s) returned:

     SELECT c.FirstName,c.LastName,c.DateOfBirth
     FROM dbo.Customers c
     WHERE c.DateOfBirth IN
          SELECT temp.DateOfBirth
          FROM dbo.Customers temp
          GROUP BY temp.DateOfBirth
          HAVING (((Count(*))>1))

     FirstName   LastName    DateOfBirth
     Karen         Williams       1977-03-18 00:00:00
     Betsy          Simpson       1977-03-18 00:00:00

-- Same two values, one row returned:

          a.FirstName, a.LastName, a.DateOfBirth,
          b.FirstName, b.LastName, b.DateOfBirth
          dbo.Customers a INNER JOIN dbo.Customers b
             ON a.DateOfBirth=b.DateOfBirth
             AND (a.FirstName > b.FirstName)
          OR (a.FirstName=b.FirstName AND a.LastName > b.LastName)

FirstName  LastName  DateOfBirth                 FirstName   LastName   DateOfBirth
Karen        Williams     1977-03-18 00:00:00   Betsy          Simpson     1977-03-18 00:00:00

NOTE:    You want to be careful with this, given the size of the targeted result-set.

No comments:

Post a Comment