Update table from these SQL query

Transact-SQL
1

Hi sql expert,

I am junior with sql and trying to update a simple table from output A to output B as shown below from this sql statement:

SELECT Cable_Source, Cable_Codes
FROM Cable_Table

Basically, BL1 is the highest level, so any cable_codes that listed is a Cable_Source, then we want to take all the Cable_Codes of that Cable_Source to the above row of Cable_codes.

For example:
Cable_Course Bl1 has the Cable_Codes of DD1,ED1, BL2
Because the value of BL2 in BL1 Cable_Codes listed as Cable_Source, then I want to list all the Cable_Codes of BL2: (CD1, DD1, ED1, BL3, BL4) in the Cable_Codes of BL1 that is currently not listed in BL1 then delete those Cable_Codes in the BL2.

The same for BL3, because BL3 listed as Cable_Codes in the BL2, all the Cable_Codes in BL3 gets move to BL1.

The same for BL4 as well.

Here is the current output A and here is the output I want to achieve:

A
A846×163 22.6 KB

Here is the create table and values statement:

CREATE TABLE Cable_Table
(
Cable_Source VARCHAR(3)
,Cable_Codes VARCHAR(200)
)

INSERT INTO Cable_Table (Cable_Source,Cable_Codes)
VALUES ('BL1','DD1,ED1,BL2'), ('BL2','CD1,DD1,ED1,BL3,BL4'),('BL3','CD1,ED1'), ('BL4','ED1')

2

The idea that inspired me in trying to find a solution is to create a second table (Temp_Cable_Table) in order to proceed by successive steps.
The problem you raise seems to me difficult to implement because it looks more like a procedure than a data selection query. Therefore, consider my work as a suggestion rather than a solution.
On the first pass the Temp_Cable_Table is filled with the split of the Cable_Codes fields of Cable_Table.

Cable_table

Cable_Source Cable_Codes
------------------- -----------
BL1                 DD1,ED1,BL2
BL2                 CD1,DD1,ED1,BL3,BL4
BL3                 CD1,ED1
BL4                 ED1

Temp_Cable_table

Cable_Source Cable_Codes
------------------- ------------------
BL1                  DD1
BL1                  ED1
BL1                  BL2
BL2                  CD1
BL2                  DD1
BL2                  ED1
BL2                  BL3
BL2                  BL4
BL3                  CD1
BL3                  ED1
BL4                  ED1

The Temp_Cable_Table is then updated by propagating the BL1 code to all the remaining lines having the same Cable_Codes as the lines with Cable_Source equal to 'BL1'. or to all lines with different Cable_Source but equal Cable_Codes.

Step 3 therefore eliminates any duplicate rows that may have been generated in step 2.

Finally, step 4 collapses all rows of Temp_Codes_Table into one row by aggregating the Cable_Codes fields.

Who knows if all this will be useful!

--
-- Step 0
--
if object_id('Temp_Cable_Table') is not null drop table Temp_Cable_Table
if object_id('Cable_Table') is not null drop table Cable_Table
create table Cable_Table
(
	Cable_Source varchar(3)
	, Cable_Codes varchar(200)
)
insert into Cable_Table (Cable_Source,Cable_Codes) values
	('BL1', 'DD1,ED1,BL2')
	, ('BL2', 'CD1,DD1,ED1,BL3,BL4')
	, ('BL3', 'CD1,ED1'), ('BL4','ED1')

create table Temp_Cable_Table
(
	Cable_Source varchar(3)
	, Cable_Codes varchar(200)
)

--
-- Step 1
--
insert into 
	Temp_Cable_Table
	(
		Cable_Source
		, Cable_Codes
	)
select
	Cable_Source
	, value
from
	Cable_Table
cross apply
	string_split(Cable_Codes, ',')
order by
	Cable_Source

--
-- Step 2
--
update
	Temp_Cable_Table
set
	Cable_Source = 'BL1'
from
	Temp_Cable_Table t
where
	t.Cable_Source not in ('BL1')
	and 
	(
		exists 
		(
			select
				1
			from
				Temp_Cable_Table tt
			where
				tt.Cable_Source = 'BL1'
				and tt.Cable_Codes = t.Cable_Codes
		)
		or
		exists
		(
			select
				1
			from
				Temp_Cable_Table tt
			where
				tt.Cable_Source <> T.Cable_Source
				and tt.Cable_Codes = t.Cable_Source
		)
	)

