I am trying to create dummy columns having sequential numbers in SQL query or in ADO using Excel VBA.
SEQ1 = some Sequential number e.g. 0,1,2,3,4,5,6....100
SEQ2 = some Sequential number e.g. 1,2,3,4,5,6,7....100
Query:
TRANSFORM FIRST(F2.Textval) AS Textval
SELECT SEQ1 AS DUMMY1, SEQ2 AS DUMMY2, COUNT(F2.Position) AS Pos, F1.Name AS Name
FROM FILE1 AS F1 INNER JOIN FILE2 AS F2
ON F1.ID = F2.ID
GROUP BY F1.Name
ORDER BY F1.Name, F2.Position
PIVOT F2.Position IN (1,2,3,4,5,6,7,8,9,10...100)
Output:
Q.1] Is there a way to create these dummy columns purely in an SQL Query without using primary key? I don't want to write loops in Excel VBA to create a sequence.
Output should look like this:
DUMMY1 DUMMY2 Pos Name 1 2 3 4 5....
==============================================================================
0 1 78 AB OBIW SDMS SDED GSAS SDSQ...
1 2 12 DS ZASY SEEW QTSA RYUD SOPH...
2 3 33 EF QWET DJMS WDUD JSAS SDJL...
3 4 60 AC ALJJ BDMS AUUD AWAS EDST...
4 5 02 JK ADGH VDWS CARD ARAF SNSS...
...
Q.2] Can below sequence be written purely in an SQL Query instead of coding it in Excel VBA?
PIVOT F2.Position IN (1,2,3,4,5,6,7,8,9,10...100)
Currently, I am creating this sequence in VBA:
NumSeq = JOIN([TRANSPOSE(ROW(1:100))], ",")
...PIVOT F2.Position IN NumSeq
