How can i change my output

Transact-SQL
1

declare @checkRegionCountryModel nvarchar(100) = 'Europe : Belgium : KDL345,KDL900 ; Europe : Czech Republic : KDL345,KDL900 ; Asia : India :KDL345'

declare @temp table
(
namevalue nvarchar(100)
)

declare @result table
(
region varchar(100),
country varchar(100),
model varchar(100)
)

insert into @temp
SELECT
x.Item from DelimitedSplit8K(@checkRegionCountryModel,';') x

select f.item as output
FROM @temp l
CROSS APPLY DelimitedSplit8K(replace(l.namevalue,':',','),',') f

output is below:

output

Europe
Belgium
KDL345
KDL900
Europe
Czech Republic
KDL345
KDL900
Asia
India
KDL345

i want to turn the above output to like below

Region Country Model
Europe Belgium KDL345
Europe Belgium KDL900
Europe Czech Republic KDL345
Europe Czech Republic KDL900
Asia India KDL345

2 
DECLARE @checkRegionCountryModel nvarchar(100) = 'Europe : Belgium : KDL345,KDL900 ; Europe : Czech Republic : KDL345,KDL900 ; Asia : India :KDL345';

WITH Splits
AS
(
	SELECT REVERSE(LTRIM(RTRIM(SUBSTRING(X1.Split, X2.Pos + 1, 100)))) AS Split1
		,REVERSE(LTRIM(RTRIM(LEFT(X1.Split, X2.Pos - 1)))) AS Split2
	FROM dbo.DelimitedSplit8K(@checkRegionCountryModel, ';') S
		CROSS APPLY
		(
			VALUES(REVERSE(LTRIM(RTRIM(S.Item))))
		) X1 (Split)
		CROSS APPLY
		(
			VALUES(CHARINDEX(':', X1.Split))
		) X2 (Pos)
)
SELECT LTRIM(RTRIM(LEFT(S.Split1, X1.Pos - 1))) AS Region
	,LTRIM(RTRIM(SUBSTRING(S.Split1, X1.Pos + 1, 100))) As Country
	,X2.Model
FROM Splits S
	CROSS APPLY
	(
		VALUES(CHARINDEX(':', S.Split1))
	) X1 (Pos)
	CROSS APPLY
	(
		SELECT Item
		FROM dbo.DelimitedSplit8K(S.Split2, ',')
	) X2 (Model);
3

Thank you so much @ifor

4

hi

Ifor has given the solution

i have done it in a different way

..if this helps for any reason .. GREAT .. depends on Data

i am assuming always will be KDL ... based on this

declare @checkRegionCountryModel nvarchar(100) = 'Europe : Belgium : KDL345,KDL900 ; Europe : Czech Republic : KDL345,KDL900 ; Asia : India :KDL345'

; with cte as 
(
select ROW_NUMBER() over(order by value) as rn , replace(replace(value, ':KD','#KD'),': KD','#KD') as ok from string_split(@checkRegionCountryModel ,';')  
) , cte_1 as 
(
select rn,row_number() over(partition by rn order by rn) as sam, value as bhim  from cte a cross apply string_split ( a.ok , '#') b 
) , cte_2 as 
(
select a.rn,value  from cte_1 a cross apply string_split (a.bhim,',') b 
where a.sam <> 1 
) 
select a.bhim,b.value from cte_1 a join cte_2 b on a.rn = b.rn where  a.sam = 1

image

5

Thank you @harishgg1

6

@Ifor for the below input

DECLARE @checkRegionCountryModel nvarchar(100) = 'Europe : Belgium : KDL345,KDL900 ; Europe : Czech Republic : KDL345,KDL900 ; Asia : India :KDL345,KDL347';

if i add one more model it is not returning me entire model name in the last row below is the screenshot for reference chances of coming more region , country and model in the input.

image

7

@harishgg1 model is not static there will be different brand with diff model so we can not hardcode.

8

@Ifor ,i got the solution thanks once again

9

hi vajeed

i modified my approach ..

if it helps great .. for ANY reason

