Home Forums SQL Server 2008 T-SQL (SS2K8) how to show single column value (separated by ;) as multiple columns otherwise null RE: how to show single column value (separated by ;) as multiple columns otherwise null

  • Alright, revised it!

    SELECT

    ID,

    PARSENAME(REPLACE([Subject],';','.'),1) AS Split1,

    PARSENAME(REPLACE([Subject],';','.'),2) AS Split2,

    PARSENAME(REPLACE([Subject],';','.'),3) AS Split3

    INTO #SplitTable

    FROM YourTable

    SELECT ID,

    CASE WHEN Split2 IS NULL AND Split3 IS NULL THEN Split1

    WHEN Split3 IS NULL THEN Split2

    ELSE Split3 END AS Subject1,

    CASE WHEN Split2 IS NULL AND Split3 IS NULL THEN NULL

    WHEN Split3 IS NULL THEN Split1

    ELSE Split2 END AS Subject2,

    CASE WHEN Split3 IS NULL THEN NULL

    ELSE Split1 END AS Subject3 FROM #temp2

    DROP TABLE #SplitTable

    Ugh. This probably wasn't the best way to do this, and it feels kinda inelegant. On a million-row test harness, it works out acceptably in execution speed, but the splitter table would most certainly be more elegant and a lot more efficient. This reminded me of a problem I had to solve myself at some point, but the finer details were a bit more convoluted than I'd hoped.

    - 😀