For once, I have hit something I cannot wrap my mind around...
I must realize an importation of a flat data file into our "point of sale" db format.
My problem is that the flat file is an export of an non relational DB structure (whose I don't have the schema) mixed with C structures.

The programmers of the initial apps found a clever way to not have to modify the tables of their DB: they used logical AND on a bigint field.
The DB used in the original application is Btrieve, which uses flat binary file with very limited update of the structure available, thus this choice.

My problem, as of today, is that I use a conversion table mapping the decimal values of the bit mask to retrieve which flag is true given a value in the row.
Example:

  Row in the flat file:
    Version;TimeStamp;flag;
    5;2001-02-12 19:28:10.000;50176
  define in the convertion table and their binary value:
    REZFLAG_GEGEBENOK       1024
    REZFLAG_INBERICHT       16384
    REZFLAG_ABGESCHLOSSEN   32768

This has the inconvenient to make 3 rows from 1. Because an logical AND between 50176 and each of the values of the 3 fields is different than 0
So, using a cross apply between my initial row and my conversion table, I end up with 3 rows for that column.

What I'm looking to do is not to multiply the numbers of rows, as for now, but to pivot those matched flags as column that would dynamically extend the resultset.
I've tried to make that during half the day, but I simply cannot make this work.

Here is a sample of the datas I have:
The conversion table:

set nocount on

if object_id('dbo.tblBitConvert') is not null begin
  drop table dbo.tblBitConvert
end

create table dbo.tblBitConvert(
  strFile varchar(200),
  strFlag varchar(100),
  strHex varchar(20),
  intVal bigint
);
  

-- Parsing abdav
----Ended parsing abdav
-- Parsing abvk
----Ended parsing abvk
-- Parsing adressen
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_ARZT','0x0001','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_PATIENT','0x0002','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_KKASSE','0x0004','4')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_KRANKENHAUS','0x0008','8')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_KUNDE','0x0010','16')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_KKISZENTRALE','0x0040','64')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_EMPFAENGER','0x4000','16384')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_UEBERGEORDNET','0x8000','32768')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_HERR','0x0001','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_FRAU','0x0002','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_FRAEULEIN','0x0004','4')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_FAMILIE','0x0008','8')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_FIRMA','0x0010','16')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_ANDIE','0x0020','32')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_DR','0x0040','64')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_DRMED','0x0080','128')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_PROF','0x0100','256')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_PROFDR','0x0200','512')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_ANDAS','0x0400','1024')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_KIND','0x0800','2048')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_PRAXIS','0x1000','4096')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_CODETABLE','0x4000','16384')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ANR_SPEZIAL','0x8000','32768')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKABR_PAT10VH','0x0001','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKABR_PAT10VHDISK','0x0002','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKABR_VOLLDISK','0x0004','4')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKABR_ZENTRAL','0x0020','32')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKABR_SEKTION','0x0040','64')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKMAHN_ZENTRAL','0x0080','128')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKMAHN_SEKTION','0x0100','256')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKMAHN_KUNDE','0x0200','512')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKSTAT_MAHNUNG','0x00000001L','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATSTAT_TAG_FEHLT','0x00000002L','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATSTAT_MONAT_FEHLT','0x00000004L','4')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','SPR_DEUTSCH','0x01','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','SPR_FRANZ','0x02','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','SPR_ITAL','0x04','4')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','SPR_RAETOROM','0x08','8')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ARZTFLAG_GELOESCHT','0x00000100L','256')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATSTAT_ZUSATZVERSICHERT','0x00000008','8')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATSTAT_MONATSRECHNUNG','0x00000010','16')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATSTAT_NUR_BARVERKAUF','0x00000020','32')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATSTAT_TEILGESPERRT','0x00000040','64')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATSTAT_REZEPTGESPERRT','0x00000080','128')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATSTAT_GESPERRT','0x000000C0','192')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATSTAT_GESPERRT_KSB','0x00000100','256')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_FLAG_PATBEFREIT','0x00000002L','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_FLAG_OFACKOPIE','0x00000004L','4')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_FLAG_MONATSRECH_KK','0x00000008L','8')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATFLAG2_KUNDENKARTE','0x00000001L','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATFLAG2_KEINE_KUNDENKARTE','0x00000002L','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','PATFLAG2_KEINE_ABILIS','0x00000004L','4')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_KKABDA','0x0001','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_KKABDADELETE','0x0002','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_KKOFAC','0x0080','128')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKMAHNRECH_ZENTRALE','0x00000002L','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKFLAG_SCHEIN_ZWINGEND','0x00000004L','4')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKFLAG_ABR_2MONATLICH','0x00000008L','8')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKFLAG_ABR_GERADE','0x00000010L','16')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKFLAG_ABR_BISENDEMONAT','0x00000020L','32')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKFLAG_NR_IS_EAN','0x00000040L','64')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKFLAG_AKZEPTIERT_NEG','0x00000080L','128')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKFLAG_GELOESCHT','0x00000100L','256')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKFLAG_PH_ZU_HL','0x00000200L','512')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKFLAG_HM_ZU_HL','0x00000400L','1024')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKRABATT_KEIN','0xFF','255')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKRABATT_MODELL1','0x00','0')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKRABATT_MODELL2','0x01','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','KKRABATT_MODELL3','0x02','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','AKKEF_ALPHA','0x0001','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','AKKEF_NUM','0x0002','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','AKKEF_ALPHANUM','0x0004','4')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','AKKEB_LINKS','0x0001','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','AKKEB_RECHTS','0x0002','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','AKKEB_VOLL','0x0010','16')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_VERSICHERUNG','0x00000001','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_KRANKENKASSE','0x00000002','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_INVALIDENVERS','0x00000004','4')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_MILITAERVERS','0x00000008','8')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_SUVA','0x00000010','16')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_FUERSORGE','0x00000020','32')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_UNFALL','0x00000040','64')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_VERSICHERUNGSART','0x0000007F','127')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_TIERSGARANT','0x00001000','4096')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_TIERSPAYANT','0x00002000','8192')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_TIERSSOLDANT','0x00004000','16384')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_FAKTURIERUNGSART','0x0000F000','61440')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_GRUND','0x00100000','1048576')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_GRUNDZUSATZ','0x00200000','2097152')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_KOMPLIMENT','0x00400000','4194304')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTV_VERTRAGSART','0x00F00000','15728640')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTE_HM_SL','0x0001','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTE_HM_HL','0x0002','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTE_PH_SL','0x0010','16')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTE_PH_HL','0x0020','32')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTE_DIV_SL','0x0100','256')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTE_DIV_HL','0x0200','512')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTP_FEST','0x0001','1')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADRKKEXTP_VARIABEL','0x0002','2')
insert into [dbo].[tblBitConvert] (strFile, strFlag, strHex, intVal) values ('adressen','ADR_FLAG_MEMOSHOW','0x00000001L','1')
----Ended parsing adressen

