XML Split and Pivot


  
 
create table rating_label
(
id_pk int,
label_message varchar(500)
)


insert into rating_label values(105,'Look and Feel');
insert into rating_label values(107,'Performance And Speed');
insert into rating_label values(106,'Ease of Use');
insert into rating_label values(109,'Good');
insert into rating_label values(108,'Performance');

create table query_info
(
cust_mob varchar(20),
rating_id varchar(200),
Rated_Date date default getdate()
)

INSERT query_info (cust_mob, rating_id, Rated_Date) VALUES (N'6556949494', N'109*1#108*1#107*3#106*0#105*4#', CAST(N'2018-02-15' AS Date))
 
--INSERT into query_info (cust_mob,rating_id, Rated_Date) VALUES (N'7756946507', N'109*4#108*0#107*5#106*1#105*5#', CAST(N'2018-02-12' AS Date))
--, (N'6565676879', N'109*3#108*4#107*2#106*3#105*4#', CAST(N'2018-02-12' AS Date))
--, (N'3434544657', N'109*2#108*1#107*0#106*2#105*0#', CAST(N'2018-02-12' AS Date))
--insert into query_info (cust_mob,rating_id)values('6556949494','109*1#108*1#107*3#106*0#105*4#'),('7556949486','109*1#108*2#107*2#106*0#105*0#'),('8547125934','109*5#108*0#107*4#106*0#105*4#')
;with mycte as (
select * ,  
CAST (N'<H><r>' +  REPLACE(rating_id,  '#', '</r><r>')+ '</r></H>' AS XML) vals
from query_info
WHERE Rated_Date='2018-02-15' )
, mycte1 as
 ( 
 SELECT  cust_mob,  S.a.value('.', 'VARCHAR(100)') AS splitVal1 
 FROM mycte d
 CROSS APPLY d.[vals].nodes('/H/r') S(a)
 )
 , mycte2 as
 ( 
Select *, CAST (N'<H><r>' + Replace( splitVal1, '*','</r><r>') + '</r></H>' AS XML) [vals] from mycte1
 )
 ,mycte3 as (

 Select DISTINCT cust_mob, 
 S.a.value('(/H/r)[1]', 'VARCHAR(100)') cat,  S.a.value('(/H/r)[2]', 'VARCHAR(100)') val  
 FROM mycte2 d   
 CROSS APPLY d.[vals].nodes('/H/r') S(a)  
 )

 Select rl.label_message, sum(Case when val=1 then 1 else 0 end) [1]
 , sum(Case when val=2 then 1 else 0 end) [2]
 , sum(Case when val=3 then 1 else 0 end) [3]
 , sum(Case when val=4 then 1 else 0 end) [4]
 , sum(Case when val=5 then 1 else 0 end) [5]
 from mycte3 m join rating_label rl on m.cat=rl.id_pk
 WHERE cat<>''
 Group by cat,rl.label_message
 Order by cat
  
 

 

drop table query_info , rating_label
 


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/0e6ddccd-5e94-4478-bd4b-8ca7412cb915/split-row-query?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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s