CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
Jhjry smth
Jhjry smth

Reputation: 197

Splitting single row into more columns based on column value

I've a requirement to get 3 similar set of row data replacing the column value if any certain value exists in the given column('[#]' in this case). For example

---------------------
Type     Value
---------------------
1        Apple[#]
2        Orange
3        Peach[#]

I need to modify the query to get value as below

 ----------------------
  Type        Value
 --------------------
   1         Apple1
   1         Apple2
   1         Apple3
   2         Orange
   3         Peach1
   3         Peach2
   3         Peach3

I could not come up with logic how to get this

Upvotes: 0

Views: 93

Answers (4)

Marc Guillot
Marc Guillot

Reputation: 6465

You can also get the same result without recursivity :

select Type, Value from MyTable where Right(Value, 3) <> '[#]'
union
select Type, Replace(Value, '[#]', '1') from MyTable where Right(Value, 3) = '[#]'
union
select Type, Replace(Value, '[#]', '2') from MyTable where Right(Value, 3) = '[#]'
union
select Type, Replace(Value, '[#]', '3') from MyTable where Right(Value, 3) = '[#]'

order by 1, 2

Upvotes: 1

Chris Albert
Chris Albert

Reputation: 2507

Using a recursive CTE

CREATE TABLE #test
(
    Type int,
    Value varchar(50)
)

INSERT INTO #test VALUES
(1, 'Apple[#]'),
(2, 'Orange'),
(3, 'Peach[#]');

WITH CTE AS (
    SELECT
        Type,
        IIF(RIGHT(Value, 3) = '[#]', LEFT(Value, LEN(Value) - 3), Value) AS 'Value',
        IIF(RIGHT(Value, 3) = '[#]', 1, NULL) AS 'Counter'
    FROM
        #test
    UNION ALL
    SELECT
        B.Type,
        LEFT(B.Value, LEN(B.Value) - 3) AS 'Value',
        Counter + 1
    FROM
        #test AS B
        JOIN CTE
            ON B.Type = CTE.Type
    WHERE
        RIGHT(B.Value, 3) = '[#]'
        AND Counter < 3
)

SELECT 
    Type,
    CONCAT(Value, Counter) AS 'Value'
FROM 
    CTE
ORDER BY
    Type,
    Value

DROP TABLE #test

Upvotes: 0

TriV
TriV

Reputation: 5148

Try this query

DECLARE @SampleData AS TABLE 
(
    Type int,
    Value varchar(100)
)

INSERT INTO @SampleData
VALUES (1, 'Apple[#]'), (2, 'Orange'), (3, 'Peach[#]')

SELECT sd.Type, cr.Value
FROM @SampleData sd
CROSS APPLY 
(
    SELECT TOP (IIF(Charindex('[#]', sd.Value) > 0, 3, 1)) 
            x.[Value] + Cast(v.t as nvarchar(5)) as Value
    FROM 
        (SELECT Replace(sd.Value, '[#]', '') AS Value) x
        Cross JOIN (VALUES (1),(2),(3)) v(t)
    Order by v.t asc

) cr

Demo link: Rextester

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Assuming there is only one digit (as in your example), then I would go for:

with cte as (
      select (case when value like '%\[%%' then left(right(value, 2), 1) + 0
                   else 1
              end) as cnt, 1 as n,
             left(value, charindex('[', value + '[')) as base, type
      from t
      union all
      select cnt, n + 1, base, type
      from cte
      where n + 1 <= cnt
     )
select type,
       (case when cnt = 1 then base else concat(base, n) end) as value
from cte;

Of course, the CTE can be easily extended to any number of digits:

(case when value like '%\[%%'
      then stuff(left(value, charindex(']')), 1, charindex(value, '['), '') + 0
      else 1
 end)

And once you have the number, you can use another source of numbers. But the recursive CTE seems like the simplest solution for the particular problem in the question.

Upvotes: 1

Related Questions