--
-- Step 3
--
;with cte as
(
	select
		t.*
		, row_number() over (partition by Cable_Codes order by t.Cable_Codes) as rn
	from
		Temp_Cable_Table t
)
delete
	cte
where
	rn > 1

---
-- Step 4
--
select
	Cable_Source
	, string_agg (convert(varchar(max),Cable_Codes), ',')
from
	Temp_Cable_Table
group by Cable_Source
3

Thank you gdl. I think I have an issue with 'string_agg' is not a recognized built-in function name.in my SQL server 2012.

4

Hi SQLBoy14.
You didn't say the compatibility level of your SQL engine.
If you have SQL Server 2012, you are also having problems with the String_Split function.
So try this new query, remembering that mine is just an attempt at a solution.

--
-- Step 0
--
if object_id('Temp_Cable_Table') is not null drop table Temp_Cable_Table
if object_id('Cable_Table') is not null drop table Cable_Table
create table Cable_Table
(
	Cable_Source varchar(3)
	, Cable_Codes varchar(200)
)
insert into Cable_Table (Cable_Source,Cable_Codes) values
	('BL1', 'DD1,ED1,BL2')
	, ('BL2', 'CD1,DD1,ED1,BL3,BL4')
	, ('BL3', 'CD1,ED1')
	, ('BL4','ED1')

create table Temp_Cable_Table
(
	Cable_Source varchar(3)
	, Cable_Codes varchar(200)
)

--
-- Step 1
--
insert into 
	Temp_Cable_Table
	(
		Cable_Source
		, Cable_Codes
	)
select
	Cable_Source
	, split.agg.value('.', 'varchar(max)')
from
(
    select
		Cable_Source
		, cast('<X>' + replace(Cable_Codes, ',', '</X><X>')+'</X>' as xml) as s from Cable_Table
) as t
cross apply s.nodes('/X') as split(agg)

--
-- Step 2
--
update
	Temp_Cable_Table
set
	Cable_Source = 'BL1'
from
	Temp_Cable_Table t
where
	t.Cable_Source not in ('BL1')
	and 
	(
		exists 
		(
			select
				1
			from
				Temp_Cable_Table tt
			where
				tt.Cable_Source = 'BL1'
				and tt.Cable_Codes = t.Cable_Codes
		)
		or
		exists
		(
			select
				1
			from
				Temp_Cable_Table tt
			where
				tt.Cable_Source <> T.Cable_Source
				and tt.Cable_Codes = t.Cable_Source
		)
	)

--
-- Step 3
--
;with cte as
(
	select
		t.*
		, row_number() over (partition by Cable_Codes order by t.Cable_Codes) as rn
	from
		Temp_Cable_Table t
)
delete
	cte
where
	rn > 1

---
-- Step 4
--
select
	Cable_Source
	,
	case when Cable_Source = 'BL1' 
		then
			stuff
			(
				(
					select
						',' + cast(case when tt.Cable_Source='BL1' then tt.Cable_Codes else '' end as varchar(max))
					from
						Temp_Cable_Table tt
					for
						xml path ('')
				)
				, 1, 1, ''
			)
		else
			(
				select
					tt.Cable_Codes
				from
					Temp_Cable_Table tt
				where
					tt.Cable_Source=t.Cable_Source
			)
	end
from
	Temp_Cable_Table t
group by
	Cable_Source
6

hi hope this helps

first create a function like string split

please click arrow to the left for function script

CREATE FUNCTION dbo.harish_splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

DECLARE @name NVARCHAR(255)
DECLARE @pos INT

WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

INSERT INTO @returnList
SELECT @name

SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END

INSERT INTO @returnList
SELECT @stringToSplit

RETURN
END

; WITH cte
     AS (SELECT DISTINCT 'BL1' AS ok,
                         NAME
         FROM   cable_table
                CROSS apply Harish_splitstring(cable_codes))
SELECT t.ok,
       NAME = Stuff((SELECT DISTINCT ',' + NAME
                     FROM   cte
                     WHERE  ok = t.ok
                     FOR xml path('')), 1, 1, '')
FROM   cte t
GROUP  BY t.ok;

image

7

Thank you gdl! very helpful!

8

Thank you, harishgg1. this actually works and what I am looking for.