Cross table with multiselect

Question

I have a table with 2 Columns, filled with strings

CREATE TABLE [tbl_text]
 (
  [directoryName] nvarchar(200),
  [text1] nvarchar(200),
  [text2] nvarchar(200)
  )

The Strings are build like the following

| Text1      |   Text2  |
|------------|----------|
|tz1 tz3 tz2 | al1 al2  |
|    tz1 tz3 | al1 al3  |
|        tz2 | al3      |
|    tz3 tz2 | al1 al2  |

Now i want to Count how many times the TestN or TextN are resulting in the

| Text1 |  al1 |  al2 |  al3 |
|-------|------|------|------|
| tz1   |   2  |   1  |  1   |
| tz2   |   2  |   2  |  1   |
| tz3   |   3  |   2  |  1   |

i tried solving it with an sql-query like this:

TRANSFORM Count(tt.directoryName) AS Value
SELECT tt.Text1
FROM tbl_text as tt
GROUP BY tt.Text1
PIVOT tt.Text2;

This works fine if i got fields only with one value like the third column (the complete datasource has to be like a one-value-style) But in my case i'm using the strings for a multiselect...

If i try to conform this query onto a datasource filled with the " " between the values the result is complete messed up

Any suggestions how the query should look like to get this result ?

Answer 1

You'll have to split the strings inside Text1/Text2 before you can do anything with them. In VBA, you'd loop a recordset, use the Split() function and insert the results into a temp table.

In Sql Server there are more powerful options available.
Coming from here: Split function equivalent in T-SQL? ,
you should read this page: http://www.sommarskog.se/arrays-in-sql-2005.html#tablelists