How to insert multiple records from fields in select record

  • tshad

    SSCertifiable

    Points: 5839

    I am trying to take results from multiple fields from single rows that are not null and put them in multiple rows in another table where each valid field has a record (row) of it's own.


    DECLARE @TestTable TABLE
    (
     Name1 VARCHAR(20),
     Name2 VARCHAR(20),
     Name3 VARCHAR(20)
    )

    DECLARE @TestTable2 TABLE
    (
     ID int IDENTITY(1,1),
     SingleName varchar(20)
    )

    INSERT @TestTable VALUES('Tom', 'Larry', NULL)
    INSERT @TestTable VALUES('Ron', NULL, NULL)
    INSERT @TestTable VALUES('Sheryl', 'Mary', 'Mark')

    SELECT * FROM @TestTable

    INSERT @TestTable2
    (
        SingleName
    )
    SELECT ???  FROM @TestTable

    Here is what is in @TestTable
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJoAAABPCAYAAAATDNyLAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAkvSURBVHhe7Z3PiyPHFce/yj8wezNsMMkaaQ+iSVhyCS1ycGCJJfmg9aEvOXTIQToZCcMEFgRxQGDwgJEuAQnWoMP60EmISFbShj2YECRyMYtDI8hK2IFlF3zb+QuUetXV2tZYo261+ldp6wM9qipJM/2mXr2qVn/rKbdmQKGImdyf/vJ35WiK2Mm9ePFiffPmTVGVm5cvX+IUbDkVOwjXlh+IukIRK8rRFImgHE2RCNc72qqHUq6E3krUCWor9eBtihz2Nxq5BqaiGgkp2DJtlJDL5fhR2vrDR5CqHSU0jugUn4g2R8uM2bE2rNArMYMKLQxES7QkaMu0gQoMLNdrrNdLGFbhqE7aJmE7bGHHsg1UwgeA/Y6md9HVLFxEGl6uI4/mjDpmgrpoiZQkbSn3se43mUVEHlVDh/0sItdI2o6ZsCNfQFG3EdYM3zVa9dyA3dkxgpi3l8TUsAmrPIw30BDhttSbosfL9Lz4Dew1DYpc9D722qhmlSCkY8sKY2sO7bbjdlGQih2rMay5hrBm+F8M5Jto7xpBzNtnfGqgsOoxfG6jeD5jbV2g1QF4mZ4fs+fZ9GhaKA7F+4ZFWIlNzYwUbFn1TLTQxXlZNERBwnaseswx2ZIG3XOENcPf0RjlPpufr44gPgqcUUInMRfN0A1UXa/3lgk+KtgaoyBGD71vbmF8tXdiJElbqINMi61x3OknQpK0I99kjnnkWjOQozGzcG5YMC8Woj5Fo8BGQXsoRk8XunjGnzom7qjjxwzNqHthL8nYQk5WYE42jMHJHJLuk+PWmgEdjby6DW0weD1KoAEF52xW44WnfQ/5Kgx9gJG7CODrhyujMgFit4WVzZaGSWxO5hC3HXzKZCHMeea4tWZgR+MjqOuOESoDlgi3pmWLdj/YleVwQm90wrS5gDGMtzN2E68t0wuatgaoULs4IvssbYt47cg3h5iggwK3wcTCWKIfcpGmbqpnEHVTXaEIiXI0RSIoR1MkgnI0RSLkXr16tb5x4wbYI07lUZEdzs7O+CN3tPG3OV6Rneqt9cYwmbm8vGR2XIqavFxenm36g0+dv74jf+cQFNEU2YQ72sOn8o8eQk2b2UVFNEUi+Ea0rztn3BG/d3SeiFdkB9+IltK91chZfY5S7oMrkm7WVvpc2PYlGrn7V9Swnrat13q4rj0CfCPaT9uX3BEfPv0zfonf4He8zI72XfGK7PBmRbSv0DLjcYo4UGs0WdF/j672KBlJdwQct0Z7/kc8MMVUajbxtWj7mJUfdO7y9o+/eILHvHwXD2bf8LfFReiIFkACTRLnTdmj/ps2cqHFgMdSPX8fdkeOqHZERPsGj9v3gfpT/v7PfrXAp+667T8L/NB8god/+wS4+BTg5Xt4PniM75xXxELoiLZPAl0bOiJCb3kwEuufKUaDOmpRyrQPIf9btCWJauEj2vPH+Dc+wfuld3j1rV/cQ2G5dBzpJ/dw523evF2OmdARjUWvayXQZaGWc8uk168PMKLOnY4wqNeQlp8R5f6HgARRTa3RQkigy7U6BszTpiMb3Uh3nYThXZwbj2BeLEWduIWi/t/trXGrb2HrBRRENWnCR7S338PPcR+PxLrru3/9FctCAW/xWjqEv+o8UAJdrqFuV/jm2q2NHimRb34IbfCF57x/jNvaV1gs/yfqzC4228y1WymomR2OiGjv4L0OW4MN7nBH/egf9/BZyh95BIpo85aQJoujgRAS6DJqzDd1o5pax23Dolr3Z6LsUO7/E8XRR+yC5gN+mKMClv13xbOM+R/Y/+FHzGZxNL7c334kG/XGKUyf5PA0/cUPm25LI9Rm/VjWZyd7U/3NXqMdyLTBRnoFthF+M+2bSPg1WgZJ5M4A5aNgUXOW7GZU6VERTZEIKqIpEoFfDFCBOokiguyPimyxJeU+BfkzcVpXayd41alQxI1yNEUiKEdTJIK/o5Euy3vLhh9CsyUbEkqgdyKhHcEimt4VGaadYznRXmu2pEMuCfT1SCjlPpR8uQZtvoBXmCINkkmgr0VGKfehrKYj2HrR0TZx0aCYUksiD72QQTvZn7M31cokgd6HdFJuX65IawodoD2krICU0bnFKktnSjVsVFyPmttYkPSZptusTbUSSaD3IpuU2xfvGm05QRc2RjRvUkZnntrcucGcrxrQ7WeOQ+nGph2FovOYIWSRQPshlZT7IPJlNNsalzLLjRwSaH8kknIfDEmZaScQZXRGi4Vux6LV2MJcu50R1ak/MkiggyCNlPtw3O8FoozOXRa6nYzOlFd/GTZtcypkXwIdDEmk3OqmerZQN9UVipAoR1MkgnI0RSIoR1MkwkbKrVDEwQlLueW35RTtUFOnIhGUoykSQTmaIhGCOZpXzl0qoUf307nmLELpT9S/bxeuHVviuBV6pKeTKVt3FHYk8f/2EMDRnER1xlLIhIYGrIoQOMqIrr+WMhH8y+tFWSYks8Pf0VbPYFNaTfe2f76J2TqedE3JYMDQLIzdHlougHr9gC+6zwpy2eHvaK4UiM2Xq+/F2QXGu+TaLCx75d18tw4P1XsyW49FJQGqNQ2W6CFKD2rUPMLMIBm6r557SuH9KDu8uXpp1mJLooaQe8VBgKnT+XL34qKDAs+KyNZo7vnslGuTvNtCcehOtUVYplgL7MtsXeWVZCDFrzVm58T+NqUH9aoBg2TozkpW7mPtoPqKnKzD+muGvquIjoFgFwOkqu3P+EnTVruW6zi75Np8rTBHS6Tq5CNnzkI8CUCzktmaR+kFlrTJ5qpQk0djEbl2ZejOUlbuY+wgWL+YZgUDrY24070FczQPtNWu7rvVro6JO5r4MUPzioY43czWeVQNG52ODW0rHAXL0J2drNzH2UFJotvDJbp2Jfbp39fRVj02KhoiglGdRrG71W4XNMp0NuLd+ZWvC16/f0PKma1pIw3m2o5pL0CG7gxl5T7KDurHvKOStmP+JMHX0fLNISawNtvtaKvdhG+1uw5nTQfLkXfnzAWMna9PObP1zqvncsAM3RnKyn2UHQL2O4ZdG5U4P1ejm+rpMFnX9Tr7GR3J2RL9uXtJr0+ixWvHwWu0SJA5s7XKyh0KJRPKIEompFCERDmaIhGUlFsRG97pP0dXBKKsUMQE8H9vvjeed6yHTwAAAABJRU5ErkJggg==

    What I want to end up with in @TestTable2:
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAFwAAACQCAYAAACFxQyXAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAffSURBVHhe7Z27buM4FIaP900GsFJk8gRyv4CdxpXbYBu5tIBFOpfusljYpTXVtK4CBLGxDxA9gSdFZGw5jzFakqKsK3W1jtbh+QDN2Loyv3iR+euQg2/fvvm/fv0CAofBz58//ZeXF7AsS64iLs2PHz/g69ev4vNv4l8CDRIcGRIcGRIcmV4FP8wHMJgf5Dc9yBf8MIfBgIkhl7gmQqTRBk7yOybi2iw9o03y6qfN6GpuXFbw0wZGEwesvQ++z5c9wCoSeLxl694WMJTfsTFNE1z7Ca61XCiqFBNuDfkRxrCNCZysBk6wGfEScID5uUSMIJEBU6VFLMrcGJwv2neeFXa2hLXpwKQoR/NME7tetCs/P09fPL38GvHrptJfJU01yAo+vIeZ6YJtpC+sxpms4NYLSsTeYsc+hUlif9jkCGu5zVubwIoO+Nux3J7kMDfAvmPbRcni+x9hkqm+DFh8X4PprBTpY9d8APh+PocJTqyEAvC/7RmmYrsX3LyBAe/LcH8A+yHav1qaqpOTw4eweJPCGXl3PIu1f4OFLALjKfvFevwIEnT6gCPcwY3cNry5i7alYbly5ZiwfoxuxnCxBMvdwWv6gOECljx9MWEikiVyeD8D030HT37nWPst24szhPtZkAnCPCDSGO5fJ00VUVQpsq4O73iN3J5geMPkPsKHPPbw7ADc3RTU/+FNDpcJsCNyGW/37A+34SEnYaIRDc9h2OysbaiepiooBQ8ZLr6zYufCezyLVEXk8CjBE8eCvaI6CWDbZdGNlqj0JGE5eW+JBvRVrhGwNsOw76LzsBzD8nAL6qSpnIzgPHckHrsOT2C78Ua0Bt47uLzOPic0LMo5iLbDYQ9ENYrS+FHUwbatzsOHpxY5vEmaSsgIznP0bGdERUg0eg3v6HgLe5hE5+KLssHhbYeXvHbh/hx2DG9A5TcBv6bFG8Lg+NXtmuXRpjRJUwm8e3a73bIMeHnYE4IP5tr35HcOqwV8kz226MTxeJSffL+0Dm+D984Kc6KRPMHHUX7UlE4FH2/Zc+4xXqUYsJt58Na0xfkEdCp4UAfGW3dfa7E5Z09zxH7yEt1DniYC5Gn2CAmODAmODAmODAmOTErwuBOSXto5HURASvAxbMMfKaJb0zy7NYU9fURlGlQpRR5fE89QL2oKzoXL8fgSonPDobpnqBv1BD+9ws5NujbC44vZaJzKnqGG1BOcOzjyY4QBt00tOA2pJ7hxm+MPevDe1ILTkHqCS48v/hLOabMCx5zBvd69rpWp2Wjy/u09WE5kKhi7GXg9vvp2bVD3LALUPdsjJDgyJDgy5GkiQ40mAtRo9ggJjgwJjgwJjgwJjkyO4IE7UxQkRjSHcjgy9QRXxj9yD3MEm00Yk8ktt+y6eSbUOyhN6cjiz0wNwSvEP9ogA5BCiy25bstDCp3nyP8Ulp0JM40602sIXif+MSKxbjwFCxx4loqfXnfgWsvGEWHXSK0qpX384xgeeckQip/gdeeCNU3fos9NdcEvFP8oSgavVuQbAJrpXbPRjNE4/jH0RXkJsaaZKuizoxTcmciqQyzsacO4VPyjfFeFoVt1IugyTlPJ3vKZ3D6rnrQALU5ThRjkQMPqhNOD4AcI9NZR7l4ED16JLhxU4hNDniYy5GkiQJ5mj5DgyJDgyJDgyJDgyOQIHo84CxeK0bwUyhxurr2gG5YtotOKXOWLUKlKSYy2KUiXgnSsZsl4tBpTQXAm4Co+oiYXtCxWs2g8Wr1RCu7a4Vh9wcBg5wF6K8dqKsaj1ZzyOlwON3rOnxSr2YryKkUON3oeDpRiNVtRoQ4fwmIZy+UUq9mKCoIzEu+TUKxmG6h7FgHqnu0REhwZEhwZ8jSRoUYTAWo0e4QER4YER4YER6am4KG5IL9eGB3m18wXvGA+TaIdWcFL5tMk2qGoUtTzaQYU+ZVqvzNbZQT76h2nKfq7+fixauO3yK8smn9SWG2ax2kqQ074FF5sM1tMP5rJy/PXJvisuong4SPh9F/e2jcT+3P2vnVexz9Hx4upw2InE9dMnPxzUCnkpPl8mrx0xKuU+PyTFKepFDyk/nyaxfNPUpxmilbzaVaZf5LiNJO0m0+zyvyTFKdJcZodQ3GaPdKD4BSniQzFaZKniQh5mgiQp9kjJDgyJDgyJDgyJDgyOYJHjk2ulxn6nYn+EaIqyhxumumRNwP4z3J6hGyOUvC75RIsdwevccVPG1g5Fkyn8ntIjTFpdR9/tqAOH8PUcmEXUzwYujTd6VRvTFrdx58tbDS56RuFDB7giQm3fkx3gtQck1bz8WcLBU+Ic3hWRqrVG5NWb1+zWPCYOLyxNGf32Ui1BmPS6uxrlggeijOBCWsslxXKfaUxaTX2NUsFD8Rh/6uEGTcZk1ZjX7MXT5Ojka/Zu6fJ0dXX7ElwfX3NngTX19ccfPnzd/7SJrz88bdYQXTLWfB///pHrCAuD3maPUKCI0OCI0OCI9OD4N3Gev7fyRGcPM0uUeZw8jS7QSl4V55mptCIElMnaOu6KajDu/E0E8dysWuFtFw/hY1mJ55myAcPMddLbE6h4N14mgEOy/aNZiu8cooFZ/myC0+TwwdPEEG3D3o97ZQI3pGnKRFBt2CDodFDeang3XiaIdE4trq8eUXdswhQ92yPkODIkODIkKeJDMVpIkCNZo+Q4KgA/Ac3lwN1538W9QAAAABJRU5ErkJggg==

    Thanks

  • pietlinden

    SSC Guru

    Points: 62898

    step 1: find all the names that are not null 
    step 2: union them together.
    insert the result from step 2 into your final table.
    return results from final table.

    Nothing to it, right?
    Yeah, I didn't post an answer exactly, but that would deny you the chance to try it yourself.

  • tshad

    SSCertifiable

    Points: 5839

    That's fine.
    But I don't need a list of names (my mistake).  I need to use another value in the record to go along with the names in each record.  So changing the code to the following:


    DECLARE @TestTable TABLE
    (
     ID INT IDENTITY(1,1),
     NameRecord INT,
     Name1 VARCHAR(20),
     Name2 VARCHAR(20),
     Name3 VARCHAR(20)
    )

    DECLARE @TestTable2 TABLE
    (
     ID int IDENTITY(1,1),
     NameRecord INT,
     SingleName varchar(20)
    )

    INSERT @TestTable VALUES(5, 'Tom', 'Larry', NULL)
    INSERT @TestTable VALUES(7, 'Ron', NULL, NULL)
    INSERT @TestTable VALUES(9, 'Sheryl', 'Mary', 'Mark')

    SELECT * FROM @TestTable

    INSERT @TestTable2
    (
        NameRecord,
        SingleName
    )
    SELECT ???

    The @TestTable looks like:

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAQMAAABQCAYAAAAdvBAmAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAx7SURBVHhe7Z0/iOvYFcY/J02qfQvbLNskS+RXGHVJZVfbxZ4UZgt1wZDCrhabwEAChhBwN42nCdjFggmkUBIwZG2lypLC7rYThowUNs0+0gTe2ypNMjnn6srWeDy2x9Zfz/mBGPlasnSupHPPufb5pvSHP/35HoIgvHhK33zzzf1HH32kXybPmzdvkObxLolL6TuxI3+wLd/R64IgvHDEGQiCoBBnIAiC4mln4N+iVruFH66XSiitlxo6jnonPtQxariNfmz0HOLmkU2hXfr9pInTtrT7LoSO0Sl1EFuXZWCH06mtr3/twYHPIFM7Tr+Hj48MqkN49/e458XrozIox9d5a5botRK+gaNEbaLFm5twBykeP1bS7DsftzW68co9jHVLfKRoh9NBA5a+BzxYdjnGwSBlO1xtBz2baJzmoE9LE4w6upMhYM/iNZYezqFp4yat0XkLo96EuVzB068LRap9Z6C74AdojrZuiY007aiPcD/qkjWMgSurCvcupjs6bTsW2g6jjErVxSlmnD5nYLxO5MG5urZ2j87k/TZhvQ6FVOjVQUeHSLVbB7dqPZLG0DYdHsV4P9p2XzDjO1O41QrK6sUT+/nOpn3PcdQ98OD8qC2yTa1l09gRL1n2XZxkY4ePmb2E+TpwDXGQiR3+DPbSxClmnDGBeLoH2ovRRX+XRyXvtwhDei/SyUsXlesFtVGk0hsAap3f56iFwll66CoTvd+kAjsaui17KK8vSgll2r0/YQ/71H7c3gAsT58Hh2Qtuijc3qOdg3bPcilS0wbw+TUn1H6Nu8g2k74ZvB8nafZdkmRgh3/bQg9DXNd1QxykbId/S86DUjcMr3GKGWc4Aw+rEz3QIeojesi2ParyioHXZIPXo2rVwlV4DtF1RnlJyt3K+oHn/ZY2ZuEHR+cMvDndCi6mHOo8tZ9D7XzDdPVBKF0a3S/QhW6vB+3GlYWqexecP58Tt/NnRrcpV1BVa/GSWt8lTJp28EPUsinnDkPtGEnTDqNLzuOMuY/TnYF/twmpY6eOa8tG62alX1NoXiav2OcRlh/c4TMepDbm4QOvFnp4d11xngeh0Xo8DXtxx37JGBszGfRdIqRjBzuCMjmCSQKOICDt63H63MdpzoDyZg6LzX5SHcherg9zPI7k1RRWl4Oj+bNVpH0PxhWs6hjTMLkij7z36516E+3xFM5T+3E7BZM363a6sJTT3UK369zOn9lYmq8f9k24b3QbtRY/mfRdAiRuB623eibmiTmCgKTtUOkBhQLBO6fPfRzvDKL5NSXXK8p9R3HmV48gjzoMfSavA3Y5OH7LdnX7IQx0J3PeMTjv1gqWmhN4inAe5Kn9ttp5kmE+Ie/M7XSCg6CdRxrvUecE27iNYBv6yETShIAs+i4JkrXDueEQfYwGt+sl/q/LmWTtMLoTzDHQz2cLK+u0Z1MKlQqEFPjkCylUEgThIhFnIAiCQpyBIAgKcQaCIChKb9++vX///fdBf5HWX0EQ8sWrV68CZ/Cf/6YXIHz4wXvqBxPC83n37p26aEXnsux4p18Vl3fvXqnrobwAP6BpIZGBIOQT5Qz+9e9v1Ys04FRBEIT8IZGBIAiK4yODr3+L6w+6+Kt+eSqHIwP+vf/m56Fcz53IL0QvEKcT7bfIckoJW5ZkUAeRCP7nqJU+fXj/clvtc23bl3Sv/4ru+CiRtgfbRniq/UyOiAz+gfFP3sOHP/4lfqdbzuG4yCBanZVmpVyxqY/CPmMFokgfJltEIuzlK/Ra8T+4SXBEZPBDtP/yLW3zR/xMt5zDwcgg0dJoQUiZ6q8xNL94LHCSQ/I5Z7C00QrlwSRHiAcKvffLsj0htZUnjpALY9vW65H0iFOorLKlq+ufwh3kPzo4fs4gJo76NqFqoc/yTt4cph2nYu1L5YAs25NSWzljn1wYS8uxUEh0nbUp1I4OpuM2mlllS8bP0S9AdJC/yMDoYrHoQqmDGXVcx6lY+1LZllzblmUL52S2pbbyhopu9Ki/LRembVuvs/5gewwlXOVMMW43keXMSX30GZDz6CCfkYEgPOL5cmH1ZlvJ2DlTF8NYlU5P4RMa2L5A6yaqJ/4xKtW/PxQV9r+GWy1nMmeWu8jggYST7+CGolsr18NVAdglubYty1YInikXxjJ2bkP9g5E83EJG9zOY499HzvsHeG1+hZX3T/2a7PI8ujYfZ3JtchcZPJBwYlmxIcuK6TeFEzlGli1nbMnYlzo4QS6sjib5j6p1lRPHR9HB8Ed6PaA++hsq01+gVvtULa1pma7NJ/pdYvkb6ofvk8166Xy5v/0M1lWLaTmE7333fxdRpJIFUqj0XCi1qE3RXIwSmS+4yEIlmTMQLg6nQyNmA6512j8UeYnk79sEQYiD+khNNC4kxzwaiQwEQVBIZCAIgkJNIPIKj9j8oKb1VxCE/MATiMoZpDlDfSkz4llwWd8mXMos/IV9myAIgiDOQBAEhTgDQRAURzkDJ6wPpyVpfQFVmxD+BHW96Pp7YT9c2/+o73Tdf5EomFzYkxTMjsPOwOmgAQueqiP3YCWsL2B0F0FVWrhwdVrG5aeFojrU1ypYvLm5qfsvFMWRC9tPwWTP9sK/5BqF/5ffwFXK+gLOjQ0r8/LT4mLUmzCXK0QLZwtBgeTC9lI02bPj8TGzlzBfp/QTTwp7BzkpPy0qvjPdaEoqcRCdPmxJnwVyZ/lKK4oiF3aIQsmeHYt/20JPKebohoThunuzH0YlwlFslf5yFXh/wn24X/psxVJhnFrkKa0oiFzYQYoke3YMPLHX4jr4RVoPp4Mb28xOt66oROcMvDm5bhdTzhEOSJ+F7ShXgr85oQhyYcdQGNmzQ7AjYEGMSWqOgI85kPLTczHq6PZNJf1VXPIvF3YcBZE92wvllK2eiXmKjoAOCsoQRO4sDlj6i1WCCyx9lne5sGMphOzZPpwbVqEdoxHJQxP/XwY+eU+rL3JnsVCm0cel0aeA0mdr8i0XdjwFkD2TQqViIIVK+UIKlQRBuEjEGQiCoBBnIAiCQpyBIAiKteyZIAgvF55AlG8TCsRlfZsgduSF0A5JEwRBUIgzEARBIc5AEATFUc5gI3uWTq172se7KKLSZ7Uabrn/lGZBjGXJcX/eNqENDy6+j1vWYkjyuHEThx1J93WEw86AZc9cLXvm9YGGFsVIirSPd1E46JRtWJ4uYZ5YsIvaf9XqpsSa4RLsTYVPcSiQHYedAcuehRWLRlj0ot5JBP/O3fw/faOO66GLQlfgpol/B7caUYYyuljcJ/PvyJPHgmXamIX3mrcC2m1U9cviUBw7njdnoLyaiSRVz4zXJpb2THtSH7PVMlXNxUITlilTbuA/6rIVZrvSLwpDo1JoqiBVhaYddMLtI2Gu0ymhM9MvEuaqacLWT5EzdWE1I8IrFEFulKC1PYfOO6NB5Sw7ylw1HEKRH6V+HV2GHjdHOwMlYU4nhmHCgiMUiczJkyrprtoN3cLFGwuyg8uU56isBiiXg5trXW2+U9qMpdBsVCZhWlGB3dL5KW1f4e1ZnZr1ENSHOJiO22heqRfJw6pLamCg47IWZlTxg+6TBZ+zSietjVTbvvPOKkQ61w5+7bMjGNC1WmAUqlLFzNHOIJAwT14qnamPtFz6YoRmxC8KR8DqRrr/WCa9Fz7cu6TNVKS3RE85Dlp4FFpSSMtiPJxu8PaUavTb4yBVc6YYpylbryKdFTwWdd0WYlERjY4AoqNnHs57m3PsYOiatFoNjM1kNT6elyaQGelKpfu4c5GeGvOFwTLp7YMy6W3Mw5FJLQt0tzS36s22kk7jEHeYqmw9328uBgMX5oNhPZgorfR1pBOOnltkd97bnGcHPQHoTzwM3UaiA/FBZ6DSAzqD4PFPQSqdcqhSJxjNfOcGvWWG4V3BCK6VjgT4NY+IoUz6LnjEqtLoGeYSKlfd7L+GnQrdiPwtT9pKdCzciuUuYVyTIpzgZPzZanf8mOF5b3OWHXwNjUCpyk3w26GDzsDoTjAH5aBqgqOFleVRzqLfTAKeM0AwZ1BuuGjPizobnj7BtdLzLdx/A2CuZNKfIphjAKV+KkxtrWDt3L6OJt2z62950mTnNyL8LROfdmBny6bwcScZnvc2Z9mhoc+YDF00EvrdgRQqFYjs+o4nr6ZoLuJxzOnZEe95byOFSsLLgtO2UqN4svVFPe8MkcigQFzaSFR0JDIQBOEiEWcgCIJCZM8E4YUTpjqle/61gyAILx5JEwRBUIgzEASBAP4PualOv0fw53UAAAAASUVORK5CYII=

    The results should look something like:

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMEAAACPCAYAAABDEL+AAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAuySURBVHhe7Z2xcuI8FIUv/7NAiixPYJ4A0qSiTWfK0KSjpEtDSpjZIm2qzOwsPEF4AjZFoN7HWH5Jlm3JGAMmccy955vRJNiY2EJH0nV0fBt///7dkmI+n1Ov19O/gm8G30U1xPXc+Pnz5/bfv3+kCwASSUaCX79+URiGZiP4Xv78+UM/fvywr8BXEdfzf/Y1AGKBCIB4IAIgHogAiAciqILFgBqdJ9rYl3VlMWhQY7Cwr+RQSgSmshoN6jz5X+vmqfNNlbihp050TmkZEJuvU4vIuTa3is138U0Cq187KEfpkSAIAloOH2vV0ILJmrbbrSnzcEY9Dr3a5ok6vRmF8+i6tts50Tht9N2p2vZ2T037umrq2A5Opfx0qD+iSXCgoekvMLcH0z13h542Cxok+3XP7fboer99uyHb2xf39N3bkGj14fSQB44vPNd9x+nzV+f5FPfUdp/3WWr/h3nzGQR03bK/UpemTqP3pzDRuQ4Wbr1m6jEzqpiy9zs8os5r3g6O4YyYoEX3zxMKZuPMScaoC7sjerY983oS0MzpwYiWNGy90q3Zv44qstGi91H8fqLhXfr+xaBFw/bc9oZ6/4p6e6cBqqLGM6L2ldNYio5X59oaEiUjSdzb6grPOc6reHUd6tC52T9VTVQdc+d+1ojeh+pcytK8oX6g6yrbGPYz643peh2d7zxUxz7GZ6uus7dSpxZfS0BqiKHttGv3+xxX53VuB0ei/2Osy3Q6VZ95HPOQtmrqkfyu5iFb/Upd4FaP27msJ9uAwm20d71VdbF137pz7Dzcqm8oer85NtjaP2mZb8NkW/R56nKSEp+f4dDx7t9y8c45xv27+nf/OnKP0Z9v6+gYVquV/S3F1LO5Nv86zPbkBHbr1fvb2XPLnJf3WQfrPHp/vdrBacT1fPbdoe50TuFySHc53YAJkOJhS/W0S7u9HLrHcIfBHmX71yQmUN/I7jz1wPHBterTMqzfc865RdeqZ35f25dZco85HzP3j3vGE0YFj+YVtWlFH/bYxas/Wu5yuM5j6tQOTuVsEZg5qm10v+0Wg5p7toZtO01QRX17avA9gzD9rKS80X3eN9h9MMPq2PtCDhy/fKeddt26zjnnNb0v3Tl6htxjPo/m/bO6tgIRFrH5UBJIG1Fvpupkz1Qo4oQ6r2M7OJJPEIHCNrrhcL/GF49n9ABmXpxt1EU06X7kjAaHju/eqqp196t5rJ5n2uPcoG/zNKZZ0KebfZVuelvns+zdnbLoXtS7Bbl4pGGRCIvQo5SOAZLGo2OYPZxc54ratYPj+BwR6EangyP7ytCdRrcp7bA1vp6ohlYW9flva+q/tJxhUJWigMg27NdIBQeOV71YNM+w+1Rg+azvwOjj1DA/6yXHtF76tC68JZn5rNY7jc7o/XTP7523CWxL9nz6O6H0WkzZW4cl6lwfU7d2cAxlAmPwteQFxp+BCTozAbob3Erj0wJjcDms39VExAuEN/Sxsr8KBiIQRHe6psnKnQ616KW/prdzokoGQASi0HNq967KVrwANJ7HuNPp2M0AyAEe4xoCj3E1wGMMgAUiAOKBCIB4IAIgHogAiIeJCLJuI1WKnE4Xw8JxXGXL+Y4qEMFqJAgTH64qhUuEL4UuTePrMYvwgsQVVrgCFJwEpkMXT5HnVu871cMrD1YimPWkfbG6MR/hgT7BwysRJiLw18REy/kFzJk3v+ll6bvDmvcjCh0LpSacx1OnJt30A72B4kOaV+18V50gWE6HooYQG2oYU8YDDXbgGRMYL21JC+IlUcYDDXZgIYLFwI8BjI+1yAfMhTIeaLADCxF0b9vJExR06a0mB3zAXNCx0KkeaJAFS6lrCJZSVwOWUgNggQiAeCACIB54jIF4EBjXEATG1YDAGAALRADEAxEA8UAEQDwQARAPExFw9Rhromtjczk1hNVIwM9jDKoA06FLZm9+YO0p7mTyK+9uG3g5kDXRqJPNUM8dViKQ5TFWjfpQfmAvv/LutqlOeD57TW2oxq4ZUF+YGYGJCCR6jLteZvvmTZ+CjFc49RaneNu8vG5aAy+0DEdnZYK8RFhOh6R4jM/PD9ylBz2CmIra0O+XJYW38mIpnjGBBI/xJ+UHNiOInhLZJ1cI1AAPEYj1GDuUzg8c+5T1SBLe7kyfJMBCBBI8xmnQr4sSfeuz8gPbZxEpJE6FNFhKXUMqX0qtplaNnr5rJOv5plhKDRIWrzM9DIicCmkgAvEsKNKAVAlABED1//rx75JXmcBjDMSDwLiGwGNcDQiMAbBABEA8EAEQD0QAxAMRAPHwEIH+t3+yrsYtHDwFOf5pFtdVH3iIoDtNDDVxmeu7vYyWAgSTtXNtfnYacB48p0ObJxrPApo88Pw3aFfbIlcfjpUyO1q4I0X8tAo3l7EE++nxsBSBWVvP1iaoGvV4RtS+skvFdSM/lMtYL8Ue07XNhj8PlzR8xEgSw1AEPBeELYct24u36KWvpkbxYp+jcxm/JZ3C7kgiG3YiiLI3TojbTCiJCVSwsxw+pr08chmfDTMRLOhxuKRwxDh7Y/eBJsGMxvGkHrmMz4aVCMwooCYCvJfGN+l+5IwGyGV8NoxEEI0CweSBv0PKe14QchmfC5ZS1xAspa4GLKUGwAIRAPFABEA88BgD8SAwriEIjKsBgTEAFogAiAciAOKBCIB4IIKLJjbM2JcVsNhJ9nf58BGB5zNm5pzKeKjhrPxceIjAPF9/RRPrnNrOddIOJmZ0naa1N3NyNM+JvCyV4FxYiGDzsSJylw6bVZa+s+qycb0BftbKiCL/cJH/GLmNNSxE0LxqE2XSl+qcvSycVcYvsFQj2/4pXpF/eDHI8R939uc7lpjbmMd0qOvn72o0xrQqk8qxlkQ5mk3jNnnZdsWw1z+c89QN4z9evtBv5zOk5zZmExh3p/GcWZcRtUulcqwv8fVFicpPCfxj8cSlp5p4EfJyG7MRgcfila3Nsnn/TJOTTPRhmus4KenIkYe03MYMRaCCvd6Mjc1SZ633gtLFIw2PNdFb/3Fiyj8WYbmNeYhA30Z0hvvVZE1vTCaxuufvv8TPHFLF3Aou7slTdDyx9o/XxQuM85CV2xhLqWtILZZSC8htjKXUoBBJuY0hApCDrNzGEAHIQVZuY3iMgXgQGNcQeIyrAYExABaIAIgHIgDigQiAeCACIB4+ImDrMU6dYbne4vi6D64HAvvgIQLOHmNLEAQ0y/EW6+UNuLV9HixEcCke43Sk8ssxtEe7jrDIORbS7a19HeOtqnVHEHiK82AhgkvyGPvmFvN/yiOF0KXbcEkvjgoi62N2kZtq6HdEz/bz19ol5o0g8BRn4TEdYu0xTtH+4TR9q87RRjlZ+/2nURiXWKaDkO4pzsImMObuMTa4DVZbSPdkqNRutHgq1NDuMLs9H3me4ixsROBRE49x0hBtKeK496UNVgfEQf8m8/whxWJArWE79RWvJzl5jn2keYqzMBRBvTzG6egUlSx5+9ttFePsIWqwPeqpgHh0xJxloeZMBwdFYZ7iLDxEwNhjvINpsOrnvsaaiY/G1xM1Jh5Clqc4C5ZSfyG6Eeb1/kXE06FTjzsbAZ7iLFhKDTwkeYqzQARfTDRFS0uWQ/urQZanOAtE8IVUPqUpjSxPcRZ4jCtE3/XJCiPu/VerlfkJqgeBcYXoBr9PBO52eIyrAYHxNxHP/eMCvh+IoEIuJ0aQBURQMVoIeQV8HxABEA9EIJ7qcyHXDT4igMfYMc6AU+AhAniM7StQBhYi4J/H+Os8xjsdhRlZOI2kh2EhAtZ5jBO+xmPsHZuMqMemg+IBj+kQPMYOJ3qMYz7U6CFQABo2gTE8ximneYwjZmp44Fhlx8BGBB5s8xh/jcdYE85tovA7eXeZGIqgXh7jz+ZLPMYWkyichtQS9k8DHiKAxzillMc4Ruc9nlOoRCbpCXRYSl1DsJS6GrCUGgALRADEAxEA8cBjDMSDwLiGIDCuhqief9D/9cC0luhFHkYAAAAASUVORK5CYII=

    I could do this with a cursor but would prefer not to.

    Thanks,

    Tom

  • ScottPletcher

    SSC Guru

    Points: 98565


    INSERT @TestTable2
    (
        NameRecord,
        SingleName
    )
    SELECT ca1.*
    FROM @TestTable
    CROSS APPLY (
        VALUES(ID, NameRecord, Name1), (ID, NameRecord, Name2), (ID, NameRecord, Name3)
    ) AS ca1(ID, NameRecord, SingleName)
    WHERE SingleName IS NOT NULL

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • drew.allen

    SSC Guru

    Points: 76739

    ScottPletcher - Wednesday, January 30, 2019 8:37 AM


    INSERT @TestTable2
    (
        NameRecord,
        SingleName
    )
    SELECT ca1.*
    FROM @TestTable
    CROSS APPLY (
        VALUES(ID, NameRecord, Name1), (ID, NameRecord, Name2), (ID, NameRecord, Name3)
    ) AS ca1(ID, NameRecord, SingleName)
    WHERE SingleName IS NOT NULL

    You don't need to include fields that are consistently repeated, that is, you don't need to include ID and NameRecord, because you can just use the values from the main table.

    INSERT @TestTable2
    (
        NameRecord,
        SingleName
    )
    SELECT tt.NameRecord, ca1.SingleName
    FROM @TestTable AS tt
    CROSS APPLY( VALUES(Name1), (Name2), (Name3) ) ca1(SingleName)

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • tshad

    SSCertifiable

    Points: 5839

    That helped a lot.

    Here is the solution that is a little closer to what I was trying to achieve

    I have 2 tables (old values and new values).  I only want to put in the record where the values have changed from the old to the new.  The issue was that there were multiple types of values (owner, depenants and policies) that had to be compared and I wanted a record for each difference.  So there could be from 0 to 3 records written out for each set of records.

    Here is the code:

    DECLARE @OldTable TABLE
    (
     ID INT IDENTITY(1,1),
     NameRecord INT,
     OwnerName VARCHAR(20),
     Dependants INT,
     Policies INT
    )

    DECLARE @NewTable TABLE
    (
     ID INT IDENTITY(1,1),
     NameRecord INT,
     OwnerName VARCHAR(20),
     Dependants INT,
     Policies INT
    )
    DECLARE @DiffTable TABLE
    (
     ID int IDENTITY(1,1),
     NameRecord INT,
     Differences varchar(200)
    )

    INSERT @OldTable VALUES(5, 'Tom', 3, NULL)
    INSERT @OldTable VALUES(7, 'Ron', NULL, 4)
    INSERT @OldTable VALUES(9, 'Sheryl', 2, 3)

    INSERT @NewTable VALUES(5, 'Tom', 3, 1)
    INSERT @NewTable VALUES(7, 'Ron', NULL, 4)
    INSERT @NewTable VALUES(9, 'Sheryl Ann', 3, 4)


    SELECT *
    FROM @OldTable o
    JOIN @NewTable n
    ON n.NameRecord = o.NameRecord

    SELECT o.NameRecord,
           ca1.value1,
           ca1.value2,
           ca1.valueType,
        CASE WHEN ca1.valueType = 1 THEN 'Owner has changed from ' + ca1.value1 + ' to ' + ca1.value2
       WHEN ca1.valueType = 2 THEN 'Dependants have changed from ' + ca1.value1 + ' to ' + ca1.value2
       WHEN ca1.valueType = 3 THEN 'Policies have changed from ' + ca1.value1 + ' to ' + ca1.value2
       END ValuesChanged
    FROM @OldTable o
    JOIN @NewTable n
        ON n.NameRecord = o.NameRecord
    CROSS APPLY
    (
        VALUES
            (ISNULL(o.OwnerName,''), ISNULL(n.OwnerName, ''), 1),
            (ISNULL(CONVERT(VARCHAR(10), o.Dependants),''), ISNULL(CONVERT(VARCHAR(10), n.Dependants),''), 2),
            (ISNULL(CONVERT(VARCHAR(10), o.Policies),''), ISNULL(CONVERT(VARCHAR(10), n.Policies),''), 3)
    ) ca1 (value1, value2, valueType)
    WHERE ca1.value1 <> ca1.value2

    INSERT @DiffTable
    (
        NameRecord,
        Differences
    )
    SELECT o.NameRecord,
        CASE WHEN ca1.valueType = 1 THEN 'Owner has changed from ' + ca1.value1 + ' to ' + ca1.value2
       WHEN ca1.valueType = 2 THEN 'Dependants have changed from ' + ca1.value1 + ' to ' + ca1.value2
       WHEN ca1.valueType = 3 THEN 'Policies have changed from ' + ca1.value1 + ' to ' + ca1.value2
       END ValuesChanged
    FROM @OldTable o
    JOIN @NewTable n
        ON n.NameRecord = o.NameRecord
    CROSS APPLY
    (
        VALUES
            (ISNULL(o.OwnerName,''), ISNULL(n.OwnerName, ''), 1),
            (ISNULL(CONVERT(VARCHAR(10), o.Dependants),''), ISNULL(CONVERT(VARCHAR(10), n.Dependants),''), 2),
            (ISNULL(CONVERT(VARCHAR(10), o.Policies),''), ISNULL(CONVERT(VARCHAR(10), n.Policies),''), 3)
    ) ca1 (value1, value2, valueType)
    WHERE ca1.value1 <> ca1.value2

    SELECT * FROM @DiffTable

    Here are the three results. The last one is the resulting table with only the differences. 
    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAnEAAAE8CAYAAACvo+cXAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsMAAA7DAcdvqGQAAFMvSURBVHhe7d1/rDvrXSf290kCCb9ybwiw6W215BbPWeG4lSjsttj/lJa7i33oykqzk/5RsKDa4y3byBb0rIC12JK6VcURlS20W2xEJGfVSBml6KiNbcrPRayddiFoKw2OOB4UuupGrPiRewOBm5+nz+eZZ+yxz4w9/nk8Pu/X1blfe+aZmWeemXnmM88z47l4UEBEREREqfIG8y8RERERpUhkS1wwiI10RERERE/n4uJi4d+whSDu7/3P/xK//68/a74RERER0bG9/G99FX7mfe/Sgdsb3vCGhX/DFoK4V35khGrh0+YbERERER1bZ/Q2fPgffive+MY34k1vetPsbzmQmwVx8s/f/G/H6P3Iy3jppZf0yGP41Kc+ddTlnROWXXIsq2TOrZyOuT7cx7bHskuOZZVM2stJGtV+9ofega/4iq/AW97yFv335je/WX+XIC4I5GYPNoQa5IiIiIjoCf3RH/0RPv3pT+PP/uzP8Prrr+OLX/wivvzlL5uxPj6dSkRERHRiJIB77bXX8NnPfhaf//zn8aUvfUk3uIUb3XQQx1Y4IiIiotMhwdtf/uVf4nOf+xy+8IUv6Fa45XgteXeq10ah0IYXfDZ9sv5fAdWhHrM/ehkFtMOzDedh3x6tU7BeZvyh7XndvGF1vj6F6mI5HtKxt9uuHm33A+zLSaWunMy4KKe8LiKcv0PvA3r+rMu2xbosoUfbnXVZpMhyMuOiPOG6SPepBG/SAicB3MogbmP5FqZqZjLDh2kD2aaFwt6PrjHqlSMWXnid1N90kIPbPNEdcRVV6VkloDE169LLwrGWKqODOvJ229VR9uUDOlYls1BOdrJjY13eTqWyP/g+wLpsK6zLNsO6LJmU1GVB0Bb8RdnPPXGZImq9FuD091v4qqBbOQe3x7qCXJIplpEbTzA139PBQ7vpojXtoJgxgzI1jFQlXj9WQT7xdtvJofblc5O5THZsyL43qiHYFR9ZN/4pHGIfYF22BdZlO2FdlkwK6rK4AE7s78GGpAWxoaubmCg53MQeNIfqSLiKarWghxfaQ7T151CzskpTLZjp1jTNe8M7uPksLP0lZjpvOB++Yjm6DljInxoWSlOoOOqabw+8PpxxDpfLe5KVRb57p5dZkGXrgSrvy59NHv1yC9ZJJ4gug+V18lM+6XbbWXhfXrXOy9tXrEi/rkwX9oEV5bQ4H3Wiq9QxHtdhmatArx0af6CCWnVszMpC6Dyb/SDqWFkYH72Nj7E+jxygPmNdtiHWZbtjXbZW2uuy/QVxqgiyeRf3+8nXnIpuG1FXQsUORkEzY7g5dOwiezNSw9QVSL0J6M8yXq5GZCdxkO2Z6aRpPtxULjuPLmD/z1KTN3oSWcdNJ8NLgD01+WgApYraYP7OiIY/fGq7KAV7uuSv3FPDb3AfStNr5Pzx+xDskGFyMOt/r2AH20ntvF31350+qO7nO7LK40TnUa3TrBtmRdnN1kldMct3cczttnfBvrxqnTEbPt++q8soukxj9oEV5bQ4nz6u1NV2XroH9FXgELf1HAbBdPXbxYpoF6HjQ44Ne3ZsxOzrCyRdxLEyi5Liyu6A67PSAeoz1mWbY122I9Zlkc6oLttjEDfFJOqqaQ+KHVVIwYYP6EjXRLWWit7NYORtXAV5CH8W+spujLplKjeZbuygH8xYdh4pYF3IA7Tg4k42TNx0QzVcpbqpmYVkiug8jFCDGW76ADJXNvLuvZ9/yZMMl3mG08jVpf60B1EtCFKx6Q8ZXObGmKgEwzsXrZZaS6n5phO1U16psYrKY5AvuerV4spAFhSs05Kjbbe9M/uybMfYdc7O8jjbvmvKKLJM4/aBFeX0aD4LpNLuoqSmrfYv0ZOuKDNmZ+HjY9SB3u2X1yG8r4cF6ZaPleAMHbuND7g+Kx2mPmNdtiHWZTtiXRbpjOqy/QVx4aufvSvixnZQuVUHpzZE1VKRbsNE8eqKJ3mlce1HwrM/VfhmWyyQ+wnU1URXX9qJiOkOs7K7CV+dhqmKbXxd1jtNsXwN976NO1dVMDV1Vat21Pa9i9zaM9amZfAE220fFvblTdd5H/vJLuWUQW2kpuk1kJ00YVlLXQInLWobP9H6HKw+Y12WGOuy3bEueyJR2/gw67OfIM4b6mbIXEOaJA8jU2sg1+3Oo3hpULf8pXl9dVDrT2voSqGLu3kH9bwPO0qxjGu59yJuOhmOOm5nw9UOe1FAG2a4uQfC6zsY5y4XyyaYNpxGf9qV2lFUhV2XHWSWLZXfkrpSvTFxv1qvXL0OV1+tFlHOOag7OZRXXRZsWnbGk2y3XYT35VXLHU9mV8+z7btpPlfuA1uUkxbsg+rE3emhte8uwWVR67C8r4sg3fKxEjSzxJbdkddHHLg+Y12WFOuynbAu20zUOqSgLts+iAvfc2E1MWlM0Vl14OxMXQm1ghhePgOO5S+/4viN6+upSqE3kAn9fFcmpi88TnA/Qdx0S8Olc33Q8yNueSqo6Q+3HBvTR4Xjp3FLfho1yw2uUNYodjBV2Wqa8rGaKr/T8NWerJc6tMzVarGsDrKcf2Ubb9OyCzzFdttQ7L68Yrlqv5hU/Gnm23fTfMbtAxuWU+ZSVyqWvhlbps2ZaS04tuyPJt1B+Ouwel8XS2UTHCuzK/u4sjvS+hy1PnuKY4J12dymZRd4iu22IdZlO/DXIW11mX4BvjT3ye+RfM8/+L/4AvwUYdklt9eykiurCtA7tZ/G2INz26eOuT48HrfHskuOdVkyad+nXvmREX7g2/9ffN3XfR3e/va345u+6Zvw9V//9fiar/ka/RL8N7zBb4Pb44MNRERERHQsDOKINnXAH3UkIjoa1mWpxyCOiIiIKIUYxBERERGl0KMHGz7yEzm8+OKLePXVV4/2LxERERH53vN+N9GDDZFB3OtfOl4D3Tve/lb9Y3i0uddeew0vvPCC+UarsKyS8cvpNfMt/V577YWjbXfuY9tj2SXHskom7eW009OpElgdC1viiIiIiDYXGcT94Z98xnw6POlSJSIiIqLNsCWOiIiIKIW2b4n75D/Bzdtr+DXzdVvrW+LkfWP+Kzv8vwKCV5LRasNquNxCf9X0vEb4mIbVgimjAlhEMbwPoHDx7sVjUIYVPmDeq/jr6nj9MXXUhoWGLaQNiRt+LF5b5XE/L6SOx7psW6zLNsO6bANHOfYPZ4uWuN9H93veind8x4/in5ohu0jWEneNwcODfgDi4SH83jxapdgJymygSjBUhod7KWR6DasouTamUj7TBlBK70F9eB9HvfKEAddeeWgX1MnOqqNrhhwW67JtsC7bAOuyhI597B/GFi1x34LrX/yMSvMRfJ8Zsou1LXHePdx8FrN3yxIdQrGDh+CXyzPBy8L1GFqW/0do5T6K27M4M2RQGwXBwYGxLqNjYF2W0BGP/QNKxz1xYwcViZgvLlBg/8N+SBOyKdOLgrlSk5chq89V0xRfaA/R1p+lSf4ZlbvXhzPO4ZKtJLGubr4XbvNcWuOOiHXZ/rEui8e67Oxtf0/cniR6OjVvo9GTpuEBco7FPv6deWhX6kBjqrskpraLUlCoYxfZm5Eq6xZQbwL6s61O2P1nccL22qqit1TZtG7AjpoVMj+Ixtm0xh0R67I9Y10Wh3XZ83D6LXHmBb1FuZLIFHFj5+GybXg3cnWGFm50oapivbKRd+/9ik2dZK6Cq7bw52ciU1MV/cMUNk+waxU77wPYGpcc67L9Y10Wi3XZ85COljiio8rgiifYBL5LBSIfReV2ar6Ll5HN/97iPTjeJ+HmLd4LRnR0rMvO3cm3xOkmYXUZoXdBb4jbOmA/t0uqfctcwUYdt+beEK/vYJy79G+EfaYW9jP1/74zRo43kqyVqb0Pue6HMDbfgXfiMvdxTKZ/YL6r0pxO1f718rPevwTrsgNgXfYI67Ln5eRb4jK1HgZowpKbVq0m0OrxsfydZVDrtYCmpW/6tRwb02f+qP7CfnZRwcSegr9ekMR34ab17eazr9j5DWTvfhiFwrv1X+XOUvvXd5mxyvgnVTl/sypn81f99dXDzwTrskNgXbaMddnzEvkCfAmsjhXIveWNX+bLfLfEFyEnx7JKxi8nvgB/G9zHtseyS45llUzay2mnF+DznjgiIiKi03by98QRERER0WNsiSMiIiJKIbbEEREREaVQ5IMNQlrIJMA61r9EREREBLzn/W6iBxsig7hjPtHBJ222x7JLzi+r83nq8lCO+TTnMRzzGOHxuD2WXXKsy5JJe12209OpRERERHTaGMQRERERpRCDOCIiIqIU2iqIG1YL+hUn8ldo+29oOxT9HjizrPlfFUMznlbw2ig8KrsCqiy8x7wPqLJ6NxZ2ZxlW+IB5B+Gvo3rxY0v7XWjYQtqQuOFnYFg9/PF/DMeqz1iX7YB1WXKsyzaW5rps8yBuWEUJNqYPD3h4mMJ2rIMeSJnaSC1HlmX+pi3kr8vgq+ASyrfMtvL/poMc3Gb7LA/E3X0c9cp5VlJ7p06qza75nGZHrM9Yl+2IddkGWJcllvK6bPMgrtjBQ6cG/73NGVzZebj3x9tVhrcO7BtWe9vKFMvIjSeYmu8Ukv9HaOU+ilte3a/hoV1xkLvOm+8p9oT1Geuy3bAuW4F1WULpr8t2vCfOQ98ZI3fpV4EHJxGza+PqSIs7R97wDm4+C0t/aaNaMF0TBdOtI90W6nN71sX0vLosrm6+V13d8wp2peEtnFwDN1nz/WwcsT5jXbYz1mWrsS5L4Azqsp2COK9dQR0tHOti0uuriLkRXDVTIuM6LF2B+X9WE2j0pAzlCqSuvkz9rgnbRSmo4cYuJuWe323x3LosMj+IBq9gVxiiKvtQ5/xakI5Zn7Eu2wLrss2wLlvjPOqyrYM4uUm34tiYjo5VEQ1x6+RQZu/DZsL3kUwH6hTl4k76H7w+HH3C8rde5spG3r33K7i8PRsO6+yaW9Yqdt4H8Ao2ktduwrVvzu4+ruPWZ6zLtsK6bGOsy+KdS122VRAnFZ6lKrze0QK48z15HFWmiFojh+4dL81W+y7c2B9F5TZ8t83LyOZ/Dwu3S3mfhJu3/O6cZ8HvbhzXLb8lpO5/TvsTqseuz1iX7QHrsoRYl0U7n7ps8yDOa6NSz2FwxADOL3DA5g0kuyuWcd29wzBzBRt13A79nVa6d8a5yyNu09OWqb0Pue6HMDbfgXfiMvdxTKZ/YL6rMptOVZm9/IzKLIPayLSEqL9pK498a4pRLcUlcPT6jHXZ3rAuS4R1WZTzqcs2DuKGt3W1M3RRCt2bcPDo1VNXEXYDaT5XnA5LXYW56ipM7cS9FtA0VyLSlXSG9zltT13Btr7dfPYVO7+B7N0Po1B4t/6r3FmqzL7LjFXGPwnr4ptVeZq/6q+vHk5P7uj1GeuyPWJdlgzrsnPGF+CnGMsuOb40Ohm+AH97PB63x7JLjnVZMnwBPhERERGdLAZxRERERCnEII6IiIgohRjEEREREaVQ5IMNRERERPQ03vN+N9GDDXw6NcVYdsmxrJI5t3I65vpwH9seyy45llUyaS8nPp1KREREdMYYxBERERGlEIM4IiIiohTaKogbVgv69SYXFwVUj/D+4WMv76x4bRTM64QuCgW0pfxkWKGNvb1caN/zeyLz/ewIr5JLsXM7Ho+5PqzLdnAKddmwqrfdqVcP3M82M6ymt87fPIhTO3HJtTGVF8dOG0CpioPuI8de3lkZomo5sKfmRb89Gw7LL5rsZzD72cMUtmOx8otybsfjMdeHddkOTqMuG965uL7Owemf8Amf+9lmVODe7JrPKbR5EFfs4GFUg35/cyZ4AbEecxDevYu8fWWWV8RNy8Ud98hkvHu4eRtXwcu2MzWMHjrgq6EjyH7dMfu1+v+VnYd7yB07rY58/B/cEdeHddkOTqIu83Dv5lDulJFz+qfb83Bux+hBeWhXHOSu8+Z7+ux2T5zXhzPO4TI4sA4gc5nDeHbAeOhPxjy5JpW5go06bttDeI+KbIJ+VJO7uiqpFoIui6rfbaC7GKqoBulDTVTSDF3tmy9nQ+1nzhi5Q+7Y5+AIx/9RHXh9WJft4BTqMtk/cmUVOKrACA50Y5yZX3t5+XHDj+3cjtF9G96qbdrATdZ8T6GtgzivrXZOqw60bg57NaSuKgY5B5Y+EG/V4ZreiPn4Mqj1BshOmrAsvyKZdfuPXUzKPb9rYpCD25T7QPyrkmwv6LJQVVXF3B+i0mcl/bSFfPfONM8Pcde9RvlKfzkbXruiThct3LDJMtbRjv8jOcr6sC7bwdPXZV7fQa4se4e01GPepRq5/BXDj+TcjtH9G6LaBBqddJfO1kFcpjZSO+dx7h0qdmRZ6kAYdVDG2AylRDJF1Ez5TVVFUg8qsrytghRzeWaZyxB91TZGXVeS6k9VAOOxuuKcqnHSlSHpMzU0rrt+N9DwDt1ruTI9H1LxVRwb06A7giId8/g/hmOtD+uyHTxpXea3zndL/vys+njeqhq1fBE3/EjO7RjdN6/dhGunP8DdrTtVX5Ec894huScB7ObaUqZYxvV4AqnH4l1jICeZ2d8INcuMMorla3RVzSc3+bbOqLlKAjhLBXA9BnAJndu9g8dcH9Zluzh6Xaa7UgeheU3Rki7V1Rk4Aby/N5oflI/r1jwoV5/T+ITqxkGcbqJVYb2/qke4d2hYVcvzr7i84S3q42voFm1ay99W8yZ8T64281ks1WNzct9JXl2ZBjuyvq8jogtAKlC3pJ+Amt1onHZqXSv1HAYM4FY6+vF/YEddH9ZlW3vquky6UpENL810qd5NzPfTcW7H6GFkUBvNA/xpK498a4pRLX3ltHEQl6n1MEDTv6/jooKJPcVBu5TlPhJ1xSPLs0ourgd8ujIpf1uZe3Ck/JrAoLcqSPHvO4HjX51cVCawI9MXUc5Jb4F50u4MDG/rGKOLkikr+eNvxT129OP/wI66PqzLtva0dZkEQoC9FOVlJIrrdk+uU/zcjlFajS/AT7GnK7shqoU7lEfpOQlxP0vm3MrpmOvDfWx7rMuS436WTNrLiS/Ap8OQLqGL0lncEEpEzxjrMjoDDOJoM/JDkg8Pqbx3gIhohnUZnQEGcUREREQpxCCOiIiIKIUiH2wgIiIioqfxnve7iR5seBTE/fJPF/QIIiIiIjo+Pp1KREREdMYYxBERERGlEIM4IiIiohRiEEdERESUQgziiIiIiFKIQRwRERFRCj36iZFq4dNmFBEREREdW2f0tu1+J44/9ktERET0dJL+2C+7U4mIiIhSiEEcERERUQrxnjgiIiKiE7L1PXG9H3kZL730kh5Jm/vUpz6V6vJLe/73iWXhYzkQnT4ep+eF704lIiIiOmMM4oiIiIhSiEEcERERUQolD+K8NgoXBbQ9813IsEIb4UF7o5d3gYuFvwKqQzP+0A65buvsY9lqHtWLKo5VXAt2zP+wWpht88LCDpdCO5TFsLq8/5u/ox0Eq3hoFy4eH4+r1ncf+/USr12d1xMFqR/U3A+wnK2cSj6I6Gxt2BI3Rr1yxEop38L04QHy4IX8TQc5uE1Wiqv5J9cLq46uGZIqwypKsM12n8J2rOMF7iem2An2/QGu1X+D4FjoFE2Kp5TBlZ2He794NHp9B7Cv1NjD89oFWE4Wjakpl14DKFXQnpoERERnbrMgTgVVrZyD2yc6qWaKZeTGE7COXiWD2ig48adQsaOClJoJAqIDBToNmSsbcPqhiyoPfgx3lBBOL6vVq6EYLC5TROdhhJplvhMRnbmN74m7urGjW8OGoW6NoNtTdydUUTXdY4X2EG392XR7CJWmKi1Hujukuthdu8Qb3sHNZ6Hr6LjpvOF8+Irl6Dh0IX9qWChNoeJgrCfcB5WncFe0LDfo6owqt4DOX6isw99DeV1Xbrt7qvzLiXqM3OUxgoKknn5bSgtUuEtVul2rbZmf2peX93Gx4fwTy1zBhoP+rCz66psNHcPtuSz0OgfzkoHhZUWaoD/rlg8tf0U95ddNS+kX8qPGr9tmoeH7rUOIiB7b/MGGTA2NqNa4YgejoLtnGgr0xi6yNyM1rAXUm4D+LOPlCt5DW1V02V7QHZKFE+6uHddh6UrV/7PU5A115Z2JnU6GlwB7avJhulc8GV5XE/vDp7aLUlBLS/7KPTX8BvehNL3GPt8hW0T5egzHnO2ky2l8XVZDZVRMua20ptz27mny77UrqKOFm1PoPZx5+m0pLWD57p0J0oa4616jfKU+qoghmM98Hz/kviItpdIYNy+LWVfqXstiiNt6zu9OlnnVb+cBahx1XE/0ca2mCd+GsaKeepxe8jOvEx56qtwlrR4elc/F9PutQ4iIHts8iFOKHRUcLVfK+grUXMla9fkVaD50tRz+LORqejxG3TKBmkw3Dl3Zh++Jmw7U6dzFnfSlxk03lKtzddKvmYUE3Ssww02/iz4Juvd+/iVPMlxf2YfSWFlTYe9H8UYFsbrryXQDBZFJXLmtsq7cDuDY+ZeWl4pjYzoKulZPx5NvS7mQuu7iTiKZ4R26QRCZz86Or9k+fuB9Zd6l6pfFrCt1r2VhIZvvoqTmV+1fojft+Ou7ijqug2MZ6lieWVFPPUov+QnVCTNx+QzqnwPVIUREy7YK4tRpDDe2g8rtxHwfomqpK9OGuZKdtjaovEI3bOs/FXQt1ZmaCshq6sq2q89cImK6U74XZtb1JBV9EMweodz25Yj5929Yt9E7wQBOO4FtWSxf62NheOfOg8hYB9xXjlIW5j7PXgPZSROWVcVwuSs3kV3ytSwin7wXj4iObMsgTtXdtQZy3W7oCjunrmD9M4PXnyS78paKWF1h381vfFm8V2ZZsYxr6UaKm05X7HXczoarSvuigDbMcHN/nO4Cy10uBgjBtOE0+tO+mK6nSqjLSVtRbplL/SBHcKLypiZo3rTc9uJI+VefK9J1dqoBnHYC21KOBbeEkmvPWt8Qnn+wjx98XzlGWQTHsbqQ6/TQyru49/zlytPywW2vs+N95ZNPG9RTkp9QnYB1+VxKv/86hIho0dZBnG6NawXXsfJZVeSW371QcVwzfB11hd0byIR+t0RlAlvf8xZHulX8Cjx6uqXhchPdoOdfyfdUBpv+cGnlmT76mQY/jVvy06hZ7nCVHk13Palaff703rpyk/Gu6bYp4PbODN643PbjGPkf3koXVxclGW7+TvG34p5+WxZRVvFIPhw4qWNjUvGXP9/HD7+vHL4sJH3OzM+CY8sxrVLXRpjaE3VY+8uZHe+xLWLr8rVM8jOvEy6arqymGR6Vz8X0avDe6xAiojC+AH/P+AL883HaZTFEtXCH8sjcHyatQRUcpAua+4TPG1Zh3ZVP5Hf6iBbxOD0vr/AF+ERnSgUTFxcluPaNH8DRwYTfHiINfa3TelSaiJ45BnFEaVPs6JvpR+GnEzI1jE76PsJ0KnZG84cXRp39PRBCRLQHDOKIiIiIUohBHBEREVEKPXqw4SM/kcOLL76IV199lf9u+S8REdExvfDCC+YTnYOkDzZEBnGvf4kNdNt6x9vfqu+fSavXXnuNlYHBsvCxHIhOG4/R87PT06kSiNB22BJHRERExxAZxP3hn3zGfKKk/v1v/Tb8zu/8S92lSkRERHRobInbo//xv/+f8Cu/+s/MNyIiIqLD2aIl7pdwo4I8CfT8v+9G95Nm1DP3m7/Rx3d/9xX+73/xcTPkjHltVC+qGJqvz9X8x2ALqD7XwpC3RZgfxE1UFrN3oh7YsZazyinkQcTkI9h/T3bfVflmPUMUb8uWuB/Ah1SgJ8HeH/7Jr+D6ZTOY8PDlP8eP//hPnnEg56FdUCdqq46uGfJsDav6BfRT+SHYaQMoPeOTTb7ll4MuCxtuM0Hgsi7AOZUA6GwNcdfNYaC22UHfJLbVdmQ9Q5TE5i1xn/Twib/+1/Dvmq/02K/+yv9+xoFcBrWRnKwHuDZDni15c0LwloSMhWzexT0jDlUWl8iNJ5iar7HWvWWCb6E4LO8ebj4Ly3w9LaxniJLYriXut34B/833+N2p3/uzv28GUth5B3L0iNeHM87hkhEHvOHdPDiQ7jBpUZFu1sJSS2W4hcaTF/qbdLo7Vg1dGL84n7YJlr12qDs7GLhggn5Ul/ewGuoCDoarPKjPs9nI8oOuvJjlL4haB22DPOh1VvOPSr+QBzV+TdmEhxcqDsZmsE/l1apjPK7DknU0y63q5frfw/MMymCeRs2zPTT5DK/rMg/tillOTH6D1SOizW1xT5zy19+NH/5fPoM//O2P4Fvvvg03v2KG0wIGcs+DDiTUCRGtZ/xCeh0M+CdmeVG83ZMWNP8EjsZUd7NObRelWUQSJulKaiI/nd81XUF71pQn4x1ke6a7tpeFU5GAYIjbut8dqLtw67ePA4Kxi0m55083yM27eYsdjGRYMK0eXkT5egyn7wckXl8FPtdlNTRu+WEr1mGjPMSlXyzLh56NvKTVw6Pytpi+18jp1HNFdKYt5HU3eMffb9Vys3q5N7iP226S5mak8tsC6mpD68+S975aYpQMaj2zHN2qupiv+H2CiJLYvCXu5R/CR3/xh/CfyH1wL/9N/P3y38AnPLbGxWEgd/4yNXlJ+hS2Y81bTZ6b8D1xwYvipXUSLdwU/ebJzJUKPNz7xyf7IF3wdvmMCjAeRqgF/Xy6lXOMumVab3QLkoO+J13YXZQKBVT7l+hNTTASlrdny4eV9f8VujXIb1HS8zODizcqOHEkIPGgYjgVl6s5xi7fTCRWrcOGeYhMv1SWM3F5Gy6mz6j5+EHfCmq5V5J+aVkL203SBFkIf05q1byJaGPbtcTRRgaDX2Agd/YyuLLzcHlT3IFc+y1usz8VIGXMfVO9BrKTJiwradecdCU6yDZMa5e0SJkxKqqArcKMvg42wkFK1PLNqK2syMPGIvJ2mje6EdGebdwS9wc/+914xw//Ev5Avnzyl/CP/yHwt//Wt+hxFO3NX/km/ML/9iEGcmdGd6NWh6YVQVpuxsjxprg5HRDVcWvul9Ldk7lLFe4uCdLNbnQz96YF3akyPt/F3Wx8cK+cSYciap0eWhs9WJIDLD8nXn8Sul9MgnHAqTiAfeXnNXb5IevWIVJcHiIslSWmE//fuLwtpddlrz8lEDVt1HbbxiHnTfQMbdwS986/18GH8FP4j1Sg947v+Cngf+jwJ0YSeOGFr8WH/tefZyB3RjK1HgZomnvBKpjY08P+VEPq+PdDoWnprj7LsTGNLCBJN1CRk5/uQm6qG/RCrUlL4ysTc89dETetnBos5W/BsdU0iaIBmU5mJ9NdoOK4ZrhPuvgk4rFnzXBxyw9btw7LVufhMb8s3ZKZf9M1QVlc3hbTq8EbtPT5067fbgllLnXgph+g2Pe8iZ65yBfgy6uj2KW6GXnt1r/5N+ZqeIV/9a/+P/zAD/6QvlfuFPFFynMsCx/L4fR4wyqsuzIeGACRwmP0/LyyywvwGcDt7rOffd18Av7T7/7bsz8J4IiINjV/O4j/BLB+6IKInrWN74mj9SSA+yt/5WV8tP+r+rtUuvX639etb8EfEdEmih15Cto8vBA8AUxEzxpb4vYsCOB+9dc+ih/70YYe9oGf/8f48R/77/RnIiIion1gS9weSQD3Td/0Th3A/Yd/49vxze/8Zvzmb/4L/NW/+u/oz//HR/9Pk5KIiIhoN5EPNgh5uOHVV1/lv0n/fdu/ja/+qq/FX/zln+DTf/pHugw//jv/D37ov/5h/N7vfRyf+MTv4e/8nQr++T//RT1u36TLloiInic+2HBekj7YEBnEcWfYXhDYBb73P/svcPtTP4lv/da/hu/4jv8Yv/3b/8yMOU18ymmOZeFjORCdNh6j52enp1Npf7qdFt797v8SP/hfvQ8/93M/Y4YSERER7YZB3IG99NI78IlP/BY+8PM/g2/7tn/PDCUiIiLaDYM4IiIiohTaKoib/+hkAdVkb5ymMK+NvPnRzot8Hu2BGZZfeh/jLqLe77gvMu8g/4UC2rIP7Ht56+Y3rOr9L3hl5JNSea3qVwo9X/IGgfk+UT3+djnk/h52rOWsE5OPoG4+WL0sy33qY38Tp5DfU6qr6OxsHsSpHbLk2pjKD05OG0DpeZ+8NjfAtfVhvHdqfrTzg+/Fh6+u1dC0GKJqObCD/PdsOE+wDwzvXFxf5+D0n7Jm9NAuqJODVUfXDHmW5BVQJaAx2yeycCyetI5viLtuDgO1DQ7zNq4nPPa3CrxYV9H52ziI8+5d5O0r6B8Lz8hLnF3cMYpLzpvC/c73Yv5u7RrGD12oc2A6ePdw8/ZC/kcPHRz3BUAe7t0cyp0yck5/f1fUG8ugNpIThArMzZDnRwWyTRetqdoHwvvEIIf6LSuGo9LHZhax79zf1Ukc+xtgXUXPwMZBXOYyh/FsZ/TQn4zh3nPXTCxzhfeijp9qD+A9KrbfRf867zf9X+RxHTTPqavQ67zpEshf+y0calhefb4O0of6T4bVC1T75su+qfzbKv+37WFE/ifoR3W1q7xWpcVKd2mYrjZ9ZV1FNUi/Sf69PpxcWVXGFrJwoC9wzfzay8uPG077IdtinMNlcKIMWFnku3cYSvnPupqHoW5n83nV9lHjVu83y60qMftfuKs3NNxrh9JGNRt6Ko/B8vV0QZoNlpN4/dT4oKUpar2XhhcqDsZmsE9aneoYj+uwQuU6K6eleeosLKRR81THtJ/P8LqGPNmxry4UKmbdYspoPocQ1lX0DGzenVrsYJBzVEUhO/mtOhTyZgQlk0Htg32863ebsCw5UPPzSvpjLn63/EG/6b+fg9uUCktVYN//Ybzrg6ZL4IPvwoe/31RkKv27JP205Z8w9UykS+Ua5Sv95QBU/nsDZCdB/kPdZmMXk3LPz+cglH91wsn2TP6lq61i8q/SZyX9hvn3+g5yZbmezuDKxrybInL5K4bTfkS1/mQuoX82XE6keRf6Om94h676T7fch1uNtt1vlltV4razqrNGMkz+prYZPsRt3e961MPqt2b/C8jyS4A9NdPJrSMVtKdq1EbLiUvvByZomPn3bFOTxq33Yvpew/9R9rkiOnIc5VuYBuUyK6cb3IemndouSkF0IGluRvoYRL0J6M+S96hWo6c69mW5Zt1GNfVtsSwW1mcB6yo6f1s92DB7EfOog/LS9SAlkCmh1h3rMpyqYK0eBGXf+V78g5Jp0rh8l/+vupL78Mc+hrquhNSfutr+2Mc+jP69GifdspI+U0Pj2pwc5UR5LVd+B5Qpomb2gamqaOpBRZe3cRP0qVlZ/1/dUjNeyP94rK5I5WQoXR2SfqP8e+g7Y3RL/vys+njeMhy1fBE3nPZjPIFszgUSpOkPGVzmxpioBHJvUKvVgisbejpRAZK5LWPb/WZZ3HbWrSumdUPmowdayOa7KKnh1f4letIdrIcbsny0cBO8ZV7t852HEWoSdW60HCVu/WT+y+sRt97DxfQZNZ+1l89BOS0tK6Oiibx7PztmZt2N4c9xnvTYN1atzzLWVXTmtgri5qS/H8g96kuhpDKlMv7ux34XEpPF+7voS9A8+xujdmlGGcXyNbqqZtEnypuDhnALMsUyrqNO4guu/RaP2Z85GYYkzr9UtLlBaF5TVZ2bipaOL9zSFqaCtLE5wcm2de/buHPViVDtuDl1wm2rimN9vbF+v1nPv7k92zCtG9KSoodn/PsZew3TUhPTJZdY3HK2sY/1PryjH/s7Yl1F52jzIE4el676VzPe8Bb18TV0azEl4rXzuLg2V4PyfXCHn/vOd2EpJptTJ8n3fufP4S7oB1BX+5E/RSIVlFvSTw6vvZregb6PyGx//V2uRlfdTK1P8urKNZT/yKfMEuZfuieQDS/NdFPcTcx3Oi4VDDVyqEsQFGziodrGpdAJTm3bXL0OV7e8FVHOOag7udX1RtL9JpEcYPk7lddXwaX+JPfkFdBW+al1emgtB6KyfH0/1Wyl/PQrT8BRy4kRzD8oNGmZFHHrvZRejoPEfSBR0+Yu/VbQDTz1sT+TcH1YV9FzsN09cepqQu6Js1RFfT044aeTTlCm9kH08WH/nkIpwybQ/6Dc5xFHnSQ/2Ac+bPlN/N//u3hvZHo5OUprvOmiOpBMrTfb/kH+B701+e8NVM1l8l+ZwI5MnyT/0j0hvXCLKaQ7Bd1u8pMa7ZeqE6ZqEzetYJ9Q23g6QtATqSIbZPPzFvuibGh9s/cqSfebdYq4acls/LxVHL+T1x+eM8MtOHYvlF+xtHx/R1/RIha3nDgy/xbckpl/0zX7b9x6L6ZXgzdo6fOnVRtIT2s5NqZb/AbJkx77mUsduOmHNhKuD+sqeg74Avw9W34B/vEMUS3coTzaLah+uhcp7yf/+8SXSvtYDocnP5Zs3ZXxcJgfeDtxp3fsr8a6ig7vFb4A/xkZXKsrxxJc+yYlleAS6aJPc/6JtjB/843fSnTI+8FOVtqOfdZVdGLYErdnab8i4hXdHMvCx3IgOm08Rs8PW+KIiIiIzhiDOCIiIqIUiuxOJSIiovRgd+p5Sdqdynvi9oz3xJ0PloWP5UB02niMnh/eE0dERER0xhjEEREREaUQgzgiIiKiFNoqiJv/SGUB1d3eGv0shX/ksxC8py9FuP3n0r4t90LeMWnKYL5fPFFZ7PSO1YSOsYx1TiEPIiYfwXFxqPrhEMcd6zVKo82DuGFVv/h3+vCAh2kDKMm77CgxKT+Y8nuYwnasdFUY3P5zad+W+5RvmXLw94ts00pXUHsqQdFZGOKum8NA7QsHeYvYuuNum23Jeo1SauMgzrt35y/+zchLn13ccW9PrtjBQyd4qXIGV3Ye7n2KTh2S/5HJf0ZebO4iTdnfq7Rvy0NR9YJ+QbnTZ1D0HHn3cPNZWObr3h3iuGO9Rim1cRCXucxhPKucPfQnY564tqbKzxkjd+lXR6nj9eGMc0hr9vcr5dty3zKXyI0nmMpnr41qwXS1FqrQDXS6taS6MHx2LbgifTuqyyuUvlBxMDaDpXVl3s1r0kfOx0O7Usd4XIdlWnC8dmh8ZIviBP2ovEQtU61ZVX2ezUbycGHWN2pdl3nywvXwPINEG+QhYfldFNT4oBUrLm+h4Qvlram8WqYsZR3Ncqt6uf738DyDMpinke7RoclneF3jLB93j7fl8noEqx2L9RqlyObdqeqKZZBz1AEqB8StqkbyZgRtymtXUEcLaXzvtT7JqcoaLb4IWqR5Wx5G0JohJ1UH2Z7pau1l4VTMyVWd/YPhU9tFyY82VqR3MSn3/OGDHNymDPdP2mhM9fBeI/Rj5aquGkla+ZvaJr3yaD59XPVayEuXsG6NGeK27ncH6unqt49P/JF5USKXWUT5egynr1OoGEEFPtdlNXTFus5ImhJg++vnd/VV0JboeKM8xKVfLL+Hnm1q9Li8rShvrYjO1JTlQ8evG9Rys3q5N7gPTTvf5ibNzUjltwXUm4D+LHlf3Zr7+LjL6Fbg+bZczO/CMiOwXqO02erBhmJHHWByYI86KC9dh1EyUllUHNtUNOmTqck+8MzvAzPSvi0PY4qJtGZAWjXGqFumJUS30jjoSxCSz+LKFFjmSgUP7j083QoSl95WJ2szgZX1/5X0+iTuD8+o4bPLSt0C47fu6PmYwZHzWSABaBclNW21f4ne1AQjYXHziFlm8SboXpaWI4kR1Bzj1jUctQTrVwsKSgVJDyPUpK9ywzwkKb+ZuLwNV5R3HLXcK0m/vK2CbR6kCbIQ/rxCouNu1TIjsF6jtNkqiJvzcO+CXUgbksrHUpVPL/Unfd4Hdj7bcs8W7ou69lu1Zn8mCIm1afoo0q3nINswLU/SOmTGrJdBbaSm6TWQnTRhWQm64LQVy8xcwVbhRF8HFeEgJWJdd9qRdlnvZfvYDodx2OOO9Rqlx+ZB3LCKi6rfPO8Nb1EfX6PMdufk1FVyRbpqUnrSl8rzQl2i+tXbM78PLOXb8mC8oe7CyjVUuUjwku/iLrihSpXZ7MnB8WTW6qS7GHOXq9NH0cFRHbfm3ik9H/1J5ADL3zJef7JBn4EKhC4KaKOIWqeH1kY3ucctUwIDwKk4wOzBsATrGqzfLI3Jm77ZMM4G671UfphO/H/j8rayvNeImla2uf62gU2Ou4TLZL1GabXdPXHqWlLuibNKLq4HEV0NFGt4K90bXZSki8L8pemnGDK1ntr+Tf+eyIsKJvb0MD8jkAJp35Z7pW9kN+VgNTFpBPuF3KM0UNGL5Y+rTGD3zMlXBUeTij+NtKpM9QQr0kfy74FyS356ldy0PMmT8zIbf/4Vx9VDY2Uu9cle34yvp82ZaS04di9h69jqZUpXnkQ89qwZLsm6LqVRZYuByk9si9iG671UfhdN1wRlcXmLK+8k/GnR9Kedb/PNrD3uFrZlsmWyXqO04gvw94wvwD8fLAvfQcpBWnYqYDf0ifGGVVh3ZTwwgkkV1lXn5xW+AJ+IiNYJv/1AGvr0QxdElAoM4ojo+DI1jNgKdxJmvzYgf6POjg9WENExMYgjIiIiSiEGcUREREQpFPlgAxEREaUHH2w4L0kfbODTqXvGp1PPB8vCx3IgOm08Rs8Pn04lIiIiOmMM4oiIiIhSiEEcERERUQptFcTNfxyygGqyN0NTyLmU37D6jF8zZfBYUOTtC+bHYhOVRdQ7Qg/hWMtZ5RTyIGLyEey/h9p3D3l8sP4h2iaIG1ZRcm1M5Ychpw2gJO+no8TOpfzUSaHZNZ+fKx4Lc/mWXw66LGy4zQSBy7oA51QCoLM1xF03h4HaZgd5y9a642OX7cv6h0jbOIjz7l3k7Sv/l9Yz8rJlF3eM4pIrdvAQ/FJ9xkI27+I+dWcpD+2Kg9x18ldfn6Wz2JYHkLlEbjzB1HyNte6tDXyrw2F593DzWcS+S39XBzs+WP8QBTYO4jKXOYydvrl68tCfjOHyzLUdrw9nnMNl2s5Sw1s4uQZusuY7pXdbHoA3vJsHB14b1YLpZi2saInxhvN0uutNDV0YvzifoBfNa4e66yK71iboR3XpDauhLuBguMqD+jybjSz/wuQ5ZvkLotZB2yAPep3V/KPSL+RBjV9TNuHhBRX0jM1gn8qrVcd4XIcl62iWW9XL9b+H5xmUwTyNdGUOTT7D6xrj0fEhgZhZfsx6BKv9COsfopnNu1PV1dUg56gDXw60W1U98WpoG/rkoypRtG6QrtdNq8q/CTQO0v+STundlnukgwH/BCwvUbd70gLjn6jRmOpu1qntojSLSMIkXUlN5Kfzu94qaM+a8vyWl2zPdNf2snAqcuIf4rbudwfqLtz67eMT/9jFpNzzpxvk5t28qh4bybBgWj28iPL1GE7fD0i8vgp8rstqaNzyw1asw0Z5iEu/WJYPPdvUvHF5W0zfayz/iHsRnWkLed0N3vH3W7XcrF7uDe7jtpukuRmp/LaAutrQ+rPkPbiwfyz6+Mig1jPL1611i/mN31dY/xCFbfVgw+yFyaMOykvXd5RMpiZlOIXtWPMr7RTw2k249jMOViKkdVvuVfieuOAl6tL6ghZuin7zS+ZKBR7u/eOTfZAuePN6RgUYDyPUgn4+3YozRt0yrTS6BclB35Muui5KhQKq/Uv0piYYCcvbs+XDCjXd6FYfv0VJz88MLt6o4ET3NHhQMZyKO9QcY5dvJhKr1mHDPESmXyrLmbi8DRfTZ9R81l5uq+VeSfqlZS1sN0kTZCH8eYVEx8eqZYaw/iFatFUQN+fh3gVy7EPaUgZXdj5F3dFyYhtjXLf0CcOq+5/5hJhI27ZMm2u/xW32pwKkTAa1kfrcayA7acKyVnTBLZCuRAfZhmntkhYpM0ZFD7BVONHXQUU4SIlavhm1lRV52FhE3g52o9u29nF8sP4hWrZ5EDes4qLqN/t7w1vUx9co87IoMd21oC5H/WrHr5TSEwSbk6Y5WUxbeeRbU4x2O5ulVrq35RHogKiOW3O/lO6ezF2qvWhJkC44Gcu9ZReFeXeqjM93cTcbH9wrZ9KhiFqnh9ZGN87nAMvPidefhPoTJNgAnIoDzB7gilt+yLp1iBSXhwhLZYnpxP83Lm9L6XXZ608JRE0btd3W2Oj4SLRM1j9Ey7a7J05do8r9L1bJxfUgoguDYmVqPVV+TXP/UAUTe3qYx/vp4Lgt1/Hve0LTtJw4NqaRBSTpBipy8tNdyE11g16oNWlpfGVi7rmTp+NzarCUvwXHVtMkOp/LdDI7me4CFcc1w33SlScRjz1rhotbfti6dVi2Og+P+WXplsz8m64JyuLytpheDd6gpc+fdv12W23t8ZG51IGbfrBiT8skem74Avw94wvwzwfLwsdyOD3esArrrowHBjqk8Bg9P6/wBfhEROdj/vaDC/0EsH7ogoieNQZxREQpMPtVAPkLngAmomeNQRwRERFRCjGIIyIiIkqhyAcbiIiIKB34UMP5Sfpgw6Mg7pd/uqBHEBEREdHx8elUIiIiojPGII6IiIgohRjEEREREaUQgzgiIiKiFGIQR0RERJRCDOKIiIiIUujRT4xUC582o4iIiIjo2Dqjt233O3H8sV8iIiKip/Oe97v8nTgiIiKic8UgjoiIiCiFeE8cERER0QnZ+p643o+8jJdeekmPPIZPfepTR13eOWHZEZ0+HqdEtCm+O5WIiIjojDGIIyIiIkohBnFEREREKZQ8iPPaKBTa8ILPFxe4mP0VUB3qMfujl1FAOzzbcB727dE6Betlxh/aPtct4fYZVgt6vKxj+DMRERGdvu1b4vItTB8eIA9FPEwbyDYtFBYirn0Yo145UNAWJbxO6m86yMFtHnH5+xS3fWbB4hB33RwGanynGP5spiciIqKTtp/u1EwRtV4LcPr7DXhUINLKObh9otahTLGM3HiCqfmeWuHtk6lhNKoh493DzWdhyfjwZyIiIkqF/d0Tl7k8SMBzdWNHt4YNq6EuQ9PtqVuZqqiarsFCe4i2/hzqTlRpqgUznUq7qvHQG96FAp2Y6bzhfPiK5eg4dCF/algoTaHiYKwnPJBg++g8FFCw6hiP67Akf7PP83w+WtcVeV9O45e5jAt1Rycsp6BcvXZoHqs2EhER0TO1xwcbLGTzLu73fb7N1NCIao0rdjCadReGAr2xi+zNSA1rAfUmoD/LeGkl9NBWwVK2Z6brZeGEu2uDoMb8WWryRq+GTOx0MrwE2NNZtyVKFRWIyPC6mtgfPrVdlIJoRvJX7qnhN7gPpek1Dv3OWrN9dJRto6fKJx90uc4+d1BcVUYLeY9PM9Fp1PBZd/SqcoqazxC3db97V2+7+q0fXBIREdHMHoO4KSbjHC4z5useFTvqpL/cGqdbcExrjbQkmcHI27gK8hD+LLw+nPEYdcsEaroFykE/mPHCfWQDtODiToKeuOmGarhKdVMzC8kU0XkYoQYzvOgPz1zZyLv3fv4lTzJc5hlOY2WR158OxWyfdX2mcesq5RDO+4o0wTpBrZMWrGtUOUVuDwk4uyip7VvtX6qAU4JLIiIiCttfEHfQ+6qKuLEdVG4n5vsQVctBtmFafKQlyYxZ79pv4Zn9qWDCxBYL5D6yRg7du6ANKGK6NN1EttH2SbKu+yqPqO2RQW2kPvcayE6asCzTzUtEREQz+wnivKHuPsw1pOvxMDK1BnLdbui+sRxg+Uvz+pNk95NlrmDnu7ib33iFlT/rUSzjunuHYdx0Mhx13M6Gq+DyooA2zHBz35fXdzDOXS6WTTBtOI3+dACbbJ8kZbRpOa4qp8j5BONVIN3poXWIbnoiIqKU2z6IC98/ZjUxaUwP/PMURdy0gvY2+Qw4lr/8iuOa4etkUOsNZEI/35UJbH3PW5zgPr+46ZaGy010g57fkiRPgzb94ZZjY/qocPw0bslPo2a53+7UrbdPkjJKkiZsVTlFzUe2b04NVsMuLDi2pDWzIiIiIo0vwE8xlh3R6eNxSkSbeoUvwCciIiI6XwziiIiIiFKIQRwRERFRCjGIIyIiIkqhRw82fOQncnjxxRfx6quvHu1fIqJz9cILL5hPRETJJH2wITKIe/1Lx2uge8fb36p/5JU299prr/EEQXTCeIwS0TZ2ejpVAqtjYUscERER0eYig7g//JPPmE+HJ12qRERERLQZtsQRERERpdD2LXGf/Ce4eXsNv2a+bmt9S5y8R1NevxT8FRC8apPWkHeRLpVdddWb5Ne9A3VfjrWcVU4hDyImH8NqQW+zldvrKal8Vy+q6ugkIqKnskVL3O+j+z1vxTu+40fxT82QXSRribvG4OFBPwDx8DDiezQ3kW9hGpTd1IbbTBC4rAtwTiUAOltD3HVzep8/6PuIt9qOHtoFdUFg1dE1Q4iI6Gls0RL3Lbj+xc+oNB/B95khu1jbEufdw81nYZmvtIPMJXLjCabma6xMDaPRihfarxtPuznpfT6D2kguCgbq0oqIiJ5SOu6JGzuoyNX/xQUK7Evdmje8mwcH0h1myvSisNQtFm6h8YbzdLo7Vg1dGL84n2DzeG2/O1BPE7nNJuibLsOFbt5hNdQFHAyXLvVQN7osP+jKi1n+gqh10DbIg15nNf+o9At5UOPXlE14eKHiYGwG+1RerTrG4zosWUez3Kperv89PM+gDOZp5BgZmnyG13WZh3bFLCcmv8HqERHRadr+nrg9SfR0at5GoyfdgQPkHGt+8qT1dDDgn5itJmD3pAXNP4GjMdXdrFPbRSmyUCVdSU3kp3uYNoBSBe1ZU56Md5CVbSPje1k4FQkIhrit+92Bugu3fvs4IBi7mJR7/nSD3Lybt9jBSIYF0+rhRZSvx3D6fkDi9VXgc11WQ+OWH7ZiHTbKQ1z6xbJ86NnIS1o9PCpvi+l7jZxOPVdEZ9pCXneDd9Q3RS03q5d7g/u47SZpbkYqvy2grja0/ix576slRsmg1jPL0a2qi/mK3yeIiOhUnH5LnOm6K0rfXaaIGzsP9z76tEQRwvfEjTr+/YReHw5auNGFqor1SgUe7v3jk32QLrgJUZV/R+5JDPr5ZPx4jLplWm90C5KDvmchm++iVCig2r9Eb2qCkTAVmAfLh5X1/xW6Nci0dsn8zODijQpOHAlIPKgYDq0bNcfY5ZuJxKp12DAPkemXynImLm/DxfQZNR8/6FtBLfdK0i8ta2G7SZogC+HPSa2aNxERnaR0tMTRCQs/dCJ/8uCJuW+q10B20oRlJe2ak65EB9mGae2SFikzRkUVsFWY0dfBRjhIiVq+GbWVFXnYWETeeHMnERHtycm3xOl7q6pDv0XAk246wN64mYEW6ICojltzv5TunsxdPn5QIUg3u9HN3JsWdKfK+HwXd7Pxwb1yJh2KqHV6aOVdJG88zQGWnxOvPwndL5bBlQ04FUd2AD+vscsPWbcOkeLyEGGpLDGd+P/G5W0pvS57/SmBqGmjtts2DjlvIiI6iJNvicvUehig6d/XJTd1tXr8iZGd+fdDoWnprj7LsTGN/C0LSTdQkZOfTpf/QJX/rDVpaXxlYu65K+KmlVODpSvRgmMn3WYyncxOprtAxXHNcJ908UnEMw/i45Yftm4dlq3Ow2N+WbolM/+ma4KyuLwtpleDN2jp86ddv90SylzqwE0/QLHveRMR0cFFvgBfAqtjBXJveeOX+YLoLfHl2qfHG1Zh3ZXxwACIFB6jRLSNV3Z5AT7viSNKLni7gm7BksZieeiCiIjowE7+njiiU1fsjOYPLwRPABMRER0YW+KIiIiIUogtcUREREQpFPlgg5AWMgmwjvUvEdE54oMNRLSppA82RAZxx6x0+PTW9lh2RKeNxygRbWOnp1OJiIiI6LQxiCMiIiJKIQZxRERERCm0VRAX/nHTQvBuyAOaL6+AarI3qZMhbxAomG11UajiCJtrUdT7TA/hWMtZJyYfwT58sP1XljvbzgW0ZTn7LpN9zu8U8quODalTjn5MEBHtyeZBnKr4SrAx1T9uOoXtWIcNrGR5rlnetAGU5D2PlIi8AqoENKbmh2h7WTgWT1rHN8RdN4eB2gaHeRvXEFXLgT3bzjacYx0nWwVeT5jfkOGdi+vrHJw+DwgiSqfNg7hiBw+d4CXjGVzZebj3h6sEvXsXefvKX15GXk7u4o5RXAIe2k0XrWkHxdn74msYDXKo37IAj8q7h5vPIvad+7vS87dxFd7OD2q7m68n5yTy6+HezaHcKSPn9PfX+kdEdEQ73hPnoe+MkbsMauP9y1zmMJ5Vsmp5k/FBg8az4fXhjHN4tGmsLPLdOwx1d1bQ+jFEdfmzbmGpoh3Vla3GVQtBV5jpojXpqzr9cqvKBP2o+YS7ekPDvXYobVSzoafyGCxfTxek2WA5iddPjQ9amqLWe2l4oeJgbAb7pNWpjvG4DitUrrNyWpqnzsJCGrllYWjyGV7XkMwVbNRxq9J5j0bHlEnUuiwt92KWWIrwAtW++TKjLhQqZt1iymg+h5Any2+IHB+5sgocLWThQDfGmfk92ifihhMRPbGdgjivXVFVcQsHfd93sYNBzlEnQKm8b1UVnzcjaK2o1p/MJfTPOcuJNO9Cx8PDO3TVf7qFM9xqNHYxKff8Lq9BDm5TTtRy4naQ7QVdYeoUWDEncJU+q9MvtapEzkdR23Ykw+RvapvhQ9zW/a5HPax+uxQIyPJLgD0100kXewXtqRq10XLi0vuBCRpm/j3b7HFx672Yvtfwfyx7rojOtIV8voVpUC6zcrrBfWjaqe2iFEQHkuZmpPLbAupNQH+WvEe1GmVQ6w2QnTRhWX6QMQswY9dxzTaUPEuwr2ci3cHXKF/pLyGyXLNuI2mdXyyLhfVZ8FT5nfP6DnJl2RrSm4B5l2rk8lcMJyJ6QlsHcdJaUnFsU3kf1uwF46MOykvtHLTCeAKJbRZIkKY/ZHCZG2OiEsi9Qa1WC65EcdOJCpBM93XeVgG62bpW1v9Xt/CNUdcnX/WnW5kc9GVB0kU267sNiZqP0K0rpnVD5qMHWsjmuyip4dX+JXrSHayHG7J8uXAI3jKfUUHSwwg1iTo3Wo4St376wmRpPeLWe7iYPiMtnfrTCkE5LS0ro6KJvHvvBweSJshC+HMcVQ41c5xMVZBRD4KcuHVctw0zNTSuTWAvQf61tFqtsWp9lj1pfv0ehG7Jn59VH89b+6OWL+KGExE9oa2COAngLBXA9Y4QwC2S+1hw0O7bsxFuaQtTQdrYnOCK5Wu4923cuepEWLtETp1w26qA15fvtd9SNvszQdRG/Jvbsw3TuiEtKXp4BrWR+t5rmJaamC65xOKWs419rPfhZYplXEcF8AvWr4vsH10VFekg/4DN7UfPrwSFuUFoXlMVepqgkIgoRTYP4rw2KtLddawAbljFRdW/SveGt6iPr6F7QWgNFQw1cqhLEGQCOW/YRqEUOsGpk2euXoerW96KKOcc1J3c6vLVwWEXd0H/l75faNuupRxg+XuR11fBpf4k9+QV0Fb5qXV6aC0HorP7qWYr5adfeQKOWk6MYP5BoUnLpIhb76X00k2XuK04atrc5cbHlb6H0Bwj+ru0RK16kCJuXfxvcxJcuSX9dPjalkCRcH2eOr+SL2TDSzNdqndmWxMRpcTGQdzwVrqjuihJt4b5O+hvxck9ceoqWe6Js1QAcj044afuTo0qu+kAaJpuKKs5gT0dIeiJVJENsvl5y2axrIIdfbP3Kv79THAsf/tX1Dx72wT08qSxzMbPW8XxO3n94Tkz3IJj90L5FUvLt5rAQKWJjQDilhNH5t+CWzLzb7omKItb78X0avAGLX3+tGoD6WmldXu6xW+QZGq92TGi56OLZNU2iVuXZRLYS0+i6V6PkrnUgZt+aCPh+jxpflXoJzGcvRTlSdcvut3kATgR0QngC/BTjGV3ePJjydZdGQ+H+YG3EzdEtXCH8igtF06nl18eo0S0jVf4Anyi7YTfSCKtRIe8H+xkyW0MFyW49k06Ari05ZeIaA8YxBEtmT0NLX+jzlJ37jNR7Oj1H6Vl5dOWXyKiPWAQR0RERJRCDOKIiIiIUijywQYiItoPPthARJtK+mADn05NMZYd0WnjMUpE2+DTqURERERnjEEcERERUQoxiCMiIiJKoa2CuPmPoRZQ3e3t5Ikce3lnQ94xaX60dl5+B3xF2io7vWM1oWMsY51TyIOIyUdwLB3qOAr/UPK+XsfH45+IKNrmQdywql8wPZUfQp02gJK8M/GAjr28c5Nv+WVnyi/btA77rtt9O5Wg6CwMcdfNYaD2hYO8RUyOVZhj9WEK27EWg65ttiWPfyKiWJsHcfLL6CPz8umMvEDdxf0Bz7DevTt/oXVGXmbu4o61+HZU+ekXlDt9BkXPkXcPN5+FZb7undQNneDF9Blc2Xm4u1YOR65viIjSZLd74rw+nHEOlwd8003mMofxLOjw0J+Mdz8xPGeZS+TGE0zls9dGtWC6WgtV6AY63VpSXRg+i5lXpG9HdXmF0hcqDsZmsLSuzLt5TfrI+XhoV+oYj+uwTAuO1w6Nj2xRnKAflZeoZao1q6rPs9lIHi7M+kat6zJPXrgenmeQaIM8JCy/i4IaH7RixeUtNHyhvDWVV8uUpayjWW5VL9f/Hp5nUAbzNNI9OjT5DK9rHHWsOmPkZpXD4225vB7Basc6Qn1DRJQmWwdx+mSqTgpoHfiF0+pKfJBz1IlHKvpbdXrMmxG0naA1Q06qDrI909Xay8KpmJOrOvsHw6e2i5IfbaxI72JS7vnDBzm4TRnun7TRmOrhvUboR6TVNh1JWvmb2ia98mg+fVz1WshLl7BujRnitu53B+rp6rePT/yReVEil1lE+XoMp69TqBhBBT7XZTV0xbrOSJoSYPvr53f1VdCW6HijPMSlXyy/h55t9vy4vK0ob62IztSU5UPHP2bVcrN6uTe4D0073+Ymzc1I5bcF1JuA/ix5X92a67UrqKOFm1nlkNGtwPNtuZjfhWVGOFp9Q0SUIlsHcZmavCQ84r6XA5i9kHzUQXmpfYE2NcVEWjMgrRpj1C3TEqJbaRz0JQjJZ3FlWjsyVyp4cO/h6VaQuPS2OlmbCays/6+k1ydxf3hGDZ+F37oFxm/d0fMxgyPns0AC0C5Katpq/xK9qQlGwuLmEbPM4k3QvSwtRxIjqDnGrWs4agnWL3jhekYFSQ8j1KSvcsM8JCm/mbi8DVeUdxy13CtJv7ytgm0epAmyEP68ggRcFcc2wVqMVcuMcMz6hogoLXbrTlVV9F7ue0nMw72LUBcNbWzhvqhrv1Vr9meCkFibpo8i3XoOsg3T8iStQ2bMehnURmqaXgPZSROWlaALTluxzMwVbBVO9HVQEQ5SItZ1p91ul/Veto/tcBgSwFkqgOutCuC2duz6hojotG0cxOluDXUp7Fejy/e9HMCwqpbndzt5w1vUx9cosz9lO95Qd2HlGuoEK8FLvou74IYqff9T0L03mbU66S7G3OXq9FF0cFTHrbl3Ss9HfxI5wPL3Ga8/2aBtVQVCFwW0UUSt00Nro5vc45YpgQHgVBxg9gBNgnUN1m+WxuRN32wYZ4P1Xio/TCf+v3F5W1nea0RNK9tcf9uAyktFuruTBHAJl3n0+oaIKEU2DuIytR4GaPr3qF1UMLGnh/m5goDcEwf/njir5OJ6ENGFRvH0jexB11sTk0awveQepYGKXix/XGUCu2dOvio4mlT8aaRVZaonWJE+kn8PlFvy06vkpuVJnjCW2fjzrziuHhorc6lP9vpmfD1tzkxrwbF7CVvHVi9TuvIk4rFnzXBJ1nUpjSpbDFR+YlvENlzvpfK7aLomKIvLW1x5J+FPi6Y/7Xybb2Z4K13EXZQkX+Zv4edsFrZlsmUevb4hIkoRvgA/xQ5SdtKyU8GBusNoW96wCuuujAdGMKnC+o2ItvEKX4BPlG7htx9IQ59+6IKIiMhgEEeLMjWM2Ap3EmZPZcvfqLPjgxVERHRuGMQRERERpRCDOCIiIqIUinywgYiI9oMPNhDRppI+2MCnU1OMZUd02niMEtE2+HQqERER0RljEEdERESUQgziiIiIiFJopyBuWF16rc6BzH/0tIBqsjeek5C3L5gfi01UflHvCD2EYy1nlVPIg4jJR7DPH2p/P+Qxdax6gYjouds+iFMnn2bXfD6kYRUl18ZUfvB02gBK8t5FSizf8stOl58Nt5kgcFkX4JxKAHS2hrjr5jBQ2+wgb9lad0ztsn2PVS8QEdG2QZyHdsVB7jr5K7a35d27yNtX/hsEMvIScRd3jOK2k7lEbjzB1HyNte6tDXyrw2F593DzWcS+S39XxQ4egu2XsZDNu7jfS0R+vHqBiIi2DeKGt3ByDdxkzfcDylzmMHb6plXAQ38yhrufM86z4w3v5sGB10a1YLpZCytaYrzhPJ3uelNDF8YvzifoRfPaoe66yK61CfpRXXrDaqgLOBiu8qA+z2Yjy78weY5Z/oKoddA2yINeZzX/qPQLeVDj15RNeHhBBT1jM9in8mrVMR7XYck6muVW9XL97+F5BmUwTyNdmUOTz/C6xvD6cMY5XM4icgnEzPJj1iNY7UeOWC8QEdFWQZw6yTSBxkH6eSIUOxjkHHVCkxPIrTrt8ip/IzoY8E/A8hJ1uyctMP6JGo2p7mad2i5Ks4gkTNKV1ER+Or/rrYL2rCnPb3nJ9kx3bS8LpyIn/iFu6353oO7Crd8+PvGPXUzKPX+6QW7ezau290iGBdPq4UWUr8dw+n5A4vVV4HNdVkPjlh+2Yh02ykNc+sWyfOjZZg+Ny9ti+l5j+ce1i+hMW8jrbvCO+qao5Wb1cm9wH7fdJM3NSOW3BdTVhtafJe/BBdBjOtBWASNaN/5ytAxqPbN83Vq3mN/4feXI9QIREW0exHntJlw7XOkf3uxF4KMOykvtFrRG+J644CXq0vqCFm6KfvNL5koFHu7945N9kC5483pGBRgPI9SCfj7dijNG3TKtNLoFyUHfky66LkqFAqr9S/SmJhgJy9uz5cMKNd3oVh+/RUnPzwwu3qjgRLfIelAxnIo71Bxjl28mEqvWYcM8RKZfKsuZuLwNF9Nn1HzWXpao5V5J+qVlLWw3SRNkIfx5hUxNjqspbMeatyouW7XMkKeoF4iInrsNgzg5gY4xrlv6xGTV/c/HexLNw70L5OZ9P/Tkrv0Wt9mfCpAyGdRG6nOvgeykCcta0QW3QLoSHWQbprVLWqTMGBU9wFbhRF8HFeEgJWr5ZtRWVuRhYxF5O9iNbtvK4MrO73iLwlPXC0REz9OGQZw5OZuT0rSVR741xWi3s+Zqwyouqn53lje8RX18jTIv93ejA6I6bs39Urp7Mneptu6SIF1wMpZ7yy4K8+5UGZ/v4m42PrhXzqRDEbVOD62NbpzPAZafE68/CbW7SrABOBUHmD3oErf8kHXrECkuDxGWyhLTif9vXN6W0uuy158SiJo2arutobtRq0NTTn4AFnthlGiZT1AvEBHRlg82HJPcEwf/njir5OJ6ENE1Rxvy73tC07ScODamkfcySbqBipz8dBdyU92gF2pNWhpfmZh77uQp4pwaLF2JFhxbTZPofC7Tyez8LsiK45rhPunKk4jHnjXDxS0/bN06LFudh8f8snRLZv5N1wRlcXlbTK8Gb9DS50+7frutlqn11DHVNPdKVjCxp4s/ZZK51IGbfrBiT8skIqL94wvwU4xld3q8YRXWXRkPDHRI4TFKRNt4hS/AJzqO+dsPLvQTwPqhCyIiogNjEEe0o9nT0/IXPAFMRER0YAziiIiIiFKIQRwRERFRCkU+2EBERLvjQw1EtI2kDzY8CuJ++acLegQRERERHR+fTiUiIiI6YwziiIiIiFKIQRwRERFRCjGIIyIiIkohBnFEREREKcQgjoiIiCiFGMQRERERpdCj34mrFj5tRhERERHRsXVGb9vux375xgYiIiKip/Oe97v8sV8iIiKiNLu4uDCfHltoifvPf+K38NnXv2RGEREREdGxfeUbv4zv+w/+Nb72a78W3/AN34Bv/MZvxNve9rb47lT5+/znP4/Pfvaz+NM//VP88R//sf5XvstwCfKIiIiI6HCk5e2Nb3wjvvIrv1J3p0o3qgRyEsR99Vd/Nd70pjctBnHyQYK0L3zhC/iLv/gLvPbaazqAk3/luwxnEEdERER0WBLESZAmQZy0vL344os6kHvrW9+Kr/qqr9IBXmQQ96UvfQmvv/66bn37zGc+gz//8z/H5z73OXzxi1/ULXVEREREdFgSpEmLmwRt0qUqLXIS0L35zW/WQVxwn9wsiJN/gtY4CeSCPwngZLiMZyBHREREdDgSoAVdqnL/21ve8hb9JwGcfA/G67RBECckWJM/CdyCv2AYERERER1eOJCTFrngT1roggBOLARx8jH4C7e+yR8RERERHUcQyAWB23IAJxaCuEAwiMEbERER0dMJArflAE5EBnFEREREdNr4xgYiIiKi1AH+f5Xzt8/tONKhAAAAAElFTkSuQmCC

    Thanks for the help.

  • drew.allen

    SSC Guru

    Points: 76739

    You don't need the CASE expression.

    SELECT o.NameRecord,
        ca1.value1,
        ca1.value2,
        ca1.valueType + ' has changed from ' + ca1.value1 + ' to ' + ca1.value2 AS ValuesChanged
    FROM @OldTable o
    JOIN @NewTable n
        ON n.NameRecord = o.NameRecord
    CROSS APPLY
    (
        VALUES
            (COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
            (COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'),
            (COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies')
    ) ca1 (value1, value2, valueType)
    WHERE ca1.value1 <> ca1.value2

    The only difference in your warning message is which value has changed, but it's based on a code that you've hard-coded into your table value constructor.  Instead of hard-coding a code that's later translated into a value, hard-code the value in the first place.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • tshad

    SSCertifiable

    Points: 5839

    I do like that simplification.

    Thanks.

  • tshad

    SSCertifiable

    Points: 5839

    Another question on the above cross apply.

    Using the above Cross Apply where we have 3 rows coming back, is there a way to tell it to only send only one row instead of three based on some criteria.

    For example, if we have blank in the o.OwnerName, we know we have a new record, so we only need the first row and not the dependants or policies rows.  After they come back they are three separate records. We don't want to stop the whole record, as I do want to report that this is a new record.


    CROSS APPLY
    (
    VALUES
    (COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
    (COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'),
    (COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies')
    ) ca1 (value1, value2, valueType)

    Thanks.

  • pietlinden

    SSC Guru

    Points: 62898

    Using the above Cross Apply where we have 3 rows coming back, is there a way to tell it to only send only one row instead of three based on some criteria.
    Use ROW_NUMBER() to number the names that are returned and then filter for  = 1?

    CREATE TABLE #test (
      FirstName VARCHAR(20) NOT NULL,
        Letter CHAR NOT NULL
    );
    GO
    INSERT INTO #test (FirstName, Letter) VALUES ('Al','B'),('Al','D'),('Al','A'),
    ('Bart','X'),('Bart','B'),('Bart','D'),('Homer','D'),('Homer','A');

    SELECT FirstName
        , Letter
    FROM
    (SELECT FirstName
        , Letter
        , ROW_NUMBER() OVER (PARTITION BY FirstName ORDER BY Letter) AS rn
    FROM #test) x
    WHERE rn = 1;

    Oh wait, you're using an old version of SQL Server... Windowing functions were introduced in either 2008R2 or 2012. So you'd have to use a Common Table Expression, I think..

  • tshad

    SSCertifiable

    Points: 5839

    That wasn't really what I meant but I figured out how to do it. 

    I can't stop all three from being sent from the cross apply, but I can use a case statement in the Cross Applyl and then filter on the rows in the where clause.In this case, if the o.OwnerName is blank, I know this is a new record so I set the other valueTypes to a blank so they will get filtered out.


    CROSS APPLY
    (
        VALUES
            (COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
            (COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), CASE WHEN o.OwnerName IS NULL THEN '' ELSE 'Dependants' END),
            (COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), CASE WHEN o.OwnerName IS NULL THEN '' ELSE 'Policies' END)
    ) ca1 (value1, value2, valueType)
    WHERE valueType <> '' AND
       ca1.value1 <> ca1.value2

    This will only show rows that are different and the valueType is not blank.

    Thanks

  • drew.allen

    SSC Guru

    Points: 76739

    tshad - Wednesday, January 30, 2019 6:51 PM

    Another question on the above cross apply.

    Using the above Cross Apply where we have 3 rows coming back, is there a way to tell it to only send only one row instead of three based on some criteria.

    For example, if we have blank in the o.OwnerName, we know we have a new record, so we only need the first row and not the dependants or policies rows.  After they come back they are three separate records. We don't want to stop the whole record, as I do want to report that this is a new record.


    CROSS APPLY
    (
    VALUES
    (COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'),
    (COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'),
    (COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies')
    ) ca1 (value1, value2, valueType)

    Thanks.

    Just add conditions to your WHERE clause.
    AND (o.OwnerName > '' OR ca1.valueType = 'Owner')

    If the conditions are really complex, you can change from a table value constructor to a SELECT/UNION clause.

    CROSS APPLY
    (
        SELECT COALESCE(o.OwnerName, ''), COALESCE(n.OwnerName, ''), 'Owner'
        UNION ALL
        SELECT COALESCE(CAST(o.Dependants AS VARCHAR(10)), ''), COALESCE(CAST(n.Dependants AS VARCHAR(10)), ''), 'Dependants'
        WHERE o.OwnerName > ''
        UNION ALL
        SELECT COALESCE(CAST(o.Policies AS VARCHAR(10)), ''), COALESCE(CAST(n.Policies AS VARCHAR(10)), ''), 'Policies'
        WHERE o.OwnerName > ''
    ) ca1 (value1, value2, valueType)

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • tshad

    SSCertifiable

    Points: 5839

    The union does give flexibility to how the rows are handled.

    I did it like your first suggestion and just changed valueType to allow me to use the where clause to remove two of the rows.

    Thanks.

  • DBA.k

    Ten Centuries

    Points: 1085

    tshad - Tuesday, January 29, 2019 7:59 PM

    That's fine.
    But I don't need a list of names (my mistake).  I need to use another value in the record to go along with the names in each record.  So changing the code to the following:


    DECLARE @TestTable TABLE
    (
     ID INT IDENTITY(1,1),
     NameRecord INT,
     Name1 VARCHAR(20),
     Name2 VARCHAR(20),
     Name3 VARCHAR(20)
    )

    DECLARE @TestTable2 TABLE
    (
     ID int IDENTITY(1,1),
     NameRecord INT,
     SingleName varchar(20)
    )

    INSERT @TestTable VALUES(5, 'Tom', 'Larry', NULL)
    INSERT @TestTable VALUES(7, 'Ron', NULL, NULL)
    INSERT @TestTable VALUES(9, 'Sheryl', 'Mary', 'Mark')

    SELECT * FROM @TestTable

    INSERT @TestTable2
    (
        NameRecord,
        SingleName
    )
    SELECT ???

    The @TestTable looks like:

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAQMAAABQCAYAAAAdvBAmAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAx7SURBVHhe7Z0/iOvYFcY/J02qfQvbLNskS+RXGHVJZVfbxZ4UZgt1wZDCrhabwEAChhBwN42nCdjFggmkUBIwZG2lypLC7rYThowUNs0+0gTe2ypNMjnn6srWeDy2x9Zfz/mBGPlasnSupHPPufb5pvSHP/35HoIgvHhK33zzzf1HH32kXybPmzdvkObxLolL6TuxI3+wLd/R64IgvHDEGQiCoBBnIAiC4mln4N+iVruFH66XSiitlxo6jnonPtQxariNfmz0HOLmkU2hXfr9pInTtrT7LoSO0Sl1EFuXZWCH06mtr3/twYHPIFM7Tr+Hj48MqkN49/e458XrozIox9d5a5botRK+gaNEbaLFm5twBykeP1bS7DsftzW68co9jHVLfKRoh9NBA5a+BzxYdjnGwSBlO1xtBz2baJzmoE9LE4w6upMhYM/iNZYezqFp4yat0XkLo96EuVzB068LRap9Z6C74AdojrZuiY007aiPcD/qkjWMgSurCvcupjs6bTsW2g6jjErVxSlmnD5nYLxO5MG5urZ2j87k/TZhvQ6FVOjVQUeHSLVbB7dqPZLG0DYdHsV4P9p2XzDjO1O41QrK6sUT+/nOpn3PcdQ98OD8qC2yTa1l09gRL1n2XZxkY4ePmb2E+TpwDXGQiR3+DPbSxClmnDGBeLoH2ovRRX+XRyXvtwhDei/SyUsXlesFtVGk0hsAap3f56iFwll66CoTvd+kAjsaui17KK8vSgll2r0/YQ/71H7c3gAsT58Hh2Qtuijc3qOdg3bPcilS0wbw+TUn1H6Nu8g2k74ZvB8nafZdkmRgh3/bQg9DXNd1QxykbId/S86DUjcMr3GKGWc4Aw+rEz3QIeojesi2ParyioHXZIPXo2rVwlV4DtF1RnlJyt3K+oHn/ZY2ZuEHR+cMvDndCi6mHOo8tZ9D7XzDdPVBKF0a3S/QhW6vB+3GlYWqexecP58Tt/NnRrcpV1BVa/GSWt8lTJp28EPUsinnDkPtGEnTDqNLzuOMuY/TnYF/twmpY6eOa8tG62alX1NoXiav2OcRlh/c4TMepDbm4QOvFnp4d11xngeh0Xo8DXtxx37JGBszGfRdIqRjBzuCMjmCSQKOICDt63H63MdpzoDyZg6LzX5SHcherg9zPI7k1RRWl4Oj+bNVpH0PxhWs6hjTMLkij7z36516E+3xFM5T+3E7BZM363a6sJTT3UK369zOn9lYmq8f9k24b3QbtRY/mfRdAiRuB623eibmiTmCgKTtUOkBhQLBO6fPfRzvDKL5NSXXK8p9R3HmV48gjzoMfSavA3Y5OH7LdnX7IQx0J3PeMTjv1gqWmhN4inAe5Kn9ttp5kmE+Ie/M7XSCg6CdRxrvUecE27iNYBv6yETShIAs+i4JkrXDueEQfYwGt+sl/q/LmWTtMLoTzDHQz2cLK+u0Z1MKlQqEFPjkCylUEgThIhFnIAiCQpyBIAgKcQaCIChKb9++vX///fdBf5HWX0EQ8sWrV68CZ/Cf/6YXIHz4wXvqBxPC83n37p26aEXnsux4p18Vl3fvXqnrobwAP6BpIZGBIOQT5Qz+9e9v1Ys04FRBEIT8IZGBIAiK4yODr3+L6w+6+Kt+eSqHIwP+vf/m56Fcz53IL0QvEKcT7bfIckoJW5ZkUAeRCP7nqJU+fXj/clvtc23bl3Sv/4ru+CiRtgfbRniq/UyOiAz+gfFP3sOHP/4lfqdbzuG4yCBanZVmpVyxqY/CPmMFokgfJltEIuzlK/Ra8T+4SXBEZPBDtP/yLW3zR/xMt5zDwcgg0dJoQUiZ6q8xNL94LHCSQ/I5Z7C00QrlwSRHiAcKvffLsj0htZUnjpALY9vW65H0iFOorLKlq+ufwh3kPzo4fs4gJo76NqFqoc/yTt4cph2nYu1L5YAs25NSWzljn1wYS8uxUEh0nbUp1I4OpuM2mlllS8bP0S9AdJC/yMDoYrHoQqmDGXVcx6lY+1LZllzblmUL52S2pbbyhopu9Ki/LRembVuvs/5gewwlXOVMMW43keXMSX30GZDz6CCfkYEgPOL5cmH1ZlvJ2DlTF8NYlU5P4RMa2L5A6yaqJ/4xKtW/PxQV9r+GWy1nMmeWu8jggYST7+CGolsr18NVAdglubYty1YInikXxjJ2bkP9g5E83EJG9zOY499HzvsHeG1+hZX3T/2a7PI8ujYfZ3JtchcZPJBwYlmxIcuK6TeFEzlGli1nbMnYlzo4QS6sjib5j6p1lRPHR9HB8Ed6PaA++hsq01+gVvtULa1pma7NJ/pdYvkb6ofvk8166Xy5v/0M1lWLaTmE7333fxdRpJIFUqj0XCi1qE3RXIwSmS+4yEIlmTMQLg6nQyNmA6512j8UeYnk79sEQYiD+khNNC4kxzwaiQwEQVBIZCAIgkJNIPIKj9j8oKb1VxCE/MATiMoZpDlDfSkz4llwWd8mXMos/IV9myAIgiDOQBAEhTgDQRAURzkDJ6wPpyVpfQFVmxD+BHW96Pp7YT9c2/+o73Tdf5EomFzYkxTMjsPOwOmgAQueqiP3YCWsL2B0F0FVWrhwdVrG5aeFojrU1ypYvLm5qfsvFMWRC9tPwWTP9sK/5BqF/5ffwFXK+gLOjQ0r8/LT4mLUmzCXK0QLZwtBgeTC9lI02bPj8TGzlzBfp/QTTwp7BzkpPy0qvjPdaEoqcRCdPmxJnwVyZ/lKK4oiF3aIQsmeHYt/20JPKebohoThunuzH0YlwlFslf5yFXh/wn24X/psxVJhnFrkKa0oiFzYQYoke3YMPLHX4jr4RVoPp4Mb28xOt66oROcMvDm5bhdTzhEOSJ+F7ShXgr85oQhyYcdQGNmzQ7AjYEGMSWqOgI85kPLTczHq6PZNJf1VXPIvF3YcBZE92wvllK2eiXmKjoAOCsoQRO4sDlj6i1WCCyx9lne5sGMphOzZPpwbVqEdoxHJQxP/XwY+eU+rL3JnsVCm0cel0aeA0mdr8i0XdjwFkD2TQqViIIVK+UIKlQRBuEjEGQiCoBBnIAiCQpyBIAiKteyZIAgvF55AlG8TCsRlfZsgduSF0A5JEwRBUIgzEARBIc5AEATFUc5gI3uWTq172se7KKLSZ7Uabrn/lGZBjGXJcX/eNqENDy6+j1vWYkjyuHEThx1J93WEw86AZc9cLXvm9YGGFsVIirSPd1E46JRtWJ4uYZ5YsIvaf9XqpsSa4RLsTYVPcSiQHYedAcuehRWLRlj0ot5JBP/O3fw/faOO66GLQlfgpol/B7caUYYyuljcJ/PvyJPHgmXamIX3mrcC2m1U9cviUBw7njdnoLyaiSRVz4zXJpb2THtSH7PVMlXNxUITlilTbuA/6rIVZrvSLwpDo1JoqiBVhaYddMLtI2Gu0ymhM9MvEuaqacLWT5EzdWE1I8IrFEFulKC1PYfOO6NB5Sw7ylw1HEKRH6V+HV2GHjdHOwMlYU4nhmHCgiMUiczJkyrprtoN3cLFGwuyg8uU56isBiiXg5trXW2+U9qMpdBsVCZhWlGB3dL5KW1f4e1ZnZr1ENSHOJiO22heqRfJw6pLamCg47IWZlTxg+6TBZ+zSietjVTbvvPOKkQ61w5+7bMjGNC1WmAUqlLFzNHOIJAwT14qnamPtFz6YoRmxC8KR8DqRrr/WCa9Fz7cu6TNVKS3RE85Dlp4FFpSSMtiPJxu8PaUavTb4yBVc6YYpylbryKdFTwWdd0WYlERjY4AoqNnHs57m3PsYOiatFoNjM1kNT6elyaQGelKpfu4c5GeGvOFwTLp7YMy6W3Mw5FJLQt0tzS36s22kk7jEHeYqmw9328uBgMX5oNhPZgorfR1pBOOnltkd97bnGcHPQHoTzwM3UaiA/FBZ6DSAzqD4PFPQSqdcqhSJxjNfOcGvWWG4V3BCK6VjgT4NY+IoUz6LnjEqtLoGeYSKlfd7L+GnQrdiPwtT9pKdCzciuUuYVyTIpzgZPzZanf8mOF5b3OWHXwNjUCpyk3w26GDzsDoTjAH5aBqgqOFleVRzqLfTAKeM0AwZ1BuuGjPizobnj7BtdLzLdx/A2CuZNKfIphjAKV+KkxtrWDt3L6OJt2z62950mTnNyL8LROfdmBny6bwcScZnvc2Z9mhoc+YDF00EvrdgRQqFYjs+o4nr6ZoLuJxzOnZEe95byOFSsLLgtO2UqN4svVFPe8MkcigQFzaSFR0JDIQBOEiEWcgCIJCZM8E4YUTpjqle/61gyAILx5JEwRBUIgzEASBAP4PualOv0fw53UAAAAASUVORK5CYII=

    The results should look something like:

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMEAAACPCAYAAABDEL+AAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAuySURBVHhe7Z2xcuI8FIUv/7NAiixPYJ4A0qSiTWfK0KSjpEtDSpjZIm2qzOwsPEF4AjZFoN7HWH5Jlm3JGAMmccy955vRJNiY2EJH0nV0fBt///7dkmI+n1Ov19O/gm8G30U1xPXc+Pnz5/bfv3+kCwASSUaCX79+URiGZiP4Xv78+UM/fvywr8BXEdfzf/Y1AGKBCIB4IAIgHogAiAciqILFgBqdJ9rYl3VlMWhQY7Cwr+RQSgSmshoN6jz5X+vmqfNNlbihp050TmkZEJuvU4vIuTa3is138U0Cq187KEfpkSAIAloOH2vV0ILJmrbbrSnzcEY9Dr3a5ok6vRmF8+i6tts50Tht9N2p2vZ2T037umrq2A5Opfx0qD+iSXCgoekvMLcH0z13h542Cxok+3XP7fboer99uyHb2xf39N3bkGj14fSQB44vPNd9x+nzV+f5FPfUdp/3WWr/h3nzGQR03bK/UpemTqP3pzDRuQ4Wbr1m6jEzqpiy9zs8os5r3g6O4YyYoEX3zxMKZuPMScaoC7sjerY983oS0MzpwYiWNGy90q3Zv44qstGi91H8fqLhXfr+xaBFw/bc9oZ6/4p6e6cBqqLGM6L2ldNYio5X59oaEiUjSdzb6grPOc6reHUd6tC52T9VTVQdc+d+1ojeh+pcytK8oX6g6yrbGPYz643peh2d7zxUxz7GZ6uus7dSpxZfS0BqiKHttGv3+xxX53VuB0ei/2Osy3Q6VZ95HPOQtmrqkfyu5iFb/Upd4FaP27msJ9uAwm20d71VdbF137pz7Dzcqm8oer85NtjaP2mZb8NkW/R56nKSEp+f4dDx7t9y8c45xv27+nf/OnKP0Z9v6+gYVquV/S3F1LO5Nv86zPbkBHbr1fvb2XPLnJf3WQfrPHp/vdrBacT1fPbdoe50TuFySHc53YAJkOJhS/W0S7u9HLrHcIfBHmX71yQmUN/I7jz1wPHBterTMqzfc865RdeqZ35f25dZco85HzP3j3vGE0YFj+YVtWlFH/bYxas/Wu5yuM5j6tQOTuVsEZg5qm10v+0Wg5p7toZtO01QRX17avA9gzD9rKS80X3eN9h9MMPq2PtCDhy/fKeddt26zjnnNb0v3Tl6htxjPo/m/bO6tgIRFrH5UBJIG1Fvpupkz1Qo4oQ6r2M7OJJPEIHCNrrhcL/GF49n9ABmXpxt1EU06X7kjAaHju/eqqp196t5rJ5n2uPcoG/zNKZZ0KebfZVuelvns+zdnbLoXtS7Bbl4pGGRCIvQo5SOAZLGo2OYPZxc54ratYPj+BwR6EangyP7ytCdRrcp7bA1vp6ohlYW9flva+q/tJxhUJWigMg27NdIBQeOV71YNM+w+1Rg+azvwOjj1DA/6yXHtF76tC68JZn5rNY7jc7o/XTP7523CWxL9nz6O6H0WkzZW4cl6lwfU7d2cAxlAmPwteQFxp+BCTozAbob3Erj0wJjcDms39VExAuEN/Sxsr8KBiIQRHe6psnKnQ616KW/prdzokoGQASi0HNq967KVrwANJ7HuNPp2M0AyAEe4xoCj3E1wGMMgAUiAOKBCIB4IAIgHogAiIeJCLJuI1WKnE4Xw8JxXGXL+Y4qEMFqJAgTH64qhUuEL4UuTePrMYvwgsQVVrgCFJwEpkMXT5HnVu871cMrD1YimPWkfbG6MR/hgT7BwysRJiLw18REy/kFzJk3v+ll6bvDmvcjCh0LpSacx1OnJt30A72B4kOaV+18V50gWE6HooYQG2oYU8YDDXbgGRMYL21JC+IlUcYDDXZgIYLFwI8BjI+1yAfMhTIeaLADCxF0b9vJExR06a0mB3zAXNCx0KkeaJAFS6lrCJZSVwOWUgNggQiAeCACIB54jIF4EBjXEATG1YDAGAALRADEAxEA8UAEQDwQARAPExFw9Rhromtjczk1hNVIwM9jDKoA06FLZm9+YO0p7mTyK+9uG3g5kDXRqJPNUM8dViKQ5TFWjfpQfmAvv/LutqlOeD57TW2oxq4ZUF+YGYGJCCR6jLteZvvmTZ+CjFc49RaneNu8vG5aAy+0DEdnZYK8RFhOh6R4jM/PD9ylBz2CmIra0O+XJYW38mIpnjGBBI/xJ+UHNiOInhLZJ1cI1AAPEYj1GDuUzg8c+5T1SBLe7kyfJMBCBBI8xmnQr4sSfeuz8gPbZxEpJE6FNFhKXUMqX0qtplaNnr5rJOv5plhKDRIWrzM9DIicCmkgAvEsKNKAVAlABED1//rx75JXmcBjDMSDwLiGwGNcDQiMAbBABEA8EAEQD0QAxAMRAPHwEIH+t3+yrsYtHDwFOf5pFtdVH3iIoDtNDDVxmeu7vYyWAgSTtXNtfnYacB48p0ObJxrPApo88Pw3aFfbIlcfjpUyO1q4I0X8tAo3l7EE++nxsBSBWVvP1iaoGvV4RtS+skvFdSM/lMtYL8Ue07XNhj8PlzR8xEgSw1AEPBeELYct24u36KWvpkbxYp+jcxm/JZ3C7kgiG3YiiLI3TojbTCiJCVSwsxw+pr08chmfDTMRLOhxuKRwxDh7Y/eBJsGMxvGkHrmMz4aVCMwooCYCvJfGN+l+5IwGyGV8NoxEEI0CweSBv0PKe14QchmfC5ZS1xAspa4GLKUGwAIRAPFABEA88BgD8SAwriEIjKsBgTEAFogAiAciAOKBCIB4IIKLJjbM2JcVsNhJ9nf58BGB5zNm5pzKeKjhrPxceIjAPF9/RRPrnNrOddIOJmZ0naa1N3NyNM+JvCyV4FxYiGDzsSJylw6bVZa+s+qycb0BftbKiCL/cJH/GLmNNSxE0LxqE2XSl+qcvSycVcYvsFQj2/4pXpF/eDHI8R939uc7lpjbmMd0qOvn72o0xrQqk8qxlkQ5mk3jNnnZdsWw1z+c89QN4z9evtBv5zOk5zZmExh3p/GcWZcRtUulcqwv8fVFicpPCfxj8cSlp5p4EfJyG7MRgcfila3Nsnn/TJOTTPRhmus4KenIkYe03MYMRaCCvd6Mjc1SZ633gtLFIw2PNdFb/3Fiyj8WYbmNeYhA30Z0hvvVZE1vTCaxuufvv8TPHFLF3Aou7slTdDyx9o/XxQuM85CV2xhLqWtILZZSC8htjKXUoBBJuY0hApCDrNzGEAHIQVZuY3iMgXgQGNcQeIyrAYExABaIAIgHIgDigQiAeCACIB4+ImDrMU6dYbne4vi6D64HAvvgIQLOHmNLEAQ0y/EW6+UNuLV9HixEcCke43Sk8ssxtEe7jrDIORbS7a19HeOtqnVHEHiK82AhgkvyGPvmFvN/yiOF0KXbcEkvjgoi62N2kZtq6HdEz/bz19ol5o0g8BRn4TEdYu0xTtH+4TR9q87RRjlZ+/2nURiXWKaDkO4pzsImMObuMTa4DVZbSPdkqNRutHgq1NDuMLs9H3me4ixsROBRE49x0hBtKeK496UNVgfEQf8m8/whxWJArWE79RWvJzl5jn2keYqzMBRBvTzG6egUlSx5+9ttFePsIWqwPeqpgHh0xJxloeZMBwdFYZ7iLDxEwNhjvINpsOrnvsaaiY/G1xM1Jh5Clqc4C5ZSfyG6Eeb1/kXE06FTjzsbAZ7iLFhKDTwkeYqzQARfTDRFS0uWQ/urQZanOAtE8IVUPqUpjSxPcRZ4jCtE3/XJCiPu/VerlfkJqgeBcYXoBr9PBO52eIyrAYHxNxHP/eMCvh+IoEIuJ0aQBURQMVoIeQV8HxABEA9EIJ7qcyHXDT4igMfYMc6AU+AhAniM7StQBhYi4J/H+Os8xjsdhRlZOI2kh2EhAtZ5jBO+xmPsHZuMqMemg+IBj+kQPMYOJ3qMYz7U6CFQABo2gTE8ximneYwjZmp44Fhlx8BGBB5s8xh/jcdYE85tovA7eXeZGIqgXh7jz+ZLPMYWkyichtQS9k8DHiKAxzillMc4Ruc9nlOoRCbpCXRYSl1DsJS6GrCUGgALRADEAxEA8cBjDMSDwLiGIDCuhqief9D/9cC0luhFHkYAAAAASUVORK5CYII=

    I could do this with a cursor but would prefer not to.

    Thanks,

    Tom

  • DBA.k

    Ten Centuries

    Points: 1085

    tshad - Tuesday, January 29, 2019 7:59 PM

    That's fine.
    But I don't need a list of names (my mistake).  I need to use another value in the record to go along with the names in each record.  So changing the code to the following:


    DECLARE @TestTable TABLE
    (
     ID INT IDENTITY(1,1),
     NameRecord INT,
     Name1 VARCHAR(20),
     Name2 VARCHAR(20),
     Name3 VARCHAR(20)
    )

    DECLARE @TestTable2 TABLE
    (
     ID int IDENTITY(1,1),
     NameRecord INT,
     SingleName varchar(20)
    )

    INSERT @TestTable VALUES(5, 'Tom', 'Larry', NULL)
    INSERT @TestTable VALUES(7, 'Ron', NULL, NULL)
    INSERT @TestTable VALUES(9, 'Sheryl', 'Mary', 'Mark')

    SELECT * FROM @TestTable

    INSERT @TestTable2
    (
        NameRecord,
        SingleName
    )
    SELECT ???

    The @TestTable looks like:

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAQMAAABQCAYAAAAdvBAmAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAx7SURBVHhe7Z0/iOvYFcY/J02qfQvbLNskS+RXGHVJZVfbxZ4UZgt1wZDCrhabwEAChhBwN42nCdjFggmkUBIwZG2lypLC7rYThowUNs0+0gTe2ypNMjnn6srWeDy2x9Zfz/mBGPlasnSupHPPufb5pvSHP/35HoIgvHhK33zzzf1HH32kXybPmzdvkObxLolL6TuxI3+wLd/R64IgvHDEGQiCoBBnIAiC4mln4N+iVruFH66XSiitlxo6jnonPtQxariNfmz0HOLmkU2hXfr9pInTtrT7LoSO0Sl1EFuXZWCH06mtr3/twYHPIFM7Tr+Hj48MqkN49/e458XrozIox9d5a5botRK+gaNEbaLFm5twBykeP1bS7DsftzW68co9jHVLfKRoh9NBA5a+BzxYdjnGwSBlO1xtBz2baJzmoE9LE4w6upMhYM/iNZYezqFp4yat0XkLo96EuVzB068LRap9Z6C74AdojrZuiY007aiPcD/qkjWMgSurCvcupjs6bTsW2g6jjErVxSlmnD5nYLxO5MG5urZ2j87k/TZhvQ6FVOjVQUeHSLVbB7dqPZLG0DYdHsV4P9p2XzDjO1O41QrK6sUT+/nOpn3PcdQ98OD8qC2yTa1l09gRL1n2XZxkY4ePmb2E+TpwDXGQiR3+DPbSxClmnDGBeLoH2ovRRX+XRyXvtwhDei/SyUsXlesFtVGk0hsAap3f56iFwll66CoTvd+kAjsaui17KK8vSgll2r0/YQ/71H7c3gAsT58Hh2Qtuijc3qOdg3bPcilS0wbw+TUn1H6Nu8g2k74ZvB8nafZdkmRgh3/bQg9DXNd1QxykbId/S86DUjcMr3GKGWc4Aw+rEz3QIeojesi2ParyioHXZIPXo2rVwlV4DtF1RnlJyt3K+oHn/ZY2ZuEHR+cMvDndCi6mHOo8tZ9D7XzDdPVBKF0a3S/QhW6vB+3GlYWqexecP58Tt/NnRrcpV1BVa/GSWt8lTJp28EPUsinnDkPtGEnTDqNLzuOMuY/TnYF/twmpY6eOa8tG62alX1NoXiav2OcRlh/c4TMepDbm4QOvFnp4d11xngeh0Xo8DXtxx37JGBszGfRdIqRjBzuCMjmCSQKOICDt63H63MdpzoDyZg6LzX5SHcherg9zPI7k1RRWl4Oj+bNVpH0PxhWs6hjTMLkij7z36516E+3xFM5T+3E7BZM363a6sJTT3UK369zOn9lYmq8f9k24b3QbtRY/mfRdAiRuB623eibmiTmCgKTtUOkBhQLBO6fPfRzvDKL5NSXXK8p9R3HmV48gjzoMfSavA3Y5OH7LdnX7IQx0J3PeMTjv1gqWmhN4inAe5Kn9ttp5kmE+Ie/M7XSCg6CdRxrvUecE27iNYBv6yETShIAs+i4JkrXDueEQfYwGt+sl/q/LmWTtMLoTzDHQz2cLK+u0Z1MKlQqEFPjkCylUEgThIhFnIAiCQpyBIAgKcQaCIChKb9++vX///fdBf5HWX0EQ8sWrV68CZ/Cf/6YXIHz4wXvqBxPC83n37p26aEXnsux4p18Vl3fvXqnrobwAP6BpIZGBIOQT5Qz+9e9v1Ys04FRBEIT8IZGBIAiK4yODr3+L6w+6+Kt+eSqHIwP+vf/m56Fcz53IL0QvEKcT7bfIckoJW5ZkUAeRCP7nqJU+fXj/clvtc23bl3Sv/4ru+CiRtgfbRniq/UyOiAz+gfFP3sOHP/4lfqdbzuG4yCBanZVmpVyxqY/CPmMFokgfJltEIuzlK/Ra8T+4SXBEZPBDtP/yLW3zR/xMt5zDwcgg0dJoQUiZ6q8xNL94LHCSQ/I5Z7C00QrlwSRHiAcKvffLsj0htZUnjpALY9vW65H0iFOorLKlq+ufwh3kPzo4fs4gJo76NqFqoc/yTt4cph2nYu1L5YAs25NSWzljn1wYS8uxUEh0nbUp1I4OpuM2mlllS8bP0S9AdJC/yMDoYrHoQqmDGXVcx6lY+1LZllzblmUL52S2pbbyhopu9Ki/LRembVuvs/5gewwlXOVMMW43keXMSX30GZDz6CCfkYEgPOL5cmH1ZlvJ2DlTF8NYlU5P4RMa2L5A6yaqJ/4xKtW/PxQV9r+GWy1nMmeWu8jggYST7+CGolsr18NVAdglubYty1YInikXxjJ2bkP9g5E83EJG9zOY499HzvsHeG1+hZX3T/2a7PI8ujYfZ3JtchcZPJBwYlmxIcuK6TeFEzlGli1nbMnYlzo4QS6sjib5j6p1lRPHR9HB8Ed6PaA++hsq01+gVvtULa1pma7NJ/pdYvkb6ofvk8166Xy5v/0M1lWLaTmE7333fxdRpJIFUqj0XCi1qE3RXIwSmS+4yEIlmTMQLg6nQyNmA6512j8UeYnk79sEQYiD+khNNC4kxzwaiQwEQVBIZCAIgkJNIPIKj9j8oKb1VxCE/MATiMoZpDlDfSkz4llwWd8mXMos/IV9myAIgiDOQBAEhTgDQRAURzkDJ6wPpyVpfQFVmxD+BHW96Pp7YT9c2/+o73Tdf5EomFzYkxTMjsPOwOmgAQueqiP3YCWsL2B0F0FVWrhwdVrG5aeFojrU1ypYvLm5qfsvFMWRC9tPwWTP9sK/5BqF/5ffwFXK+gLOjQ0r8/LT4mLUmzCXK0QLZwtBgeTC9lI02bPj8TGzlzBfp/QTTwp7BzkpPy0qvjPdaEoqcRCdPmxJnwVyZ/lKK4oiF3aIQsmeHYt/20JPKebohoThunuzH0YlwlFslf5yFXh/wn24X/psxVJhnFrkKa0oiFzYQYoke3YMPLHX4jr4RVoPp4Mb28xOt66oROcMvDm5bhdTzhEOSJ+F7ShXgr85oQhyYcdQGNmzQ7AjYEGMSWqOgI85kPLTczHq6PZNJf1VXPIvF3YcBZE92wvllK2eiXmKjoAOCsoQRO4sDlj6i1WCCyx9lne5sGMphOzZPpwbVqEdoxHJQxP/XwY+eU+rL3JnsVCm0cel0aeA0mdr8i0XdjwFkD2TQqViIIVK+UIKlQRBuEjEGQiCoBBnIAiCQpyBIAiKteyZIAgvF55AlG8TCsRlfZsgduSF0A5JEwRBUIgzEARBIc5AEATFUc5gI3uWTq172se7KKLSZ7Uabrn/lGZBjGXJcX/eNqENDy6+j1vWYkjyuHEThx1J93WEw86AZc9cLXvm9YGGFsVIirSPd1E46JRtWJ4uYZ5YsIvaf9XqpsSa4RLsTYVPcSiQHYedAcuehRWLRlj0ot5JBP/O3fw/faOO66GLQlfgpol/B7caUYYyuljcJ/PvyJPHgmXamIX3mrcC2m1U9cviUBw7njdnoLyaiSRVz4zXJpb2THtSH7PVMlXNxUITlilTbuA/6rIVZrvSLwpDo1JoqiBVhaYddMLtI2Gu0ymhM9MvEuaqacLWT5EzdWE1I8IrFEFulKC1PYfOO6NB5Sw7ylw1HEKRH6V+HV2GHjdHOwMlYU4nhmHCgiMUiczJkyrprtoN3cLFGwuyg8uU56isBiiXg5trXW2+U9qMpdBsVCZhWlGB3dL5KW1f4e1ZnZr1ENSHOJiO22heqRfJw6pLamCg47IWZlTxg+6TBZ+zSietjVTbvvPOKkQ61w5+7bMjGNC1WmAUqlLFzNHOIJAwT14qnamPtFz6YoRmxC8KR8DqRrr/WCa9Fz7cu6TNVKS3RE85Dlp4FFpSSMtiPJxu8PaUavTb4yBVc6YYpylbryKdFTwWdd0WYlERjY4AoqNnHs57m3PsYOiatFoNjM1kNT6elyaQGelKpfu4c5GeGvOFwTLp7YMy6W3Mw5FJLQt0tzS36s22kk7jEHeYqmw9328uBgMX5oNhPZgorfR1pBOOnltkd97bnGcHPQHoTzwM3UaiA/FBZ6DSAzqD4PFPQSqdcqhSJxjNfOcGvWWG4V3BCK6VjgT4NY+IoUz6LnjEqtLoGeYSKlfd7L+GnQrdiPwtT9pKdCzciuUuYVyTIpzgZPzZanf8mOF5b3OWHXwNjUCpyk3w26GDzsDoTjAH5aBqgqOFleVRzqLfTAKeM0AwZ1BuuGjPizobnj7BtdLzLdx/A2CuZNKfIphjAKV+KkxtrWDt3L6OJt2z62950mTnNyL8LROfdmBny6bwcScZnvc2Z9mhoc+YDF00EvrdgRQqFYjs+o4nr6ZoLuJxzOnZEe95byOFSsLLgtO2UqN4svVFPe8MkcigQFzaSFR0JDIQBOEiEWcgCIJCZM8E4YUTpjqle/61gyAILx5JEwRBUIgzEASBAP4PualOv0fw53UAAAAASUVORK5CYII=

    The results should look something like:

    data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAMEAAACPCAYAAABDEL+AAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJcEhZcwAADsQAAA7EAZUrDhsAAAuySURBVHhe7Z2xcuI8FIUv/7NAiixPYJ4A0qSiTWfK0KSjpEtDSpjZIm2qzOwsPEF4AjZFoN7HWH5Jlm3JGAMmccy955vRJNiY2EJH0nV0fBt///7dkmI+n1Ov19O/gm8G30U1xPXc+Pnz5/bfv3+kCwASSUaCX79+URiGZiP4Xv78+UM/fvywr8BXEdfzf/Y1AGKBCIB4IAIgHogAiAciqILFgBqdJ9rYl3VlMWhQY7Cwr+RQSgSmshoN6jz5X+vmqfNNlbihp050TmkZEJuvU4vIuTa3is138U0Cq187KEfpkSAIAloOH2vV0ILJmrbbrSnzcEY9Dr3a5ok6vRmF8+i6tts50Tht9N2p2vZ2T037umrq2A5Opfx0qD+iSXCgoekvMLcH0z13h542Cxok+3XP7fboer99uyHb2xf39N3bkGj14fSQB44vPNd9x+nzV+f5FPfUdp/3WWr/h3nzGQR03bK/UpemTqP3pzDRuQ4Wbr1m6jEzqpiy9zs8os5r3g6O4YyYoEX3zxMKZuPMScaoC7sjerY983oS0MzpwYiWNGy90q3Zv44qstGi91H8fqLhXfr+xaBFw/bc9oZ6/4p6e6cBqqLGM6L2ldNYio5X59oaEiUjSdzb6grPOc6reHUd6tC52T9VTVQdc+d+1ojeh+pcytK8oX6g6yrbGPYz643peh2d7zxUxz7GZ6uus7dSpxZfS0BqiKHttGv3+xxX53VuB0ei/2Osy3Q6VZ95HPOQtmrqkfyu5iFb/Upd4FaP27msJ9uAwm20d71VdbF137pz7Dzcqm8oer85NtjaP2mZb8NkW/R56nKSEp+f4dDx7t9y8c45xv27+nf/OnKP0Z9v6+gYVquV/S3F1LO5Nv86zPbkBHbr1fvb2XPLnJf3WQfrPHp/vdrBacT1fPbdoe50TuFySHc53YAJkOJhS/W0S7u9HLrHcIfBHmX71yQmUN/I7jz1wPHBterTMqzfc865RdeqZ35f25dZco85HzP3j3vGE0YFj+YVtWlFH/bYxas/Wu5yuM5j6tQOTuVsEZg5qm10v+0Wg5p7toZtO01QRX17avA9gzD9rKS80X3eN9h9MMPq2PtCDhy/fKeddt26zjnnNb0v3Tl6htxjPo/m/bO6tgIRFrH5UBJIG1Fvpupkz1Qo4oQ6r2M7OJJPEIHCNrrhcL/GF49n9ABmXpxt1EU06X7kjAaHju/eqqp196t5rJ5n2uPcoG/zNKZZ0KebfZVuelvns+zdnbLoXtS7Bbl4pGGRCIvQo5SOAZLGo2OYPZxc54ratYPj+BwR6EangyP7ytCdRrcp7bA1vp6ohlYW9flva+q/tJxhUJWigMg27NdIBQeOV71YNM+w+1Rg+azvwOjj1DA/6yXHtF76tC68JZn5rNY7jc7o/XTP7523CWxL9nz6O6H0WkzZW4cl6lwfU7d2cAxlAmPwteQFxp+BCTozAbob3Erj0wJjcDms39VExAuEN/Sxsr8KBiIQRHe6psnKnQ616KW/prdzokoGQASi0HNq967KVrwANJ7HuNPp2M0AyAEe4xoCj3E1wGMMgAUiAOKBCIB4IAIgHogAiIeJCLJuI1WKnE4Xw8JxXGXL+Y4qEMFqJAgTH64qhUuEL4UuTePrMYvwgsQVVrgCFJwEpkMXT5HnVu871cMrD1YimPWkfbG6MR/hgT7BwysRJiLw18REy/kFzJk3v+ll6bvDmvcjCh0LpSacx1OnJt30A72B4kOaV+18V50gWE6HooYQG2oYU8YDDXbgGRMYL21JC+IlUcYDDXZgIYLFwI8BjI+1yAfMhTIeaLADCxF0b9vJExR06a0mB3zAXNCx0KkeaJAFS6lrCJZSVwOWUgNggQiAeCACIB54jIF4EBjXEATG1YDAGAALRADEAxEA8UAEQDwQARAPExFw9Rhromtjczk1hNVIwM9jDKoA06FLZm9+YO0p7mTyK+9uG3g5kDXRqJPNUM8dViKQ5TFWjfpQfmAvv/LutqlOeD57TW2oxq4ZUF+YGYGJCCR6jLteZvvmTZ+CjFc49RaneNu8vG5aAy+0DEdnZYK8RFhOh6R4jM/PD9ylBz2CmIra0O+XJYW38mIpnjGBBI/xJ+UHNiOInhLZJ1cI1AAPEYj1GDuUzg8c+5T1SBLe7kyfJMBCBBI8xmnQr4sSfeuz8gPbZxEpJE6FNFhKXUMqX0qtplaNnr5rJOv5plhKDRIWrzM9DIicCmkgAvEsKNKAVAlABED1//rx75JXmcBjDMSDwLiGwGNcDQiMAbBABEA8EAEQD0QAxAMRAPHwEIH+t3+yrsYtHDwFOf5pFtdVH3iIoDtNDDVxmeu7vYyWAgSTtXNtfnYacB48p0ObJxrPApo88Pw3aFfbIlcfjpUyO1q4I0X8tAo3l7EE++nxsBSBWVvP1iaoGvV4RtS+skvFdSM/lMtYL8Ue07XNhj8PlzR8xEgSw1AEPBeELYct24u36KWvpkbxYp+jcxm/JZ3C7kgiG3YiiLI3TojbTCiJCVSwsxw+pr08chmfDTMRLOhxuKRwxDh7Y/eBJsGMxvGkHrmMz4aVCMwooCYCvJfGN+l+5IwGyGV8NoxEEI0CweSBv0PKe14QchmfC5ZS1xAspa4GLKUGwAIRAPFABEA88BgD8SAwriEIjKsBgTEAFogAiAciAOKBCIB4IIKLJjbM2JcVsNhJ9nf58BGB5zNm5pzKeKjhrPxceIjAPF9/RRPrnNrOddIOJmZ0naa1N3NyNM+JvCyV4FxYiGDzsSJylw6bVZa+s+qycb0BftbKiCL/cJH/GLmNNSxE0LxqE2XSl+qcvSycVcYvsFQj2/4pXpF/eDHI8R939uc7lpjbmMd0qOvn72o0xrQqk8qxlkQ5mk3jNnnZdsWw1z+c89QN4z9evtBv5zOk5zZmExh3p/GcWZcRtUulcqwv8fVFicpPCfxj8cSlp5p4EfJyG7MRgcfila3Nsnn/TJOTTPRhmus4KenIkYe03MYMRaCCvd6Mjc1SZ633gtLFIw2PNdFb/3Fiyj8WYbmNeYhA30Z0hvvVZE1vTCaxuufvv8TPHFLF3Aou7slTdDyx9o/XxQuM85CV2xhLqWtILZZSC8htjKXUoBBJuY0hApCDrNzGEAHIQVZuY3iMgXgQGNcQeIyrAYExABaIAIgHIgDigQiAeCACIB4+ImDrMU6dYbne4vi6D64HAvvgIQLOHmNLEAQ0y/EW6+UNuLV9HixEcCke43Sk8ssxtEe7jrDIORbS7a19HeOtqnVHEHiK82AhgkvyGPvmFvN/yiOF0KXbcEkvjgoi62N2kZtq6HdEz/bz19ol5o0g8BRn4TEdYu0xTtH+4TR9q87RRjlZ+/2nURiXWKaDkO4pzsImMObuMTa4DVZbSPdkqNRutHgq1NDuMLs9H3me4ixsROBRE49x0hBtKeK496UNVgfEQf8m8/whxWJArWE79RWvJzl5jn2keYqzMBRBvTzG6egUlSx5+9ttFePsIWqwPeqpgHh0xJxloeZMBwdFYZ7iLDxEwNhjvINpsOrnvsaaiY/G1xM1Jh5Clqc4C5ZSfyG6Eeb1/kXE06FTjzsbAZ7iLFhKDTwkeYqzQARfTDRFS0uWQ/urQZanOAtE8IVUPqUpjSxPcRZ4jCtE3/XJCiPu/VerlfkJqgeBcYXoBr9PBO52eIyrAYHxNxHP/eMCvh+IoEIuJ0aQBURQMVoIeQV8HxABEA9EIJ7qcyHXDT4igMfYMc6AU+AhAniM7StQBhYi4J/H+Os8xjsdhRlZOI2kh2EhAtZ5jBO+xmPsHZuMqMemg+IBj+kQPMYOJ3qMYz7U6CFQABo2gTE8ximneYwjZmp44Fhlx8BGBB5s8xh/jcdYE85tovA7eXeZGIqgXh7jz+ZLPMYWkyichtQS9k8DHiKAxzillMc4Ruc9nlOoRCbpCXRYSl1DsJS6GrCUGgALRADEAxEA8cBjDMSDwLiGIDCuhqief9D/9cC0luhFHkYAAAAASUVORK5CYII=

    I could do this with a cursor but would prefer not to.

    Thanks,

    Tom

    Just Simple use UNPIVOT

    SELECT u.Namerecord, u.CNAME, u.NAME

                FROM [ANT_STAGE_MISC].[dbo].[FARMFILE] s
                unpivot
                (
                 NAME
                 for CNAME in (NAME1,NAME2,NAME3)
                ) u

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply