Best Way to Split a Table into Smaller Tables Based on A Field Value

[Nassy]

Hi,

I am looking to create SQL that performs the reverse of a UNION statement.

A table has been set up for me that contains a list of week start dates (starting on a Sunday). The table tells you the week number each week start date relates to for a particular customer.


WStartDate FinWeekNo Customer
---------- ------ ---------
2006-05-03 1 Tesco
2006 12-03 2 Tesco
2006 19-03 3 Tesco
2006-05-03 4 Waitrose
2006 12-03 5 Waitrose
2006 19-03 6 Waitrose


What I need to do is to restructure this information into the following format:


WStartDate Tesco Week Waitrose Week
---------- ------ ---------
2006-05-03 1 4
2006 12-03 2 5
2006 19-03 3 6





Based on the requirement I am thinking of writing code which does the following:



1. Create a table Customers with a distinct list of customers from the first table

2. Loop through this table using a cursor (this table Customers will typically only have about 10 rows)

3. Create a temp table for each customer.

4. Join the temp tables together on their common Week Start Date fields.



Is this the best way however to go about 'bursting out' a table into smaller tables based on a field value? I am not certain at all. I have had a think about this but cannot think of a way of avoiding a loop.

Nassy

 

[vongrunt]

General idea (SQL2000):

set ansi_warnings off
select WStartDate, 
	min(case when Customer = 'Tesco' then FinWeekNo end) as [Tesco Week],
	min(case when Customer = 'Waitrose' then FinWeekNo end) as [Waitrose Week]
from blah
group by WStartDate

If number of customers is not known, then dynamic SQL is necessary - to build expression like above.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Nassy]

Hi,

I have managed to get this working using two stored procedures. I have one stored procedure that loops through a table Customers to get all the different customer names and one stored procedure that takes the name of the customer and uses it to dynamically append columns onto a table called Dim_Time

The code for anyone interested is underneath.



Create Procedure Customer_Loop as
Begin
DECLARE @Customer nvarchar(100)
DECLARE @cmd nvarchar(1000)
DECLARE Customer_Loop_Cursor CURSOR FOR
SELECT Customer
FROM Customers
OPEN Customer_Loop_Cursor
FETCH NEXT FROM Customer_Loop_Cursor into @Customer

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC Customer_Calendar @Customer
FETCH NEXT FROM Customer_Loop_Cursor into @Customer
END

CLOSE Customer_Loop_Cursor
DEALLOCATE Customer_Loop_Cursor

END

Create Procedure Customer_Calendar(@Customer nvarchar(2000)) as
BEGIN

Set ANSI_WARNINGS off

Declare @DropStatement NvarChar(500)
Declare @CreateTableStatement NVarChar(500)
Declare @InsertStatement NVarChar(500)
Declare @MergeStatement NvarChar(1000)

Select @DropStatement =
'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N''[dbo].[' + @Customer + ']'') AND OBJECTPROPERTY(id,
N''IsUserTable'') = 1) DROP TABLE [dbo].[' + @Customer + ']'

Select @CreateTableStatement = 'Create Table '+@Customer+'(WeekSDate datetime,' + @Customer + 'Week int,' + @Customer + 'Year int)'

Select @InsertStatement = 'Insert into '+@Customer+'(WeekSDate,'+@Customer+'Week,'+@Customer+'Year)
Select WeekSDate, CustomerWeek, CustomerYear from CustomerCalendars where CCT_CATEGORY = '''+@Customer+''''

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Dim_Time2]') AND OBJECTPROPERTY(id,
N'IsUserTable') = 1) DROP TABLE [dbo].Dim_Time2

Select @MergeStatement = 'Select dt.*, cus.'+@Customer+'Week, cus.'+@Customer+'Year into Dim_Time2 from Dim_Time dt
left outer join ' + @Customer+' cus on cus.WeekSDate = dt.Date'


Exec (@DropStatement)
Exec(@CreateTableStatement)
Exec(@InsertStatement)
Exec(@MergeStatement)
Drop Table Dim_Time
Select * into Dim_Time from Dim_Time2
Drop Table Dim_Time2
END



Nassy