I had the misfortune of needing to know the value of the header in excel for any specific ordinal position. This is code that gives you those header values using sql.
DROP TABLEIF EXISTS #tmp_letters;
CREATE TABLE #tmp_letters
(
id INT IDENTITY(1,1) ,
letter CHAR(1)
)INSERT INTO #tmp_letters
(
letter
)
SELECT 'A'
UNION ALL
SELECT 'B'
UNION ALL
SELECT 'C'
UNION ALL
SELECT 'D'
UNION ALL
SELECT 'E'
UNION ALL
SELECT 'F'
UNION ALL
SELECT 'G'
UNION ALL
SELECT 'H'
UNION ALL
SELECT 'I'
UNION ALL
SELECT 'J'
UNION ALL
SELECT 'K'
UNION ALL
SELECT 'L'
UNION ALL
SELECT 'M'
UNION ALL
SELECT 'N'
UNION ALL
SELECT 'O'
UNION ALL
SELECT 'P'
UNION ALL
SELECT 'Q'
UNION ALL
SELECT 'R'
UNION ALL
SELECT 'S'
UNION ALL
SELECT 'T'
UNION ALL
SELECT 'U'
UNION ALL
SELECT 'V'
UNION ALL
SELECT 'W'
UNION ALL
SELECT 'X'
UNION ALL
SELECT 'Y'
UNION ALL
SELECT 'Z';
DROP TABLEIF EXISTS #tmp_letter_data;CREATE TABLE #tmp_letter_data
(
id INT IDENTITY(1,1) ,
batchid INT ,
letters VARCHAR(255)
)INSERT INTO #tmp_letter_data
(
batchid ,
letters
)
SELECT Batch = 1 ,
letter
FROM #tmp_lettersDECLARE @BatchID INT = 1;WHILE(
(
SELECT Count(*)
FROM #tmp_letter_data) < 1020)
BEGIN
PRINT 'Working it batches'
DECLARE @Letter VARCHAR(10)
DECLARE @NextBatchID INT = @BatchID + 1;
DECLARE mycursor CURSOR FOR
SELECT letters
FROM #tmp_letter_data
WHERE batchid = @BatchID
ORDER BY id ASC
OPEN mycursor
FETCH next
FROM mycursor
INTO @Letter
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @message VARCHAR(4000);
PRINT ' '
SELECT @message = 'Letter PRINT: ' + Cast(@Letter AS VARCHAR(255))
PRINT @message
INSERT INTO #tmp_letter_data
(
batchid ,
letters
)
SELECT Batch = 2 ,
tld.letters + ltrs.letter
FROM #tmp_letter_data tld
CROSS apply
(
SELECT letter
FROM #tmp_letters l ) AS ltrs
WHERE tld.letters = @Letter
FETCH next
FROM mycursor
INTO @Letter
END
CLOSE mycursor;
DEALLOCATE mycursor
SET @BatchID = @BatchID + 1;
END
No comments:
Post a Comment