Pivot table in SQL Server 2008 r2

Question

I have a table with Comma Separated values in multiple Columns as

dept_rule  |dept_descr|dept   |dept_pos
-----------+----------+-------+--------
four rules |No descrrr|aaa,bbb|xxx,yyy

I want to seperate the values, as the below table

dept_rule |dept_descr|dept|dept_pos
----------+----------+----+--------
four rules|No descrrr|aaa |xxx
four rules|No descrrr|aaa |yyy
four rules|No descrrr|bbb |xxx
four rules|No descrrr|bbb |yyy

How to write query to do this.

Thanks in advance...

Are there always only 2 values separated by coma? Or can be there more? — Michal Barcik, Jan 04 '12 at 12:28
there can be three or four values, I want to make the query as dynamic... — user994860, Jan 04 '12 at 13:09
Do both lists always contain the same number of items? If not, what should the results look like? — Andriy M, Jan 05 '12 at 07:35

score 0 · Answer 1 · edited May 23 '17 at 12:03

If dept and dept_pos have two values separated with comma, then this should help

CREATE TABLE #T1 (
    dept_rule nvarchar(20),
    dept_descr nvarchar(20),
    dept nvarchar(20),
    dept_pos nvarchar(20))

INSERT INTO #T1 VALUES (
    'four rules',
    'No descrrr',
    'aaaa,bbbbb',
    'xxx,yy'
)

;with T as (
    select
        dept_rule,
        dept_descr,
        dept,
        dept_pos,
        charindex(',', dept) pos1,
        charindex(',', dept_pos) pos2
    from #T1
)

select 
    dept_rule,
    dept_descr,
    LEFT(dept, pos1 - 1),
    LEFT(dept_pos, pos2 - 1)
from T 

UNION ALL

select 
    dept_rule,
    dept_descr,
    LEFT(dept, pos1 - 1),
    RIGHT(dept_pos, LEN(dept_pos) - pos2)
from T 

UNION ALL

select 
    dept_rule,
    dept_descr,
    RIGHT(dept, LEN(dept) - pos1),
    LEFT(dept_pos, pos2 - 1)
from T 

UNION ALL

select 
    dept_rule,
    dept_descr,
    RIGHT(dept, LEN(dept) - pos1),
    RIGHT(dept_pos, LEN(dept_pos) - pos2)
from T 

DROP TABLE #T1

UPDATE: More flexible solution - multiple separators. But each column with separators adds more complexity

-- separator
DECLARE @sep nvarchar(1) = ','

-- our data table
CREATE TABLE #T (
    dept_rule nvarchar(20),
    dept_descr nvarchar(20),
    dept nvarchar(20),
    dept_pos nvarchar(20)
)

-- sample data
INSERT INTO #T VALUES
    ('four rules', 'No descrrr', 'aaaaa,bbb,ccc', 'kk,ll,mm'),
    ('four rules', 'No descrrr', 'x,yyyy', 'sss,rrr'),
    ('four rules', 'No descrrr', 'zzz', 'xxxx,lll')

-- find occurences of separator in the column 'dept'
;WITH T AS (
    SELECT
        0 AS row,
        0 AS start,
        CHARINDEX(@sep, dept) pos,
        dept
    FROM #T

    UNION ALL

    SELECT
        pos + 1,
        pos,
        CHARINDEX(@sep, dept, pos + 1),
        dept
    FROM T

    WHERE
        pos > 0
)

-- remember result of splitting first column
SELECT
    #T.*,
    a.part
INTO #Result1
FROM (
    -- result with string parts
    SELECT
        T.dept,
        T.start,
        T.pos AS finish,
        SUBSTRING(T.dept, T.start + 1, T.pos - (T.start + 1)) AS part
    FROM T 
    WHERE
        T.pos > 0

    UNION ALL

    -- but separators don't give us last part, append
    SELECT 
        T.dept,
        T.start,
        T.pos AS finish,
        RIGHT(T.dept, LEN(T.dept) - T.start) AS part
    FROM T 
    WHERE
        T.pos = 0
) a

INNER JOIN #T
    ON #t.dept = a.dept

ORDER BY
    a.dept,
    a.start,
    a.finish

-- SELECT * FROM #Result1

-- now second column
;WITH T2 AS (
    SELECT
        0 AS row,
        0 AS start,
        CHARINDEX(@sep, dept_pos) pos,
        dept_pos
    FROM #T

    UNION ALL

    SELECT
        pos + 1,
        pos,
        CHARINDEX(@sep, dept_pos, pos + 1),
        dept_pos
    FROM T2

    WHERE
        pos > 0
)

-- append second column's splits to first result
SELECT
    a.dept_rule,
    a.dept_descr,
    a.part AS part1,
    b.part AS part2