The conversion table function

CREATE FUNCTION [dbo].[funGetBitFlags](
    @strFileName varchar(100)
    ,@intFlagValue bigint
    ,@strFlagLike varchar(100) ='%'
)

RETURNS @outputTable TABLE(
    strFlag        varchar(255),
    intval        int
)
AS
BEGIN
    DECLARE @strFilter varchar(100)
    
    IF @strFlagLike='' OR @strFlagLike IS NULL BEGIN
        SET @strFilter='%'
    END
    ELSE BEGIN
        SET @strFilter=@strFlagLike
    END
    
    INSERT INTO @outputTable (strFlag, intVal)
    SELECT bc.strFlag, bc.intVal
    FROM dbo.tblbitconvert AS bc
    WHERE bc.strFile=@strFileName
    AND (bc.intval & @intFlagValue) <> 0
    AND strFlag LIKE @strFilter
    
    INSERT INTO @outputTable
    (strFlag, intVal)
    SELECT 'nill', null
    WHERE NOT EXISTS (
        SELECT 1
        FROM @outputTable
    )
    
    RETURN
END
GO

And a sample of imported datas:

create table dbo.importVals (
  version int,
  timestamp datetime,
  flag bigint
)
insert into importVals (version, timestamp, flag) values (5,'2001-02-12 19:28:10.000', 50176)

And the current way I use to extract the flags:

SELECT x.*, i.*
from dbo.importVals AS i
  cross apply (
    SELECT *
    FROM pms.funGetBitFlags('adressen',50179,'ADR[_]%')
  ) AS x

The result of this query is:

strFlag                     intval      version     timestamp               flag
--------------------------- ----------- ----------- ----------------------- --------------------
ADR_ARZT                    1           5           2001-02-12 19:28:10.000 50176
ADR_PATIENT                 2           5           2001-02-12 19:28:10.000 50176
ADR_EMPFAENGER              16384       5           2001-02-12 19:28:10.000 50176
ADR_UEBERGEORDNET           32768       5           2001-02-12 19:28:10.000 50176
ADR_FLAG_PATBEFREIT         2           5           2001-02-12 19:28:10.000 50176
ADR_KKABDA                  1           5           2001-02-12 19:28:10.000 50176
ADR_KKABDADELETE            2           5           2001-02-12 19:28:10.000 50176
ADR_FLAG_MEMOSHOW           1           5           2001-02-12 19:28:10.000 50176
(8 row(s) affected)

I'd be more than thankfull to anyone having an proposal upon how to do that, if it's even possible.
Thanks.

I'm a little bit lost. I think I understand what you're trying to do, in the big picture sense, if not 100 % the details. Maybe the point I get lost is trying to figure out where you're stuck?

Did a little bit of Googling on bitmasks, since I don't have all that much experience with them (but understand how they work). It seems that, like you, everybody is using a TVF to get at the data. http://sqlblog.com/blogs/adam_machan...ght-shift.aspx

I'm a bit lost too...

My problem is that I must migrate datas from this older system to our new.
When I have just one bitmask flag, it's ok, but I've reached a place where I have 45 bitmask fields (!!!) in a table structure with 400'000 rows.
Averaging 5 possible values per bitmask field, it makes around 225 boolean fields to comput for each rows.

To simplify the migration process, I wanted to be able to transform those bitmask flags in boolean column that would be added to the result set, and possibly dropping the bitmasked field of it too.

Now, I've been thinking about it in "background", when driving slowly home (we have 45 cm snow that have felt since sunday. Fun, but thrilling on the highway...)
and the more I think about it, the more I prefer the dynamic sql way, where I will be addind the fields with an "alter table", and then a query that will retrieve the flag being true for each row, and update those columns.

I think it would be more managable than doing all at once.
But this beast got me wondering if you could add dynamically columns to a dataset, and doing a pivot at the same time, without knowing for sure how many columns would be pivoted.
It surely is tricky...

When you say pivot, there's a T-SQL pivot clause, but it was introduced in the 2005 engine. And, like you say, it's dangerous, because it turns data in tables into columns in your result set. Meaning the schema of the results changes with the data. Delete a row, and a column you're querying for might not be there.

Have you worked with calculated columns? You could make a dummy table, then Alter Table Dummy Add TestColumn As 2 * 4. I'm probably a little off in my syntax (it's been a while since I've needed to do that), but something along those lines will give you the new column when you select *, but not cause the results to be stored on disc. I don't remember all of the restrictions around this, but I know the results of a cross apply aren't going to be allowed.

Still, if you import everything into a regular table, and then use your table valued function in a view to put the answers with the source data - would that work?

Still, if you import everything into a regular table, and then use your table valued function in a view to put the answers with the source data - would that work?

It's what I have now in fact.
I have bulk loaded the files into tables that follow their structure.
Now, my problem, is that I need to transform some rows into columns, but not all rows.

