What is the best way to implement a SQL Nested Cursor-CodePudding

Please can someone show me the best way to implement a Nested Cursor with the dataset provided?

As you will see there is already one Cursor that looks for the value listed in #TMP_CODE1, if there is a match then the select statement is executed and the results are inserted into the output table.

I'm trying to use a Nested Cursor to replicate the same process on the values stored in #TMP_CODE2.

I'd like to print the @Numbers in the same way that the @Letters have been inserted into the #Output table below:

  PRINT @Letters;
  INSERT INTO #Output ([Dummy Column1], [Dummy Column2]) VALUES ('Letters:', @Letters)
  INSERT INTO #Output ([Dummy Column1]) VALUES ('')

With each row grouped together and the code displayed above each result set.

The values should only inserted into the Output table if they are listed in the #TMP_CODE1 and #TMP_CODE2 temporary tables.

DROP TABLE IF EXISTS #TMP_CODE1
CREATE TABLE #TMP_CODE1(Letters varchar(3));
INSERT INTO #TMP_CODE1(Letters) VALUES 
  ('AAA'),('BBB'),('CCC'),('DDD'),('EEE'),('FFF');

DROP TABLE IF EXISTS #TMP_CODE2
CREATE TABLE #TMP_CODE2(Numbers varchar(3));
INSERT INTO #TMP_CODE2(Numbers) VALUES 
  ('111'),('222'),('333'),('444'),('555'),('666');

DROP TABLE IF EXISTS #Input
CREATE TABLE #Input (CODE1 varchar(3), [Dummy Column1] varchar (15), 
[Dummy Column2] varchar (15), CODE2 varchar(3), [Dummy Column3] varchar (15),
[Dummy Column4] varchar (15));

DROP TABLE IF EXISTS #Output
CREATE TABLE #Output (CODE1 varchar(3), [Dummy Column1] varchar (15), 
[Dummy Column2] varchar (15), CODE2 varchar(3), [Dummy Column3] varchar (15),
[Dummy Column4] varchar (15));

INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('AAA', 'DUMMY DATA 1 1', 'DUMMY DATA 2 1', '111', 'DUMMY DATA 3 1', 'DUMMY DATA 4 1')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('AAA', 'DUMMY DATA 1 2', 'DUMMY DATA 2 2', '111', 'DUMMY DATA 3 2', 'DUMMY DATA 4 2')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('AAA', 'DUMMY DATA 1 3', 'DUMMY DATA 2 3', '111', 'DUMMY DATA 3 3', 'DUMMY DATA 4 3')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('AAA', 'DUMMY DATA 1 4', 'DUMMY DATA 2 4', '222', 'DUMMY DATA 3 4', 'DUMMY DATA 4 4')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('AAA', 'DUMMY DATA 1 5', 'DUMMY DATA 2 5', '333', 'DUMMY DATA 3 5', 'DUMMY DATA 4 5')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('AAA', 'DUMMY DATA 1 6', 'DUMMY DATA 2 6', '555', 'DUMMY DATA 3 6', 'DUMMY DATA 4 6')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('BBB', 'DUMMY DATA 1 7', 'DUMMY DATA 2 7', '222', 'DUMMY DATA 3 7', 'DUMMY DATA 4 7')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('BBB', 'DUMMY DATA 1 8', 'DUMMY DATA 2 8', '111', 'DUMMY DATA 3 8', 'DUMMY DATA 4 8')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('BBB', 'DUMMY DATA 1 9', 'DUMMY DATA 2 9', '555', 'DUMMY DATA 3 9', 'DUMMY DATA 4 9')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('BBB', 'DUMMY DATA 1 10', 'DUMMY DATA 2 10', '444', 'DUMMY DATA 3 10', 'DUMMY DATA 4 10')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('BBB', 'DUMMY DATA 1 11', 'DUMMY DATA 2 11', '777', 'DUMMY DATA 3 11', 'DUMMY DATA 4 11')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('BBB', 'DUMMY DATA 1 12', 'DUMMY DATA 2 12', '888', 'DUMMY DATA 3 12', 'DUMMY DATA 4 12')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('BBB', 'DUMMY DATA 1 13', 'DUMMY DATA 2 13', '222', 'DUMMY DATA 3 13', 'DUMMY DATA 4 13')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('BBB', 'DUMMY DATA 1 14', 'DUMMY DATA 2 14', '111', 'DUMMY DATA 3 14', 'DUMMY DATA 4 14')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('BBB', 'DUMMY DATA 1 15', 'DUMMY DATA 2 15', '222', 'DUMMY DATA 3 15', 'DUMMY DATA 4 15')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('BBB', 'DUMMY DATA 1 16', 'DUMMY DATA 2 16', '666', 'DUMMY DATA 3 16', 'DUMMY DATA 4 16')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('CCC', 'DUMMY DATA 1 17', 'DUMMY DATA 2 17', '111', 'DUMMY DATA 3 17', 'DUMMY DATA 4 17')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('CCC', 'DUMMY DATA 1 18', 'DUMMY DATA 2 18', '111', 'DUMMY DATA 3 18', 'DUMMY DATA 4 18')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('DDD', 'DUMMY DATA 1 19', 'DUMMY DATA 2 19', '111', 'DUMMY DATA 3 19', 'DUMMY DATA 4 19')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('DDD', 'DUMMY DATA 1 20', 'DUMMY DATA 2 20', '222', 'DUMMY DATA 3 20', 'DUMMY DATA 4 20')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('DDD', 'DUMMY DATA 1 21', 'DUMMY DATA 2 21', '333', 'DUMMY DATA 3 21', 'DUMMY DATA 4 21')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('DDD', 'DUMMY DATA 1 22', 'DUMMY DATA 2 22', '555', 'DUMMY DATA 3 22', 'DUMMY DATA 4 22')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('EEE', 'DUMMY DATA 1 23', 'DUMMY DATA 2 23', '222', 'DUMMY DATA 3 23', 'DUMMY DATA 4 23')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('EEE', 'DUMMY DATA 1 24', 'DUMMY DATA 2 24', '111', 'DUMMY DATA 3 24', 'DUMMY DATA 4 24')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('EEE', 'DUMMY DATA 1 25', 'DUMMY DATA 2 25', '555', 'DUMMY DATA 3 25', 'DUMMY DATA 4 25')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('EEE', 'DUMMY DATA 1 26', 'DUMMY DATA 2 26', '444', 'DUMMY DATA 3 26', 'DUMMY DATA 4 26')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('EEE', 'DUMMY DATA 1 27', 'DUMMY DATA 2 27', '777', 'DUMMY DATA 3 27', 'DUMMY DATA 4 27')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('FFF', 'DUMMY DATA 1 28', 'DUMMY DATA 2 28', '888', 'DUMMY DATA 3 28', 'DUMMY DATA 4 28')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('FFF', 'DUMMY DATA 1 29', 'DUMMY DATA 2 29', '222', 'DUMMY DATA 3 29', 'DUMMY DATA 4 29')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('FFF', 'DUMMY DATA 1 30', 'DUMMY DATA 2 30', '111', 'DUMMY DATA 3 30', 'DUMMY DATA 4 30')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('FFF', 'DUMMY DATA 1 31', 'DUMMY DATA 2 31', '222', 'DUMMY DATA 3 31', 'DUMMY DATA 4 31')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('GGG', 'DUMMY DATA 1 32', 'DUMMY DATA 2 32', '666', 'DUMMY DATA 3 32', 'DUMMY DATA 4 32')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('GGG', 'DUMMY DATA 1 33', 'DUMMY DATA 2 33', '222', 'DUMMY DATA 3 33', 'DUMMY DATA 4 33')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('GGG', 'DUMMY DATA 1 34', 'DUMMY DATA 2 34', '111', 'DUMMY DATA 3 34', 'DUMMY DATA 4 34')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('GGG', 'DUMMY DATA 1 35', 'DUMMY DATA 2 35', '555', 'DUMMY DATA 3 35', 'DUMMY DATA 4 35')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('HHH', 'DUMMY DATA 1 36', 'DUMMY DATA 2 36', '888', 'DUMMY DATA 3 36', 'DUMMY DATA 4 36')
INSERT INTO #Input (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3], [Dummy Column4]) VALUES ('HHH', 'DUMMY DATA 1 37', 'DUMMY DATA 2 37', '555', 'DUMMY DATA 3 37', 'DUMMY DATA 4 37')
  
DECLARE @Letters varchar(3), @c cursor;
    
SET @c = cursor LOCAL FAST_FORWARD
    FOR SELECT Letters FROM #TMP_CODE1;

OPEN @c;
FETCH NEXT FROM @c INTO @Letters;

WHILE @@FETCH_STATUS = 0
BEGIN

  PRINT @Letters;
  INSERT INTO #Output ([Dummy Column1], [Dummy Column2]) VALUES ('Letters:', @Letters)
  INSERT INTO #Output ([Dummy Column1]) VALUES ('')

  INSERT INTO #Output (CODE1, [Dummy Column1], [Dummy Column2], CODE2, [Dummy Column3],[Dummy Column4])
  SELECT * FROM #Input WHERE CODE1 = @Letters

  FETCH NEXT FROM @c INTO @Letters;

END

SELECT * FROM #Output

So the structure I'm hoping to achieve would be like this:

This is just for AAA, I would like to be able to format all the data provided in the example code in this format.

I'm using SQL Server so would appreciate answers in standard SQL please.

Thanks

CodePudding user response：

I will preface this by saying that this really should be done in the presentation layer, rather than in SQL.

Be that as it may, you can actually get all the rows you want, in the order you want, with careful use of GROUPING SETS and ORDER BY:

SELECT
  CODE1           = CASE WHEN GROUPING(i.[Dummy Column1]) = 0 THEN CODE1 END,
  [Dummy Column1] = CASE WHEN GROUPING(i.CODE2) = 1 AND GROUPING(i.[Dummy Column1]) = 1
                           THEN 'Letters'
                         WHEN GROUPING(i.[Dummy Column1]) = 1
                           THEN 'Numbers'
                         ELSE i.[Dummy Column1] END,
  [Dummy Column2] = CASE WHEN GROUPING(i.CODE2) = 1 AND GROUPING(i.[Dummy Column1]) = 1
                           THEN i.CODE1
                         WHEN GROUPING(i.[Dummy Column1]) = 1
                           THEN i.CODE2
                         ELSE i.[Dummy Column1] END,
  CODE2           = CASE WHEN GROUPING(i.[Dummy Column1]) = 0 THEN CODE2 END,
  i.[Dummy Column3],
  i.[Dummy Column4]
FROM #Input i
GROUP BY GROUPING SETS (
    (i.CODE1),
    (i.CODE1, i.CODE2),
    (i.CODE1, i.CODE2, i.[Dummy Column1], i.[Dummy Column2], i.[Dummy Column3], i.[Dummy Column4])
)
ORDER BY
  i.CODE1,
  GROUPING(i.CODE2) DESC,
  i.CODE2,
  GROUPING(i.[Dummy Column1]) DESC;

db<>fiddle

GROUPING SETS can return subtotals and totals. It's a more flexible alternative to ROLLUP.

The GROUPING() function tells you whether a column has been grouped or not in that particular row. You can use it in your ORDER BY to get the Total rows in the correct position. Then you can use it in the SELECT to differentiate those rows.

Mind your data types. Some columns may not be compatible with each other so you may need to cast.

Be careful with the ORDER BY, if you don't specify table aliases on the columns then it will sort by the final SELECT value, rather than the actual column value