A Fun Thanksgiving Day Query.

Download the Thanksgiving query sample code here.

I thought it would be fun to put together a query to get us in the spirit of Thanksgiving dinner tonight.

This query is using techniques from another recent posting on CSV formatting query output.

 

Here is my Thanksgiving gift to you, the Thanksgiving day query.

 

Don’t copy and paste, your browser may mess up the single quotes… Instead download the Thanksgiving query sample code here.

-- a fun Thanksgiving day sql query
SELECT 'turkey'
UNION
SELECT 'stuffing'
UNION
SELECT 'cranberry sauce'
UNION
SELECT 'yams'
UNION
SELECT 'sweet potato pie'
UNION
SELECT 'gravy'
UNION
SELECT 'beans'
UNION
SELECT 'green bean casserole'
UNION
SELECT 'mashed potatos'
UNION
SELECT 'collard greens'
UNION
SELECT 'beer'
UNION
SELECT 'cider'
UNION
SELECT 'pumpkin pie';

GO

-- but thats too much food, 
--so lets keep on the diet track with only 5 items for turkey day dinner

SELECT TOP 5 fooditem FROM (
SELECT 'turkey' AS fooditem
UNION
SELECT 'stuffing'
UNION
SELECT 'cranberry sauce'
UNION
SELECT 'yams'
UNION
SELECT 'sweet potato pie'
UNION
SELECT 'gravy'
UNION
SELECT 'beans'
UNION
SELECT 'green bean casserole'
UNION
SELECT 'mashed potatos'
UNION
SELECT 'collard greens'
UNION
SELECT 'beer'
UNION
SELECT 'cider'
UNION
SELECT 'pumpkin pie' ) AS t1
ORDER BY Newid(); -- throw in some random ordering

GO
-- ok, but lets throw it into a comma delimited list rather than a table

SELECT Substring((SELECT ', ' + fooditem
FROM (
SELECT TOP 5 fooditem FROM (
SELECT 'turkey' AS fooditem
UNION
SELECT 'stuffing'
UNION
SELECT 'cranberry sauce'
UNION
SELECT 'yams'
UNION
SELECT 'sweet potato pie'
UNION
SELECT 'gravy'
UNION
SELECT 'beans'
UNION
SELECT 'green bean casserole'
UNION
SELECT 'mashed potatos'
UNION
SELECT 'collard greens'
UNION
SELECT 'beer'
UNION
SELECT 'cider'
UNION
SELECT 'pumpkin pie' ) AS t1 ORDER BY Newid()) AS t2
FOR XML PATH('')),3,10000000) AS list

 

Here is the output…

Happy Thanksgiving Everyone!

Converting part of a result set to a comma separated list

One of the common task that I have come across is the need to convert a list of results into a comma separated list.  There are many ways to use this, one if which is in reports.

Download the AsCSV.sql sample here.

Often times I have told developers, here is how you do it, and if you Google on it you will find some great samples.  Now it is time that I provide my own sample. I call this the FOR XML hack since it used the FOR XML command, without actually involving any XML at all.

-- first query showing columns 
SELECT c.firstname + ' ' + c.lastname
  FROM  [AdventureWorks].[HumanResources].[Employee] e
  INNER JOIN [AdventureWorks].[Person].[Contact] c
          ON c.contactid = e.contactid
  ORDER BY c.lastname;
 GO

-- second query as csv 
SELECT Substring((SELECT ', ' + c.firstname + ' ' + c.lastname
  FROM  [AdventureWorks].[HumanResources].[Employee] e
  INNER JOIN [AdventureWorks].[Person].[Contact] c
          ON c.contactid = e.contactid
  ORDER BY c.lastname
FOR XML PATH('')),3,10000000) AS list;

The output from the above queries will look like this.

 

Although that is useful, and I have on many occasions used a very similar query, where its gets interesting is to create a comma seperated list as part of a result set, not as the whole result set.

 

For example if you have the following list, it would be interesting to show a csv list of employees that have each title.

SELECT e.title, c.firstname + ' ' + c.lastname
  FROM  [AdventureWorks].[HumanResources].[Employee] e
  INNER JOIN [AdventureWorks].[Person].[Contact] c
          ON c.contactid = e.contactid
  ORDER BY e.title;


But I want it to look like this…

 

You can do it with a sub query in the list of columns.

 

SELECT e.title,

( SELECT Substring((SELECT ', ' + c1.firstname + ' ' + c1.lastname
  FROM  [AdventureWorks].[HumanResources].[Employee] e1
  INNER JOIN [AdventureWorks].[Person].[Contact] c1
          ON c1.contactid = e1.contactid
  WHERE e1.title = e.title
  ORDER BY c1.lastname
FOR XML PATH('')),3,10000000) AS list ) AS employeelist

  FROM  [AdventureWorks].[HumanResources].[Employee] e
  GROUP BY e.title;

So even if you can do the CSV formatting in a web page before it is displayed to the user, it may be easier to do it in the database like this.