Split delimited data into one row


  

  set statistics io on 
set statistics time on 

CREATE TABLE TEST_DSP_APPENDED_DATA04
    (ConstituentID int, city varchar(255), total int, RAFFLE_RFMLA varchar(255), customer_id int);
--Add values to table
INSERT INTO TEST_DSP_APPENDED_DATA04
    (ConstituentID, city, total, RAFFLE_RFMLA, customer_id)
VALUES
    (1, 'London', 1000, '5/4-5/£10.00-£11.00/78/90+', 101),
    (4, 'NewYork', 765, '4/21/£35/11/12+', 65),
    (7, 'Mexico', 34, '1-3/21-25/£30-£40/12/52', 431),
    (15 ,'Lisbon', 850, '1-8/15/12/14/55+', 102)
--Check (display) data in table 
SELECT * FROM [dbo].[TEST_DSP_APPENDED_DATA04]

---Query

 --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

SELECT IDENTITY(int, 1,1) id, ConstituentID, city, total, RAFFLE_RFMLA, customer_id,
Substring(RAFFLE_RFMLA , n, charindex('/', RAFFLE_RFMLA  + '/', n) - n)  cols

into mytemp

FROM TEST_DSP_APPENDED_DATA04
Cross apply (Select n from nums) d(n) 
Where n <= len(RAFFLE_RFMLA) AND substring('/' + RAFFLE_RFMLA, n, 1) = '/'

 ;with mycte1 as (
  Select ConstituentID, city, total, RAFFLE_RFMLA, customer_id,Cols
  ,row_number() Over(Partition by ConstituentID Order by id ) rn  
  from mytemp
 )

 Select ConstituentID, city, total, RAFFLE_RFMLA, customer_id,
  Max(Case when rn=1 then cols End) RAFFLE_RFMLA_1 
 , Max(Case when rn=2 then cols End) RAFFLE_RFMLA_2 
 , Max(Case when rn=3 then cols End) RAFFLE_RFMLA_3 
 , Max(Case when rn=4 then cols End) RAFFLE_RFMLA_4 
 , Max(Case when rn=5 then cols End) RAFFLE_RFMLA_5 

FROM mycte1

Group by  ConstituentID, city, total, RAFFLE_RFMLA, customer_id

  --clean up
 drop table mytemp

 -- ;with shredded as
 --(
 --    select ConstituentID, city, total, RAFFLE_RFMLA, customer_id, t.*
 --    from [dbo].[TEST_DSP_APPENDED_DATA04] as data
 --    cross apply [dbo].[DelimitedSplit8K](data.RAFFLE_RFMLA,'/') as t
 --)
 --select 
 --    ConstituentID, city, total, RAFFLE_RFMLA, customer_id
 --   ,isnull(pvt.[1], '') as RAFFLE_RFMLA_1
 --   ,isnull(pvt.[2], '') as RAFFLE_RFMLA_2
 --   ,isnull(pvt.[3], '') as RAFFLE_RFMLA_3
 --   ,isnull(pvt.[4], '') as RAFFLE_RFMLA_4
 --   ,isnull(pvt.[5], '') as RAFFLE_RFMLA_5 
 --from shredded
 --pivot (max(Item) for ItemNumber in ([1],[2],[3],[4],[5])) pvt;

drop   TABLE TEST_DSP_APPENDED_DATA04

set statistics io off 
set statistics time off

 

https://social.msdn.microsoft.com/Forums/en-US/f9ad740a-9824-47e1-94c6-9165da467174/need-help-with-querry?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s