For example, I have a tables "customers", which contains amongst other things the customer first and last names.
I have a flag field too, that describe what kind of customer it is, and which special conditions he have.

Like:

+---------------+---------------------+------------------+----------------------+
|  FirstName    +         LastName    +      Flag        +       Adr            |
+---------------+---------------------+------------------+----------------------+
|  Thierry      |        Schork       |     32768        | Somewhere street     |
+---------------+---------------------+------------------+----------------------+

But, when translated, it gives

+---------------+---------------------+------------------+----------------------+----------------------+
|  FirstName    +         LastName    +      Flag        +       Adr            |       Flag           |
+---------------+---------------------+------------------+----------------------+----------------------+
|  Thierry      |        Schork       |     32768        | Somewhere street     |      CUST_STD        |
+---------------+---------------------+------------------+----------------------+----------------------+
|  Thierry      |        Schork       |     32768        | Somewhere street     |      ADR_HOME        |
+---------------+---------------------+------------------+----------------------+----------------------+
|  Thierry      |        Schork       |     32768        | Somewhere street     |      SALES_10%       |
+---------------+---------------------+------------------+----------------------+----------------------+

And what I'm looking to achieve is

+---------------+---------------------+------------------+----------------------+----------------------+----------------------+----------------------+
|  FirstName    +         LastName    +      Flag        +       Adr            |       CUST_STD       |       ADR_HOME       |       SALES_10%      |
+---------------+---------------------+------------------+----------------------+----------------------+----------------------+----------------------+
|  Thierry      |        Schork       |     32768        | Somewhere street     |         1            |          1           |         1            |
+---------------+---------------------+------------------+----------------------+----------------------+----------------------+----------------------+

And possibly all the other flags added too, with a FALSE value.
So, I think that what I'm trying to do is a pivot, but it looks like a partial pivot.
Not sure it's possible in a single query, thus my feeling that I'm going to alter the table adding a suit of bit fields that represent the flags, and an update query to set those values.

Like that, I would be able to avoid the cross apply in the dispatching step, when I put the datas in the final tables.

Have you worked with calculated columns?

Yes, of course, but my problem stands. By using a scalar function, I can retrieve a list of fields in a varchar form.
Using a table based function, I can retrieve them as column in the dataset, but I don't think I can create, for example, 5 columns out of 1 varchar field that I would have splited in the same query that compute that varchar field....
Or I went the wrong way, an missed something obvious.

This all makes good sense. And you're right, it's a pivot. I guess the danger of rows disappearing and changing the schema doesn't really apply when you're pivoting against enumerated values.

Mind if I ask what you're going to do with the suite of boolean column?

It sounds like you have a properly normalized customer table, with the combined bitmask as a BigInt (or regular one), but need some type of where clause of some other query, or maybe downsteam logic that takes place outside of the database. It also sounds like you can get all of the bool columns from a table function?

My first approach would be to store the data you've shown in the first example in the customer table, cross apply the function, save that select query as a view that returns the last result set you want. Is that not possible?

Looking back over what you wrote, it looks like you don't have code for the TVF I'm thinking of. Can you write something along the lines of

Create Function ColumnsFromBitmask Returns Table (
BitMask BigInt,
Gets_10_Percent_Discount Bool,
StandardCustomer Int,
-- etc
)

And so on? You wouldn't even have to do logical ANDs directly in SQL, since you've got a bit converter table.

Mind if I ask what you're going to do with the suite of boolean column?

Of course not...
I have to map them to bit columns dispersed into the db shema of our application.

My first approach would be to store the data you've shown in the first example in the customer table, cross apply the function, save that select query as a view that returns the last result set you want. Is that not possible?

I was thinking about temp tables, but basically, it's what I'm heading to, yes.
I don't know what TVF refers to, but as I understand you, it's what I'm thinking to do.

The ting is, that I would have liked to have it dynamic. But it's true that the flags will not change much (I really hope not. It's already a pain to have to scan dozens of .h files to get the flags. But if they changes, urgh...)

