Post reply

SQL for normalizing repeating groups in a text file

  • August 25, 2010 at 3:11 pm

    #1211964

    Actually I wanted to come up with an ACCESS version which uses a virtual tally table so that the whole query was self-contained. However I'm not sure whether it's possible create a tally table on-the-fly in ACCESS like you can in SQL Server. For the record, this is the corresponding version of the UNPIVOT query I came up with in SQL Server which I then adapted for ACCESS. As you can see it uses a simple virtual tally table and the query is entirely self-contained.

    SELECT ProductNbr, Date,

    CASE N WHEN 1 THEN 'Red'

    WHEN 2 THEN 'Blue'

    WHEN 3 THEN 'Green'

    WHEN 4 THEN 'Yellow'

    END AS Color,

    CASE N WHEN 1 THEN Red

    WHEN 2 THEN Blue

    WHEN 3 THEN Green

    WHEN 4 THEN Yellow

    END AS Units

    FROM ProdColortbl

    CROSS APPLY

    (SELECT 1 AS N UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS Tally

  • August 25, 2010 at 4:47 pm

    #1211989

    I have managed to come up with an ACCESS variation which is self-contained without the requirement of a physical tally table. I have created a derived tally table by constructing a UNION query selecting from MSysObjects. It seems that in ACCESS you have to SELECT from something hence the use of MSysObjects. It looks a bit strange but works fine. I'm interested to know if there's a better way of doing this!.:ermm:

    SELECT ProductNbr, [Date],

    iif(N = 1, 'Red', iif(N = 2, 'Blue', iif(N = 3, 'Green', iif(N = 4, 'Yellow', '')))) AS Color,

    iif(N = 1, Red, iif(N = 2, Blue, iif(N = 3, Green, iif(N = 4, Yellow, '')))) AS Units

    FROM Products,

    (SELECT TOP 1 1 AS N

    FROM MSysObjects

    UNION ALL SELECT TOP 1 2

    FROM MSysObjects

    UNION ALL SELECT TOP 1 3

    FROM MSysObjects

    UNION ALL SELECT TOP 1 4

    FROM MSysObjects) AS DerivedTally

    ORDER BY ProductNbr;

  • August 25, 2010 at 8:56 pm

    #1212028

    Wow it worked. You are a miracle worker. I am going to keep at this site. Good info. I copied and pasted it exactly into a sql window in Access and it worked without any errors.

    Now I am going to test it in SQL Server when I get access to it.

    Thanks so much

    Mare

  • August 25, 2010 at 11:32 pm

    #1212072

    Thanks for the feedback Mare. I appreciate it:-)

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply