Dynamically / conditionally split a single dataset grouped by states into multiple datasets by individual states - TSQL

Question

Please note that all states will not have data each time the process runs. Thanks for assisting.

Source dataset

output datasets

Answer 1

Agree. I'm holding each result set in a cte or temp table, then trigger an email subscription to send the data. Each result set goes to a different distro list. It is because of this that I need to split the table variable data.

OK, so if you are going to use database mail to send the emails, it starts to make a little more sense. I say a little more, because I'm not really a fan of that solution. There are also some challenges, since you cannot use the @query parameter to read from the table variable. But you could form a body from it.

You would set up a cursor like this:

DECLARE @cur CURSOR,
        @country nvarchar(50),
        @state   nvarchar(50)

SET @cur = CURSOR STATIC LOCAL FOR
   SELECT DISTINCT conutry, state FROM @tablevariable

OPEN @cur

WHILE 1 = 1
BEGIN 
   FETCH @cur INTO @country, @state
   IF @@fetch_status <> 0
     BREAK

   -- Run some query on the table variable to form a body of the mail.
   -- Also retrieve the recipent(s) for this state.

   EXEC sp_send_dbamil ....
END

If this email subscription is client-side, I still think it is best to split this in the client.

Answer 2

Hi @Francis, W

If I understand correctly, you can use statements like this to import partial results into a temporary table.

;with CTE as(
  select *,dense_rank()over(order by State) as num from test)
select Country,State,City,Zip into #t1 from CTE where num = 1;

;with CTE as(
  select *,dense_rank()over(order by State) as num from test)
select Country,State,City,Zip into #t2 from CTE where num = 2;

;with CTE as(
  select *,dense_rank()over(order by State) as num from test)
select Country,State,City,Zip into #t3 from CTE where num = 3;

;with CTE as(
  select *,dense_rank()over(order by State) as num from test)
select Country,State,City,Zip into #t4 from CTE where num = 4;

;with CTE as(
  select *,dense_rank()over(order by State) as num from test)
select Country,State,City,Zip into #t5 from CTE where num = 5;

Best regards,

Percy Tang

Answer 3

Another approach outside of using cursor.

-- example set
IF OBJECT_ID('tempdb..#SourceSet') IS NOT NULL 
	DROP TABLE #SourceSet

	CREATE TABLE #SourceSet (
	Country VARCHAR(50),
	State VARCHAR(50),
	City VARCHAR(50),
	Zip INT
	)


	INSERT INTO #SourceSet VALUES 
	('United States', 'Alabama', 'Birmingham', 35203),
	('United States', 'California', 'Alpine', 91901),
	('United States', 'California', 'Auburn', 95603),
	('United States', 'Florida', 'Altamonte Springs', 32701),
	('United States', 'New York', 'Central Valley', 10917),
	('United States', 'New York', 'Cheektowga', 14227),
	('United States', 'New York', 'Clay', 13041)

	-- Create flags for different levels of region
	IF OBJECT_ID('tempdb..#flags') IS NOT NULL 
		DROP TABLE #flags

		SELECT *,
		DENSE_RANK() OVER (ORDER BY Country, State) AS StateCount
		INTO #flags
		FROM #SourceSet set1

		--SELECT * FROM #flags

  DECLARE @State INT = 1
  DECLARE @StateMax INT = (SELECT MAX(StateCount) FROM #flags)

  WHILE @State <= @StateMax 
	BEGIN

	DECLARE @StateName VARCHAR(50) = (SELECT DISTINCT State FROM #flags WHERE StateCount = @State)
	DECLARE @SQL VARCHAR(MAX) = CONCAT(
	'IF OBJECT_ID(','''State', REPLACE(@StateName ,' ',''), '''', ') IS NOT NULL 
	 DROP TABLE State',REPLACE(@StateName ,' ',''),

		' CREATE TABLE State',REPLACE(@StateName ,' ',''), ' (
		Country VARCHAR(50),
		State VARCHAR(50),
		City VARCHAR(50),
		Zip INT
		)
		')
		--PRINT(@SQL)
		EXEC(@SQL)

		DECLARE @SQL2 VARCHAR(MAX) = CONCAT(
		' INSERT INTO State', REPLACE(@StateName ,' ',''),
		' SELECT Country, State, City, Zip 
		FROM #flags WHERE State = ','''', @StateName, '''')
		--PRINT(@SQL2)
		EXEC(@SQL2)

		SET @State = @State + 1
	END

	SELECT * FROM StateAlabama
	SELECT * FROM StateCalifornia
	SELECT * FROM StateFlorida
	SELECT * FROM StateNewYork