FROM (
    -- result with string parts
    SELECT
        T2.dept_pos,
        T2.start,
        T2.pos AS finish,
        SUBSTRING(T2.dept_pos, T2.start + 1, T2.pos - (T2.start + 1)) AS part
    FROM T2
    WHERE
        T2.pos > 0

    UNION ALL

    -- but separators don't give us last part, append
    SELECT 
        T2.dept_pos,
        T2.start,
        T2.pos AS finish,
        RIGHT(T2.dept_pos, LEN(T2.dept_pos) - T2.start) AS part
    FROM T2
    WHERE
        T2.pos = 0
) b
    INNER JOIN #Result1 a
        ON a.dept_pos = b.dept_pos

-- clean up
DROP TABLE #T
DROP TABLE #Result1

UPDATE2: I based on first answer of Alex.K. to question SQL Server - find nth occurrence in a string - now it's improved and you may apply them to my solution

UPDATE3: Improved code based on link from UPDATE2

-- separator
DECLARE @sep nvarchar(1) = ','

-- our data table
CREATE TABLE #T (
    dept_rule nvarchar(20),
    dept_descr nvarchar(20),
    dept nvarchar(20),
    dept_pos nvarchar(20)
)

-- sample data
INSERT INTO #T VALUES
    ('four rules', 'No descrrr', 'aaaaa,bbb,ccc', 'kk,ll,mm'),
    ('four rules', 'No descrrr', 'x,yyyy', 'sss,rrr'),
    ('four rules', 'No descrrr', 'zzz', 'xxxx,lll')

-- find occurences of separator in the column 'dept'
;WITH T (dept, start, pos) AS (
    SELECT
        dept,
        1,
        CHARINDEX(@sep, dept)
    FROM #T

    UNION ALL

    SELECT
        dept,
        pos + 1,
        CHARINDEX(@sep, dept, pos + 1)
    FROM T

    WHERE
        pos > 0
)

-- remember result of splitting first column
SELECT
    #T.*,
    a.token
INTO #Result1
FROM (
    SELECT
        *,
        SUBSTRING(T.dept, T.start, CASE WHEN T.pos > 0 THEN T.pos - T.start ELSE LEN(T.dept) END) AS token
    FROM T
) a

INNER JOIN #T
    ON #t.dept = a.dept

ORDER BY
    a.dept


-- now second column
;WITH T2 (dept_pos, start, pos) AS (
    SELECT
        dept_pos,
        1,
        CHARINDEX(@sep, dept_pos)
    FROM #T

    UNION ALL

    SELECT
        dept_pos,
        pos + 1,
        CHARINDEX(@sep, dept_pos, pos + 1)
    FROM T2

    WHERE
        pos > 0
)

-- append second column's splits to first result
SELECT
    a.dept_rule,
    a.dept_descr,
    a.token AS token1,
    b.token AS token2
FROM (
    SELECT
        *,
        SUBSTRING(T2.dept_pos, T2.start, CASE WHEN T2.pos > 0 THEN T2.pos - T2.start ELSE LEN(T2.dept_pos) END) AS token
    FROM T2
) b
    INNER JOIN #Result1 a
        ON a.dept_pos = b.dept_pos

-- clean up
DROP TABLE #T
DROP TABLE #Result1

yeah, the code is really working fine... can I make the query dynamic.. As I may have three or more comma separated values... — user994860, Jan 04 '12 at 13:09
Code is working fine, but can I make the code to work for N number of columns(with comma separated values)... I mean, I may have another dynamic column(with comma separated values) and I need to process that with the above two Comma Separated columns. — user994860, Jan 06 '12 at 12:00
Yes you can. Look at example from UPDATE3. I've done it column by column. Just add next CTE - WITH T3 (columns) AS () ... where you explode data from third column and then append it to the result - and so on. — BartekR, Jan 06 '12 at 14:34

score 0 · Accepted Answer · answered Jan 04 '12 at 12:49

Here is the code for 2 values only in a column:

declare @a table (dept_rule varchar(10), dept_descr varchar(50), dept varchar(50), dept_pos varchar(50)) insert into @a values ('four rules','No descrrr', 'aaa,bbb', 'xxx,yyy')

    select 
       dept_rule, 
       dept_descr,
       dept_unp,
       dept_post_unp
    from

    (
       select 
          dept_rule, 
          dept_descr,
          substring(dept, 0, charindex(',', dept)) as dept,
          substring(dept, charindex(',', dept)+1, len(dept)) as dept1,   
          dept_post_unp
       from
       (
          select 
             dept_rule, 
             dept_descr,  

             dept,
             substring(dept_pos, 0, charindex(',', dept_pos)) as dept_pos,  
             substring(dept_pos, charindex(',', dept_pos)+1, len(dept_pos)) as dept_pos1  
          from @a
       )to_unpivot1
       unpivot
       (
       dept_post_unp for depts in(dept_pos, dept_pos1)
       )unpivoted1

    )to_unpivot2
    unpivot
    (
    dept_unp for depts in(dept, dept1)
    )unpivoted2

Pivot table in SQL Server 2008 r2

2 Answers2