/*
Problem: You have a table like this, recording what people eat.
jane pie
fred sandwich
fred sausage
fred biscuits
jane cake
Rajiv pastie
Rajiv Croissants
You want it to look like this...
jane pie, cake
fred sandwich, sausage, biscuits
Rajiv pastie, Croissants
or maybe even you have the latter and you want the former!
Robyn and I showed how this sort of thing was done in the SQL Server
Grouping Workbench under the title 'enumerating group members'. Let's
use a slightly more complicated example to show how it is done....
*/
CREATE TABLE #schedule
(
EventStart DATETIME,
description VARCHAR(2000)
)
/* so now we can get some sample data into the table */
INSERT INTO #schedule ( EventStart, description )
SELECT '12 Oct 2007 10:00', 'Meeting with Bill'
INSERT INTO #schedule ( EventStart, description )
SELECT '12 Oct 2007 12:00', 'visit Crawley site'
INSERT INTO #schedule ( EventStart, description )
SELECT '12 Oct 2007 13:00', 'Lunch with Evelyn'
INSERT INTO #schedule ( EventStart, description )
SELECT '12 Oct 2007 14:00', 'Review of CDW27'
INSERT INTO #schedule ( EventStart, description )
SELECT '12 Oct 2007 17:00', 'admin'
INSERT INTO #schedule ( EventStart, description )
SELECT '13 Oct 2007 10:00', 'Finance committee'
INSERT INTO #schedule ( EventStart, description )
SELECT '13 Oct 2007 12:00', 'lunch with Bob'
INSERT INTO #schedule ( EventStart, description )
SELECT '13 Oct 2007 14:00', 'Weekly SH meeting'
INSERT INTO #schedule ( EventStart, description )
SELECT '14 Oct 2007 11:00', 'interviews'
INSERT INTO #schedule ( EventStart, description )
SELECT '14 Oct 2007 15:00', 'office plan presentation'
INSERT INTO #schedule ( EventStart, description )
SELECT '14 Oct 2007 16:00', 'performance reviews'
/* now we produce the report that enumerates the group members */
DECLARE @list VARCHAR(8000)
SELECT @List = COALESCE(@list + ',', '') + '|'
+ CONVERT(CHAR(11), eventStart, 113) + '| '
+ RIGHT(CONVERT(CHAR(17), eventStart, 113), 5)
+ ' - ' + description + '|'
+ CONVERT(CHAR(11), eventStart, 113) + '|'
FROM #schedule
ORDER BY eventstart
/* we'll put the results in a temporary table so we can show the
reverse process
*/
CREATE TABLE #DailySchedule
(
[date] CHAR(11),
events VARCHAR(2000)
)
INSERT INTO #DailySchedule
( [date], events )
SELECT
[Date] = CONVERT(CHAR(11), TheDate),
[Events] = LEFT(members, CHARINDEX(',|', members + ',|') - 1)
FROM ( SELECT TheDate,
'members' = REPLACE(SUBSTRING(@list,
CHARINDEX('|' + dates.theDate + '|', @list),
8000),
'|' + dates.theDate + '|', '')
FROM ( SELECT [theDate] = CONVERT(CHAR(11), eventStart, 113)
FROM #schedule
GROUP BY CONVERT(CHAR(11), eventStart, 113)
) dates
) g
SELECT * FROM #DailySchedule
/* job done, but you may want to do the process in reverse.
no problem let's take the end-results and get back to the
original */
DECLARE @rollout VARCHAR(8000)
SELECT @rollout = COALESCE(@rollout, '
') + [date]
+ ' ' + REPLACE(REPLACE(events, ', ', '
' + [date] + ' '), ' - ', '|') + '
'
FROM #DailySchedule
SELECT
[EventStart] = CONVERT(DATETIME, SUBSTRING(entry,
1,
CHARINDEX('|', entry) - 1)),
[Description] = SUBSTRING(entry,
CHARINDEX('|', entry) + 1,
8000)
FROM ( SELECT [entry] = LEFT(line,
CHARINDEX(CHAR(13) + CHAR(10),
line) - 1)
FROM ( SELECT [line] = SUBSTRING(@rollout, number + 2, 8000)
FROM numbers
WHERE SUBSTRING(@rollout, number, 2) = CHAR(13) + CHAR(10)
AND number < LEN(@rollout)) - 2
) g
) h
ORDER BY eventstart
--Remember that you'll need a number table for this
CREATE PROCEDURE spMaybeBuildNumberTable
@size INT=10000
AS
BEGIN
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM dbo.sysobjects
WHERE id = OBJECT_ID(N'[dbo].[Numbers]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[Numbers](
[number] [int],
CONSTRAINT [Index_Numbers] PRIMARY KEY CLUSTERED
(
[number] ASC
) ON [PRIMARY]
) ON [PRIMARY]
DECLARE @ii INT
SELECT @ii=1
WHILE (@ii<=@size)
BEGIN
INSERT INTO NUMBERS(NUMBER) SELECT @II
SELECT @II=@II+1
END
END