Post reply

Binary Matrix table

  • August 11, 2010 at 11:55 am

    #387555

    I have a table with 5 columns

    CERATE TABLE #tmpMatrix(a bit, b bit, c bit,d bit,e bit)

    and wants to pouplate with binary data

    a b c d e

    1 0 0 0 0

    0 1 0 0 0

    0 0 1 0 0

    0 0 0 1 0

    0 0 0 0 1

    1 1 0 0 0

    ......

    any easy way to do this

    I am using sql server 2005

    Thanks for your help

  • August 11, 2010 at 12:10 pm

    #1205620

    Just an INSERT INTO for each row in 2005.

    In SQL Server 2008 you can use the VALUES row constructor.

  • August 11, 2010 at 12:13 pm

    #1205626

    Thanks

    but i am looking for an easy way to insert records in this table so wont miss any combination

    I have to insert records for all combinations

  • August 11, 2010 at 2:27 pm

    #1205714

    Something like the following?

    I'm using a subquery to get all valid numbers that will fit into your matrix (0 to 31) and use a binary test within a CASE statement.

    SELECT n,

    CASE WHEN n&1 >0 THEN 1 ELSE 0 END AS a,

    CASE WHEN n&2 >0 THEN 1 ELSE 0 END AS b,

    CASE WHEN n&4 >0 THEN 1 ELSE 0 END AS c,

    CASE WHEN n&8 >0 THEN 1 ELSE 0 END AS d,

    CASE WHEN n&16 >0 THEN 1 ELSE 0 END AS e

    FROM

    (

    SELECT number n

    FROM master..spt_values

    WHERE TYPE ='p'

    AND number <32

    ) TallyOnTheFly


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • August 11, 2010 at 2:29 pm

    #1205716

    Thanks

    this works well

Viewing 5 posts - 1 through 4 (of 4 total)

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