Find valid combinations based on matrix

Question

I have a in CALC the following matrix: the first row (1) contains employee numbers, the first column (A) contains productcodes. Everywhere there is an X that productitem was sold by the corresponding employee above

     | 0302  |  0303 | 0304 | 0402 |
1625 |  X    |       |   X  |   X  |
1643 |       |    X  |   X  |      |
...

We see that product 1643 was sold by employees 0303 and 0304

What I would like to see is a list of what product was sold by which employees but formatted like this:

1625 | 0302, 0304, 0402 |
1643 | 0303, 0304 |

The reason for this is that we need this matrix ultimately imported into an SQL SERVER table. We have no access to the origins of this matrix. It contains about 50 employees and 9000+ products.

Thanx for thinking with us!

Answer 1

try something like this

;with data as
(
SELECT *
FROM   ( VALUES (1625,'X',NULL,'X','X'),
                (1643,NULL,'X','X',NULL))
         cs (col1, [0302], [0303], [0304], [0402]) 
),cte
     AS (SELECT col1,
                col
         FROM   data
                CROSS apply (VALUES ('0302',[0302]),
                                    ('0303',[0303]),
                                    ('0304',[0304]),
                                    ('0402',[0402])) cs (col, val)
         WHERE  val IS NOT NULL)
SELECT col1,
       LEFT(cs.col, Len(cs.col) - 1) AS col
FROM   cte a
       CROSS APPLY (SELECT col + ','
                    FROM   cte B
                    WHERE  a.col1 = b.col1
                    FOR XML PATH('')) cs (col)
GROUP  BY col1,
          LEFT(cs.col, Len(cs.col) - 1) 

Answer 2

I think there are two problems to solve:

1. get the product codes for the X marks;
2. concatenate them into a single, comma-separated string.

I can't offer a solution for both issues in one step, but you may handle both issues separately.

1.

To replace the X marks by the respective product codes, you could use an array function to create a second table (matrix). To do so, create a new sheet, copy the first column / first row, and enter the following formula in cell B2:

=IF($B2:$E3="X";$B$1:$E$1;"")

You'll have to adapt the formula, so it covers your complete input data (If your last data cell is Z9999, it would be =IF($B2:$Z9999="X";$B$1:$Z$1;"")). My example just covers two rows and four columns.

After modifying it, confirm with CTRL+SHIFT+ENTER to apply it as array formula.

2.

Now, you'll have to concatenate the product codes. LO Calc lacks a feature to concatenate an array, but you could use a simple user-defined function. For such a string-join function, see this answer. Just create a new macro with the StarBasic code provided there and save it. Now, you have a STRJOIN() function at hand that accepts an array and concatenates its values, leaving empty values out.

You could add that function using a helper column on the second sheet and apply it by dragging it down. Finally, to get rid of the cells with the single product IDs, copy the complete second sheet, paste special into a third sheet, pasting only the values. Now, you can remove all columns except the first one (employee IDs) and the last one (with the concatenated product ids).

Answer 3

I created a table in sql for holding the data:

CREATE TABLE [dbo].[mydata](
    [prod_code] [nvarchar](8) NULL,
    [0100] [nvarchar](10) NULL,
    [0101] [nvarchar](10) NULL,
    [and so on...]

I created the list of columns in Calc by copying and pasting them transposed. After that I used the concatenate function to create the columnlist + datatype for the create table statement

I cleaned up the worksheet and imported it into this table using SQL Server's import wizard. Cleaning meant removing unnecessary rows/columns. Since the columnnames were identical mapping was done correctly for 99%. Now I had the data in SQL Server.

I adapted the code MM93 suggested a bit:

;with data as
(
SELECT *
FROM   dbo.mydata  <-- here i simply referenced the whole table
),cte

and in the next part I uses the same 'worksheet' trick to list and format all the column names and pasted them in.

),cte
     AS (SELECT prod_code, <-- had to replace col1 with 'prod_code' 
                col
         FROM   data
                CROSS apply (VALUES ('0100',[0100]),
                            ('0101', [0101] ),
                            (and so on... ),

The result of this query was inserted into a new table and my colleagues and I are querying our harts out :)

PS: removing the 'FOR XML' clause resulted in a table with two columns :

prodcode | employee

which containes al the unique combinations of prodcode + employeenumber which is a lot faster and much more practical to query.