TVF is short for Table Valued Function. Sorry for not making that clear. (Newspapers typically have a rule that they have to spell the whole words out the first time they use an abbreviation, with the letters in parens, then just use the letters after that.)

The nice thing about using a function and "joining" (via cross apply) to it is, if the list of flags ever does change, you only change one piece of code - the function itself. On the other hand, the "danger" I mentioned before, where you say Select a_column_that_doesnt_exist_anymore From Crosstab_Query, is much easier to control for. If you're unable to parse one of the yes or no flags, you can return a null for that column, and have it still exist. I'm not sure how close that comes to what you mean by dynamic - I'm assuming you don't actually mean D SQL?

Also, with my understanding of bitmasks, 1 is a particular value, 2 is another, then 4, then 8, 16, 32, and so on. Meaning that if you see the value 9, it's 8 + 1, and whatever options 8 and 1 represent, are both true, while all other numbers are false. Meaning, your function doesn't need to do logical OR or AND comparisons, or shift bits around. You ought to be able to do that with simpler math, although a lookup table would let you have nice descriptions to go along with each value.

On the other hand, the "danger" I mentioned before, where you say Select a_column_that_doesnt_exist_anymore From Crosstab_Query, is much easier to control for

Yes, but this should not happens, thanks to the bitconvert table.
It's generated SQL from a python script who parses the application headers and create that script, thus the 'strFile' field.

Also, with my understanding of bitmasks, 1 is a particular value, 2 is another, then 4, then 8, 16, 32, and so on. Meaning that if you see the value 9, it's 8 + 1, and whatever options 8 and 1 represent, are both true, while all other numbers are false.

Yep, it's exactly that.
This is used in the C program to spare both memory and space and stuff a lot of booleans informations in 1 value.

You ought to be able to do that with simpler math, although a lookup table would let you have nice descriptions to go along with each value.

And if you look at the table based function, it's what I'm doing.

SELECT bc.strFlag, bc.intVal
    FROM dbo.tblbitconvert AS bc
    WHERE bc.strFile=@strFileName
    AND (bc.intval & @intFlagValue) <> 0
    AND strFlag LIKE @strFilter

the

AND (bc.intval & @intFlagValue) <> 0

is the bit comparison.
I apply an logic AND between the decimal value of the flag and the actual value of the field.
If the result is different of 0, it means that the flag is set to true.

lthough a lookup table would let you have nice descriptions to go along with each value.

Sheesh, looking at that C code is straining enough. If I have to filter and export their comments too, I'm going to jump through the window...
Beside, I won't add comment in that table, as it can be regenerated from a script.
The comments are put into the stored procedures who will dispatch those datas.

I'm probably still a little bit confused - just as a warning. But it seems like you're using the correct, or best, approach. And looking at how other people are doing the same thing in Google, it seems like everyone else is also taking the same approach.

So, if you have a customer table, and a function to "extract" the boolean values from the bitmask, it seems like you should be able to cross apply these, and get the result set you need?

So, if you have a customer table, and a function to "extract" the boolean values from the bitmask, it seems like you should be able to cross apply these, and get the result set you need?

Yep, I do, but there is 1 thing that bothers me: I don't know how many duplicate I could get in m rows by using the cross apply, thus my looking to pivot those extracted fields as columns.

I think I've found my way though.
I'll have a pre-processing step that will create a dynamic sql (I know, don't fire at me...) which will create column that match every possible flags for a given table.
Then, an update with cross apply will update those columns to true if the flag is present.
And finally, I'll do my dispatching with the "extended" tables, where I know that for 1 customer row, I will still have 1 row.
Not 1 row multiplied by the number of flags that are true.

The more I think about it, the more it seems the simplest and flexible way.

No offence, but why get so complex...Are computers really worth this complexity?

Even after all your data is gone?

No offence, but why get so complex...Are computers really worth this complexity?
Even after all your data is gone?

