CODEWITHSUNDEEP
sqlsql-server-2008
jay
jay

Reputation: 3869

how to write query for split two rows?

I have order table 

OrderId OrderStatusId  CurrencyId      PromotionCode
------------------------------------------------------
  137         5           1             123a-123d
  138         5           1             123a-123d-234c

I want to split the PromotionCode column like this:

Result:

OrderId  OrderStatusId  CurrencyId  PromotionCode
-----------------------------------------------------
  137             5             1             123a
  137             5             1             123d
  138             5             1             123a
  138             5             1             123d
  138             5             1             234c

Please help me ...

is it possible to do... any way plz help me asas possible....

Upvotes: 0

Views: 277

Answers (1)

Andomar
Andomar

Reputation: 238078

If the promotion codes are always 4 characters long, the simplest way is probably a union:

select  id, substring(code,1,4)
from    YourTable
where   LEN(code) >= 4
union all
select  id, substring(code,6,4)
from    YourTable
where   LEN(code) >= 9
union all
select  id, substring(code,11,4)
from    YourTable
where   LEN(code) >= 14
<etc>

For a more flexible solution, have a look at one of the various Split functions. After creating this function in your database, you can invoke it like:

select  t.id, s.items
from    YourTable t
cross apply   
        dbo.Split(t.code,'-') s

Both queries will produce the result as requested in your answer.

Upvotes: 2

Related Questions