SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE member ( member_id INTEGER NOT NULL, name VARCHAR(24) NOT NULL ); INSERT INTO member VALUES (1, 'Bobby'); INSERT INTO member VALUES (2, 'Gripette'); INSERT INTO member VALUES (3, 'Roger'); CREATE TABLE citizen ( member_id INTEGER NOT NULL, country_id INTEGER NOT NULL ); INSERT INTO citizen VALUES (1, 124); INSERT INTO citizen VALUES (1, 222); INSERT INTO citizen VALUES (1, 840); INSERT INTO citizen VALUES (1, 3); INSERT INTO citizen VALUES (2, 840); -- La magie commence ici SELECT m.member_id, m.name, countries = LEFT(o.list, LEN(o.list)-1) FROM member m CROSS APPLY ( SELECT CONVERT(VARCHAR(12), CASE country_id WHEN 4 THEN 'AF' WHEN 8 THEN 'AL' WHEN 12 THEN 'DZ' WHEN 16 THEN 'AS' WHEN 20 THEN 'AD' WHEN 24 THEN 'AO' WHEN 660 THEN 'AI' WHEN 10 THEN 'AQ' WHEN 28 THEN 'AG' WHEN 32 THEN 'AR' WHEN 51 THEN 'AM' WHEN 533 THEN 'AW' WHEN 36 THEN 'AU' WHEN 40 THEN 'AT' WHEN 31 THEN 'AZ' WHEN 44 THEN 'BS' WHEN 48 THEN 'BH' WHEN 50 THEN 'BD' WHEN 52 THEN 'BB' WHEN 112 THEN 'BY' WHEN 56 THEN 'BE' WHEN 84 THEN 'BZ' WHEN 204 THEN 'BJ' WHEN 60 THEN 'BM' WHEN 64 THEN 'BT' WHEN 68 THEN 'BO' WHEN 535 THEN 'BQ' WHEN 70 THEN 'BA' WHEN 72 THEN 'BW' WHEN 74 THEN 'BV' WHEN 76 THEN 'BR' WHEN 86 THEN 'IO' WHEN 96 THEN 'BN' WHEN 100 THEN 'BG' WHEN 854 THEN 'BF' WHEN 108 THEN 'BI' WHEN 132 THEN 'CV' WHEN 116 THEN 'KH' WHEN 120 THEN 'CM' WHEN 124 THEN 'CA' WHEN 136 THEN 'KY' WHEN 140 THEN 'CF' WHEN 148 THEN 'TD' WHEN 152 THEN 'CL' WHEN 156 THEN 'CN' WHEN 162 THEN 'CX' WHEN 166 THEN 'CC' WHEN 170 THEN 'CO' WHEN 174 THEN 'KM' WHEN 180 THEN 'CD' WHEN 178 THEN 'CG' WHEN 184 THEN 'CK' WHEN 188 THEN 'CR' WHEN 191 THEN 'HR' WHEN 192 THEN 'CU' WHEN 531 THEN 'CW' WHEN 196 THEN 'CY' WHEN 203 THEN 'CZ' WHEN 384 THEN 'CI' WHEN 208 THEN 'DK' WHEN 262 THEN 'DJ' WHEN 212 THEN 'DM' WHEN 214 THEN 'DO' WHEN 218 THEN 'EC' WHEN 818 THEN 'EG' WHEN 222 THEN 'SV' WHEN 226 THEN 'GQ' WHEN 232 THEN 'ER' WHEN 233 THEN 'EE' WHEN 748 THEN 'SZ' WHEN 231 THEN 'ET' WHEN 238 THEN 'FK' WHEN 234 THEN 'FO' WHEN 242 THEN 'FJ' WHEN 246 THEN 'FI' WHEN 250 THEN 'FR' WHEN 254 THEN 'GF' WHEN 258 THEN 'PF' WHEN 260 THEN 'TF' WHEN 266 THEN 'GA' WHEN 270 THEN 'GM' WHEN 268 THEN 'GE' WHEN 276 THEN 'DE' WHEN 288 THEN 'GH' WHEN 292 THEN 'GI' WHEN 300 THEN 'GR' WHEN 304 THEN 'GL' WHEN 308 THEN 'GD' WHEN 312 THEN 'GP' WHEN 316 THEN 'GU' WHEN 320 THEN 'GT' WHEN 831 THEN 'GG' WHEN 324 THEN 'GN' WHEN 624 THEN 'GW' WHEN 328 THEN 'GY' WHEN 332 THEN 'HT' WHEN 334 THEN 'HM' WHEN 336 THEN 'VA' WHEN 340 THEN 'HN' WHEN 344 THEN 'HK' WHEN 348 THEN 'HU' WHEN 352 THEN 'IS' WHEN 356 THEN 'IN' WHEN 360 THEN 'ID' WHEN 364 THEN 'IR' WHEN 368 THEN 'IQ' WHEN 372 THEN 'IE' WHEN 833 THEN 'IM' WHEN 376 THEN 'IL' WHEN 380 THEN 'IT' WHEN 388 THEN 'JM' WHEN 392 THEN 'JP' WHEN 832 THEN 'JE' WHEN 400 THEN 'JO' WHEN 398 THEN 'KZ' WHEN 404 THEN 'KE' WHEN 296 THEN 'KI' WHEN 408 THEN 'KP' WHEN 410 THEN 'KR' WHEN 414 THEN 'KW' WHEN 417 THEN 'KG' WHEN 418 THEN 'LA' WHEN 428 THEN 'LV' WHEN 422 THEN 'LB' WHEN 426 THEN 'LS' WHEN 430 THEN 'LR' WHEN 434 THEN 'LY' WHEN 438 THEN 'LI' WHEN 440 THEN 'LT' WHEN 442 THEN 'LU' WHEN 446 THEN 'MO' WHEN 450 THEN 'MG' WHEN 454 THEN 'MW' WHEN 458 THEN 'MY' WHEN 462 THEN 'MV' WHEN 466 THEN 'ML' WHEN 470 THEN 'MT' WHEN 584 THEN 'MH' WHEN 474 THEN 'MQ' WHEN 478 THEN 'MR' WHEN 480 THEN 'MU' WHEN 175 THEN 'YT' WHEN 484 THEN 'MX' WHEN 583 THEN 'FM' WHEN 498 THEN 'MD' WHEN 492 THEN 'MC' WHEN 496 THEN 'MN' WHEN 499 THEN 'ME' WHEN 500 THEN 'MS' WHEN 504 THEN 'MA' WHEN 508 THEN 'MZ' WHEN 104 THEN 'MM' WHEN 516 THEN 'NA' WHEN 520 THEN 'NR' WHEN 524 THEN 'NP' WHEN 528 THEN 'NL' WHEN 540 THEN 'NC' WHEN 554 THEN 'NZ' WHEN 558 THEN 'NI' WHEN 562 THEN 'NE' WHEN 566 THEN 'NG' WHEN 570 THEN 'NU' WHEN 574 THEN 'NF' WHEN 580 THEN 'MP' WHEN 578 THEN 'NO' WHEN 512 THEN 'OM' WHEN 586 THEN 'PK' WHEN 585 THEN 'PW' WHEN 275 THEN 'PS' WHEN 591 THEN 'PA' WHEN 598 THEN 'PG' WHEN 600 THEN 'PY' WHEN 604 THEN 'PE' WHEN 608 THEN 'PH' WHEN 612 THEN 'PN' WHEN 616 THEN 'PL' WHEN 620 THEN 'PT' WHEN 630 THEN 'PR' WHEN 634 THEN 'QA' WHEN 807 THEN 'MK' WHEN 642 THEN 'RO' WHEN 643 THEN 'RU' WHEN 646 THEN 'RW' WHEN 638 THEN 'RE' WHEN 652 THEN 'BL' WHEN 654 THEN 'SH' WHEN 659 THEN 'KN' WHEN 662 THEN 'LC' WHEN 663 THEN 'MF' WHEN 666 THEN 'PM' WHEN 670 THEN 'VC' WHEN 882 THEN 'WS' WHEN 674 THEN 'SM' WHEN 678 THEN 'ST' WHEN 682 THEN 'SA' WHEN 686 THEN 'SN' WHEN 688 THEN 'RS' WHEN 690 THEN 'SC' WHEN 694 THEN 'SL' WHEN 702 THEN 'SG' WHEN 534 THEN 'SX' WHEN 703 THEN 'SK' WHEN 705 THEN 'SI' WHEN 90 THEN 'SB' WHEN 706 THEN 'SO' WHEN 710 THEN 'ZA' WHEN 239 THEN 'GS' WHEN 728 THEN 'SS' WHEN 724 THEN 'ES' WHEN 144 THEN 'LK' WHEN 729 THEN 'SD' WHEN 740 THEN 'SR' WHEN 744 THEN 'SJ' WHEN 752 THEN 'SE' WHEN 756 THEN 'CH' WHEN 760 THEN 'SY' WHEN 158 THEN 'TW' WHEN 762 THEN 'TJ' WHEN 834 THEN 'TZ' WHEN 764 THEN 'TH' WHEN 626 THEN 'TL' WHEN 768 THEN 'TG' WHEN 772 THEN 'TK' WHEN 776 THEN 'TO' WHEN 780 THEN 'TT' WHEN 788 THEN 'TN' WHEN 792 THEN 'TR' WHEN 795 THEN 'TM' WHEN 796 THEN 'TC' WHEN 798 THEN 'TV' WHEN 800 THEN 'UG' WHEN 804 THEN 'UA' WHEN 784 THEN 'AE' WHEN 826 THEN 'GB' WHEN 581 THEN 'UM' WHEN 840 THEN 'US' WHEN 858 THEN 'UY' WHEN 860 THEN 'UZ' WHEN 548 THEN 'VU' WHEN 862 THEN 'VE' WHEN 704 THEN 'VN' WHEN 92 THEN 'VG' WHEN 850 THEN 'VI' WHEN 876 THEN 'WF' WHEN 732 THEN 'EH' WHEN 887 THEN 'YE' WHEN 894 THEN 'ZM' WHEN 716 THEN 'ZW' WHEN 248 THEN 'AX' END ) + ',' AS [text()] FROM citizen c WHERE c.member_id = m.member_id FOR XML PATH('') ) o (list)