I don't understand you question...
If it's worth it !?
For me, it's not. For my manager, it's a must.
Me an my unit manager spend nearly 3 hours trying to convince him otherwise, but he has made his mind.

Ok, I think I've got it running pretty well now...
Given I have a field where the possible values are:

#define REZFLAG_NICHT_VORHANDEN            0x0001
#define REZFLAG_WIEDERHOLUNG            0x0002    // Wiederholung möglich
#define REZFLAG_NUMMEREINS                0x0004    // Rezept ist bei evtl. Fortsetzung
                                                // (z.B. bei Wiederholung) ausschlaggebend
                                                // für RezeptDatum
#define REZFLAG_ABGERECHNET                0x0008
#define REZFLAG_BACKOFFICE                0x0010
#define REZFLAG_VERAENDERT                0x0020    //wird gebraucht für Winconceptexport svb release 1.105
#define REZFLAG_RA_ABGERECHNET            0x0040
#define REZFLAG_RA_VERWEIS                0x0080
#define REZFLAG_RA_VERWEISGELOESCHT        0x0100
#define REZFLAG_FAKTURAGELOESCHT        0x0200
// für Kasse
#define REZFLAG_GEGEBENOK                0x0400
#define REZFLAG_BONDRUCK                0x0800                                         
//#define REZFLAG_NOCTU                    0x1000    //erm 2.006, Germany old
#define REZFLAG_GEGEBEN                    0x2000
#define REZFLAG_INBERICHT                0x4000
#define REZFLAG_ABGESCHLOSSEN            0x8000

I do:

--extending the tblrezept table with the flags
DECLARE @strSql varchar(max)
SET @strSql=''

SELECT @strSql=@strSql+'ALTER TABLE pms.tblrezept ADD bln'+lower(strFlag)+' bit NOT NULL DEFAULT 0
'
FROM pms.tblBitConvert AS tbc
WHERE strfile = 'rezept'

EXEC (@strSql)

--updating the table to set the boolean from the bitmasks
--Updating for the field rezept.flag
UPDATE r
SET 
    blnREZFLAG_NICHT_VORHANDEN=CASE WHEN x.REZFLAG_NICHT_VORHANDEN IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_WIEDERHOLUNG=CASE WHEN x.REZFLAG_WIEDERHOLUNG IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_NUMMEREINS=CASE WHEN x.REZFLAG_NUMMEREINS IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_ABGERECHNET=CASE WHEN x.REZFLAG_ABGERECHNET IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_BACKOFFICE=CASE WHEN x.REZFLAG_BACKOFFICE IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_VERAENDERT=CASE WHEN x.REZFLAG_VERAENDERT IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_RA_ABGERECHNET=CASE WHEN x.REZFLAG_RA_ABGERECHNET IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_RA_VERWEIS=CASE WHEN x.REZFLAG_RA_VERWEIS IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_RA_VERWEISGELOESCHT=CASE WHEN x.REZFLAG_RA_VERWEISGELOESCHT IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_FAKTURAGELOESCHT=CASE WHEN x.REZFLAG_FAKTURAGELOESCHT IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_GEGEBENOK=CASE WHEN x.REZFLAG_GEGEBENOK IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_BONDRUCK=CASE WHEN x.REZFLAG_BONDRUCK IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_GEGEBEN=CASE WHEN x.REZFLAG_GEGEBEN IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_INBERICHT=CASE WHEN x.REZFLAG_INBERICHT IS NOT NULL THEN 1 ELSE 0 END ,
    blnREZFLAG_ABGESCHLOSSEN=CASE WHEN x.REZFLAG_ABGESCHLOSSEN IS NOT NULL THEN 1 ELSE 0 END 