DECLARE @checkRegionCountryModel nvarchar(100) = 'Europe : Belgium : KDL345,KDL900 ; Europe : Czech Republic : KDL345,KDL900 ; Asia : India :KDL345,KDL347';

; with 
cte as 
(
	select ROW_NUMBER() over(order by value) as rn , * from string_split(@checkRegionCountryModel ,';')  
),
cte_1 as 
(
	select  value, LEFT(value,  CHARINDEX ( ':' , value  , CHARINDEX ( ':' , value  , 0) +1   ) )  as value123 from cte e
		union all
	select value ,RIGHT(value, len(value)- CHARINDEX ( ':' , value  , CHARINDEX ( ':' , value  , 0)  +1  )) as value123
from cte 
) , 
cte_rn as 
(
	select ROW_NUMBER () over(partition by value order by value123) as rn , * from cte_1 
) , 
cte_next as 
( 
	select *,value as joinvalue from cte_rn where rn <> 1 
) , cte_ok as 
(
select rn, b.value , a.joinvalue  from cte_next a cross apply string_split( a.value123 ,',') b
) 
select b.value123, a.value from cte_ok a join (select * from cte_rn where rn = 1 ) b on a.joinvalue = b.value
go

image

10

any thoughts Vajeed

:curious

11 
SELECT 
    region,
    country,
    LTRIM(RTRIM(split3.Item)) AS model
FROM (
    SELECT 
        LTRIM(RTRIM(MAX(CASE WHEN split2.ItemNumber = 1 THEN split2.Item ELSE '' END))) AS region,
        LTRIM(RTRIM(MAX(CASE WHEN split2.ItemNumber = 2 THEN split2.Item ELSE '' END))) AS country,
        LTRIM(RTRIM(MAX(CASE WHEN split2.ItemNumber = 3 THEN split2.Item ELSE '' END))) AS model     
    FROM dbo.DelimitedSplit8K(@checkRegionCountryModel,';') AS split1
    CROSS APPLY dbo.DelimitedSplit8K(split1.Item, ':') AS split2
    GROUP BY split1.ItemNumber
) AS split2
CROSS APPLY dbo.DelimitedSplit8K(model, ',') AS split3
ORDER BY region, country, model
1 Like
12

Harish thank you so much for your efforts it is not exactly what i wanted but it was helpful and there was a lot to learn in your post.

in the above post the length of variable is 100 that is the reason model showing KD instead full name if i increase the length then it works fine.

DECLARE @checkRegionCountryModel nvarchar(1000)

13

Thank you for your time and efforts @ScottPletcher

14

hi vajeed

what was it that you exactly wanted ?

i am curious ..

i want to see if i can do it

15

I wanted in this way.

Region Country Model
Europe Belgium KDL345
Europe Belgium KDL900
Europe Czech Republic KDL345
Europe Czech Republic KDL900
Asia India KDL345
16

Oh ok ..

That looks easy to do

It is my bed time

I will try tomorrow

17

who or what is providing you this data?

18

My initial query was incomplete at first. I've now completed it. Please see if it fully meets your needs now:

SELECT 
    region,
    country,
    LTRIM(RTRIM(split3.Item)) AS model
FROM (
    SELECT 
        LTRIM(RTRIM(MAX(CASE WHEN split2.ItemNumber = 1 THEN split2.Item ELSE '' END))) AS region,
        LTRIM(RTRIM(MAX(CASE WHEN split2.ItemNumber = 2 THEN split2.Item ELSE '' END))) AS country,
        LTRIM(RTRIM(MAX(CASE WHEN split2.ItemNumber = 3 THEN split2.Item ELSE '' END))) AS model     
    FROM dbo.DelimitedSplit8K(@checkRegionCountryModel,';') AS split1
    CROSS APPLY dbo.DelimitedSplit8K(split1.Item, ':') AS split2
    GROUP BY split1.ItemNumber
) AS split2
CROSS APPLY dbo.DelimitedSplit8K(model, ',') AS split3
ORDER BY region, country, model