Split values over multiple rows with different delimiter meaning

Split values over multiple rows with different delimiter meaning



Hi I have table Table1, this table are connected with others 4 tables:

Dim1, Dim2, Dim3, Dim4. In Table1 are different delimeter types:

No delimeter - (X_TEA) nothing to do

.. - (AGEN..XOGI) need to split row with values from AGEN to XOGI from

that dimension table

| - (24|25|LV_11..LV_25) split row in 3 rows with 24 , 25 and LV_11..LV_25

values.

<> - (<>17&<>36&<>61&<>63) split, multiple row with all values from that

dimension where they are not equal to 17, 36, 61, 63

*SQLFiddle*

Table1

| SCHEDULE NAME | LINE NO_ | ROW NO_ | ACCOUNT | DIMENSION 1 TOTALING |

DIMENSION 2 TOTALING | DIMENSION 3 TOTALING | DIMENSION 4

TOTALING |

------------------------------------------------------------------------------------------------------------------------------------------------------------

| MARKETING | 370000 | 1270 | 6 1010 | (null) |

11|12|13|24|25|LV_11..LV_25 | (null) |

(null) |

| MARKETING | 470000 | 1355 | 5 0100 | AGEN..XLIT|XOGI..WIND |

(null) | (null) |

(null) |

| MARKETING | 460000 | 1350 | 5 0103 | (null) |

(null) | (null) |

<>X_MARK_EST&<>ZZ_ZZMARK_BEL |

| MARKETING | 610000 | 1445 | 5 0102 | <>XLIT |

LV_63..LV_72 | (null) |

(null) |

| BIGSALES | 750000 | 1538 | 5 0908 | (null) |

(null) | (null) |

X_TEA |

| REVENUE | 275000 | 1250 | 5 0920 | (null) |

11|25|17|72 | (null) |

(null) |

Dim1

| CODE |

---------

| 11 |

| ACEN |

| AGEN |

| DIDEN |

| GADEN |

| LABEN |

| PADEN |

| XLIT |

| XOGI |

| WIND |

Dim2 (full in sqlfiddle)

| CODE |

---------

| 1 |

|.......|

| 28 |

| 61 |

| 63 |

| 72 |

| LV_11 |

| LV_22 |

| LV_25 |

| LV_63 |

| LV_72 |

Dim4

| CODE |

-----------------

| A_GIGI |

| G_TIGI |

| L_PIM |

| X_MARK_EST |

| X_TEA |

| ZZ_ZZMARK_BEL |

So how to split values over multiple rows with different delimiter

meaning? There probably would need use functions or some other sql server

components...

Desired Result:

| SCHEDULE NAME | LINE NO_ | ROW NO_ | ACCOUNT | DIMENSION 1 TOTALING |

DIMENSION 2 TOTALING | DIMENSION 3 TOTALING | DIMENSION 4

TOTALING |

------------------------------------------------------------------------------------------------------------------------------------------------------------

| MARKETING | 370000 | 1270 | 6 1010 | (null) |

11 | (null) |

(null) |

| MARKETING | 370000 | 1270 | 6 1010 | (null) |

12 | (null) |

(null) |

| MARKETING | 370000 | 1270 | 6 1010 | (null) |

13 | (null) |

(null) |

| MARKETING | 370000 | 1270 | 6 1010 | (null) |

24 | (null) |

(null) |

| MARKETING | 370000 | 1270 | 6 1010 | (null) |

25 | (null) |

(null) |

| MARKETING | 370000 | 1270 | 6 1010 | (null) |

LV_11 | (null) |

(null) |

| MARKETING | 370000 | 1270 | 6 1010 | (null) |

LV_22 | (null) |

(null) |

| MARKETING | 370000 | 1270 | 6 1010 | (null) |

LV_25 | (null) |

(null) |

| MARKETING | 470000 | 1355 | 5 0100 | AGEN |

(null) | (null) |

(null) |

| MARKETING | 470000 | 1355 | 5 0100 | DIDEN |

(null) | (null) |

(null) |

| MARKETING | 470000 | 1355 | 5 0100 | GADEN |

(null) | (null) |

(null) |

| MARKETING | 470000 | 1355 | 5 0100 | LABEN |

(null) | (null) |

(null) |

| MARKETING | 470000 | 1355 | 5 0100 | PADEN |

(null) | (null) |

(null) |

| MARKETING | 470000 | 1355 | 5 0100 | XLIT |

(null) | (null) |

(null) |

| MARKETING | 470000 | 1355 | 5 0100 | XOGI |

(null) | (null) |

(null) |

| MARKETING | 470000 | 1355 | 5 0100 | WIND |

(null) | (null) |

(null) |

| MARKETING | 460000 | 1350 | 5 0103 | (null) |

(null) | (null) | A_GIGI

|

| MARKETING | 460000 | 1350 | 5 0103 | (null) |

(null) | (null) | G_TIGI

|

| MARKETING | 460000 | 1350 | 5 0103 | (null) |

(null) | (null) | L_PIM

|

| MARKETING | 460000 | 1350 | 5 0103 | (null) |

(null) | (null) | X_TEA

|

| MARKETING | 610000 | 1445 | 5 0102 | 11 |

LV_63 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | ACEN |

LV_63 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | AGEN |

LV_63 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | DIDEN |

LV_63 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | GADEN |

LV_63 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | LABEN |

LV_63 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | PADEN |

LV_63 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | XOGI |

LV_63 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | WIND |

LV_63 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | 11 |

LV_72 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | ACEN |

LV_72 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | AGEN |

LV_72 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | DIDEN |

LV_72 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | GADEN |

LV_72 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | LABEN |

LV_72 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | PADEN |

LV_72 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | XOGI |

LV_72 | (null) |

(null) |

| MARKETING | 610000 | 1445 | 5 0102 | WIND |

LV_72 | (null) |

(null) |

| BIGSALES | 750000 | 1538 | 5 0908 | (null) |

(null) | (null) |

X_TEA |

| REVENUE | 275000 | 1250 | 5 0920 | (null) |

11 | (null) |

(null) |

| REVENUE | 275000 | 1250 | 5 0920 | (null) |

25 | (null) |

(null) |

| REVENUE | 275000 | 1250 | 5 0920 | (null) |

17 | (null) |

(null) |

| REVENUE | 275000 | 1250 | 5 0920 | (null) |

72 | (null) |

(null) |

PS. MS SQL SERVER 2008. For this problem I'm using split function in this

answer to split lines over | Split values over multiple rows . But after

that need to split over rest delimeters what I maybe can do... But with

this string <>X_MARK_EST&<>ZZ_ZZMARK_BEL I don't know how to split this

row. And basically I need solution without using some temporaliy tables.