FROM pms.tblrezept r
INNER JOIN (
    SELECT VerwNr_3,
            REZFLAG_NICHT_VORHANDEN,
            REZFLAG_WIEDERHOLUNG,
            REZFLAG_NUMMEREINS,
            REZFLAG_ABGERECHNET,
            REZFLAG_BACKOFFICE,
            REZFLAG_VERAENDERT,
            REZFLAG_RA_ABGERECHNET,
            REZFLAG_RA_VERWEIS,
            REZFLAG_RA_VERWEISGELOESCHT,
            REZFLAG_FAKTURAGELOESCHT,
            REZFLAG_GEGEBENOK,
            REZFLAG_BONDRUCK,
            REZFLAG_GEGEBEN,
            REZFLAG_INBERICHT,
            REZFLAG_ABGESCHLOSSEN
    FROM (
        SELECT x.strFlag, r.VerwNr_3, r.Flags_18
        FROM pms.tblrezept r
            LEFT OUTER JOIN pms.tblrezept_pos AS tp ON r.VerwNr_3=tp.FK_1
            CROSS APPLY (
                SELECT * FROM pms.funGetBitFlags('rezept', r.Flags_18,'REZFLAG[_]%') AS fgbf
            )AS x
        WHERE x.strFlag != 'nill'
    ) AS src
    PIVOT (
        max(strflag) FOR strflag IN (
            id,
            REZFLAG_NICHT_VORHANDEN,
            REZFLAG_WIEDERHOLUNG,
            REZFLAG_NUMMEREINS,
            REZFLAG_ABGERECHNET,
            REZFLAG_BACKOFFICE,
            REZFLAG_VERAENDERT,
            REZFLAG_RA_ABGERECHNET,
            REZFLAG_RA_VERWEIS,
            REZFLAG_RA_VERWEISGELOESCHT,
            REZFLAG_FAKTURAGELOESCHT,
            REZFLAG_GEGEBENOK,
            REZFLAG_BONDRUCK,
            REZFLAG_GEGEBEN,
            REZFLAG_INBERICHT,
            REZFLAG_ABGESCHLOSSEN
        )
    ) AS pvt
) AS x ON x.VerwNr_3=r.VerwNr_3

Slow as hell, but looks like doing what I want.
I'm just confused about the aggreagated part here:

PIVOT
(
<aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]

I've used an max() aggregate, and it looks like working, but I'm not sure of it...
At least, in the dozen of rows I've checked, the results where correct given the flag value.

I think max(boolean) is perfectly valid. I'd expect it to be terribly slow! But if it's doing what you want, that's the #1 thing. You can improve on it if necessary, once you have something in place.

I don't understand why you'd expect duplication from the cross join, though? If you have a table function that returns the bitmask itself, along with a column for each boolean that makes it up, the bitmask is essentially a primary key, right? (Even if it's not declared as such.) Then, if you join back to the customer table on the bitmask, you'll still have a 1:1 correlation, and no Cartesian product. Maybe I'm not understanding properly.

I'd expect it to be terribly slow!

Took around 1:15 minutes to process 2 million rows. Honestly, it's bearable.

I don't understand why you'd expect duplication from the cross join

Because, as it's a table value function, for a given flag value, it gives me back 3, or 5 results for each rows in the initial table.
I pivot the flag later, thus the duplication.
My problem with this duplication is that sometime, I have to combine several flags from the old system into one in the new one. Thus my need to pivot those flags from rows to columns

Two things

(1) That server must be a beast! A minute and a quarter, for millions of rows!! Probably to help make that happen, the schema must be very good.

(2) I've finally understood the difficulty and where the duplication comes in!

Are you still working on this, or are you satisfied with the results you've got at this point?

(1) That server must be a beast! A minute and a quarter, for millions of rows!! Probably to help make that happen, the schema must be very good.

It's far from being a beast, no.
It's a dual core laptop (I don't know it's cpu speed), with 2 Go of ram and an external USB disk for the DB files.
But I did a pretty basic error... It's not 2 millions rows, but 211'000 rows.
:-/
I just have missed 1 zero, almost nothing, right ?

Are you still working on this, or are you satisfied with the results you've got at this point?

I'm quite happy of the results, and I'm now in the process of applying this on every tables to be imported.