String Updation in a formula of a table -Query required

Question

String Updation in a formula of a table -Query required

mytesting9

SSC Enthusiast

Points: 195
More actions
September 14, 2016 at 4:20 am

#313056

Table is as Follows:
KPI ID :: KPI NAME:: KPIFORMULA::
QW1 :: ABC :: No formula
SA2 ::CRF ::[ABC]+[CRF]+[KJL]
XS3 :: EFH ::[ABC]+[KJL]
BG7 :: KJL ::[ABC]+[EFH ]
we need expected output like this:
KPI ID :: KPI NAME:: KPIFORMULA::
QW1 :: ABC :: No formula
SA2 ::CRF ::[QW1]+[SA2]+[BG7]
XS3 :: EFH ::[QW1]+[BG7]
BG7 :: KJL ::[QW1]+[XS3 ]

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

I'm really not sure what it is your asking here. CAn you give a full details of your requirements. Please supply DDL and insert statements for your tables.

Taking it literally as, the following would achieve your goal, but I have a feeling this isn't what you want.

Create table #KPI

([KPI ID] varchar(3),

[KPI Name] varchar(3),

KPIFormula varchar(30));

Insert into #KPI Values

('QW1','ABC','No Formula'),

('SA2','CRF','[ABC]+[CRF]+[KJL]'),

('XS3','EFH','[ABC]+[KJL]'),

('BG7','KJL','[ABC]+[EFH]');

Select *

from #KPI;

--Update statements

Update #KPI

Set KPIFormula = '[QW1]+[SA2]+[BG7]'

where [KPI ID] = 'SA2';

Update #KPI

Set KPIFormula = '[QW1]+[BG7]'

where [KPI ID] = 'XS3';

Update #KPI

Set KPIFormula = '[QW1]+[XS3]'

where [KPI ID] = 'BG7';

Select *

from #KPI;

Drop Table #KPI;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Using the house string-splitter[/url]:

SELECT

t.[KPI ID],

t.[KPI Name],

KPIFormula = ISNULL(x.KPIFormula,'No Formula')

FROM #KPI t

CROSS APPLY (

SELECT KPIFormula = STUFF((

SELECT '+['+[KPI ID]+']'

FROM dbo.il_SplitDelimitedStringArray(KPIFormula, '+') d

LEFT JOIN #KPI ti ON '['+ti.[KPI Name]+']' = d.Item

ORDER BY ItemNumber

FOR XML PATH('')),1,1,'')

) x;

Also, Google "Updation":

The word "Updation" is not recognized either by Oxford dictionary or by Merriam Webster dictionary, neither for US English nor for world english. Also, it is incorrect to say that "updation" is used mainly in India ONLY as a substitute to "updating"

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mytesting9 SSC Enthusiast Points: 195 More actions · Answer 3

KPI IDKPI NameKPIFormula

QW1 ABC

SA2 CRF [ABC]+[CRF]+[KJL]

XS3 EFH [ABC]+[KJL]

BG7 KJL [ABC]+[EFH]

In This table i need all the Strings in the formula [ABC]+[CRF]+[KJL] should be updated by KPI ID .

Expected output should be ....

[QW1]+[SA2]+[BG7] .I have almost 200 KPI which is having formula which need to be updated .

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

mytesting9 (9/14/2016)
KPI IDKPI NameKPIFormula
QW1 ABC
SA2 CRF [ABC]+[CRF]+[KJL]
XS3 EFH [ABC]+[KJL]
BG7 KJL [ABC]+[EFH]
In This table i need all the Strings in the formula [ABC]+[CRF]+[KJL] should be updated by KPI ID .
Expected output should be ....
[QW1]+[SA2]+[BG7] .I have almost 200 KPI which is having formula which need to be updated .

You have a solution - why are you posting the same question again?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

mytesting9 SSC Enthusiast Points: 195 More actions · Answer 5

When I run

Msg 208, Level 16, State 1, Line 1

Invalid object name 'dbo.il_SplitDelimitedStringArray'.

and it does not provide me solution yet .Pls help .

Thom A SSC Guru Points: 99126 More actions · Answer 6

mytesting9 (9/14/2016)
When I run
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.il_SplitDelimitedStringArray'.
and it does not provide me solution yet .Pls help .

I've got to be honest, but your reason is right there. dbo.il_SplitDelimitedStringArray is an invalid object. It does not exist.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 7

This should do what you're asking...

-- Test data --

IF OBJECT_ID('tempdb..#OrigTable', 'U') IS NOT NULL

DROP TABLE #OrigTable;

CREATE TABLE #OrigTable (

KPI_ID CHAR(3),

KPI_NAME CHAR(3),

KPIFORMULA VARCHAR(1000)

);

INSERT #OrigTable (KPI_ID, KPI_NAME, KPIFORMULA) VALUES

('QW1', 'ABC', 'No formula'),

('SA2', 'RF', '[ABC]+[CRF]+[KJL]'),

('XS3', 'EFH', '[ABC]+[KJL]'),

('BG7', 'KJL', '[ABC]+[EFH]');

--=============================

-- Solution --

WITH

cte_OrigValues AS (-- use string splitter to break up the formula and remove brackets

SELECT

ot.KPI_ID,

ot.KPI_NAME,

ot.KPIFORMULA,

sc.ItemNumber,

Item = REPLACE(REPLACE(sc.Item, '[', ''), ']', '')

FROM

#OrigTable ot

CROSS APPLY dbo.DelimitedSplit8K(ot.KPIFORMULA, '+') sc

),

cte_SwapValues AS (-- replace name values with id values

SELECT

ov.KPI_ID,

ov.KPI_NAME,

ov.ItemNumber,

FORM_PART = COALESCE(ot.KPI_ID, ov.Item)

FROM

cte_OrigValues ov

LEFT JOIN #OrigTable ot

ON ov.Item = ot.KPI_NAME

)

SELECT-- use FOR XML PATH to reassemble the formulas

sw.KPI_ID,

sw.KPI_NAME,

KPIFORMULA = STUFF((

SELECT

CONCAT('+[', sw2.FORM_PART, ']')

FROM

cte_SwapValues sw2

WHERE

sw.KPI_ID = sw2.KPI_ID

ORDER BY sw2.ItemNumber

FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '')

FROM

cte_SwapValues sw

GROUP BY

sw.KPI_ID,

sw.KPI_NAME;

Result:

KPI_ID KPI_NAME KPIFORMULA

------ -------- ---------------------

QW1 ABC [No formula]

XS3 EFH [QW1]+[BG7]

BG7 KJL [QW1]+[XS3]

SA2 RF [QW1]+[CRF]+[BG7]

mytesting9 SSC Enthusiast Points: 195 More actions · Answer 8

Awesome ! The Piece works for me ....

Great dude ... for taking point in race and not skipping the beat 🙂

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 9

Looking at execution plans... Chris's script is far more efficient than mine... Use his script.

Just an FYI... Chris and I are using the same split function. We just gave them different names (DelimitedSplit8k is the name the original author, Jeff Moden, used and is the name most people are familiar with).

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Jason A. Long (9/14/2016)
Looking at execution plans... Chris's script is far more efficient than mine... Use his script.
Just an FYI... Chris and I are using the same split function. We just gave them different names (DelimitedSplit8k is the name the original author, Jeff Moden, used and is the name most people are familiar with).

Your script isn't as bad as it would appear from the execution plans, Jason. Try STATISTICS TIME. Plan costs rate your script 50x slower than mine. It's not, it's more like 2x. Well it is twice as much code, that's good value for money 😉

I ramped up the sample set to a couple of dozen rows to test and dammit I've just thrown it away 🙁

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 11

Either way, yours is clearly better (well done sir!). Considering the sample size, I didn't bother looking the the timings. It's just not large enough to get accurate times and I don't have time to build a larger harness.

Anand BI Developer SSCommitted Points: 1772 More actions · Answer 12

Solution :

select [KPI ID]

,[KPI Name],

[KPIFormula] = case when [kpi id] = 'SA2' then '[QW1]+[SA2]+[BG7]'

when [kpi id] = 'XS3' then '[QW1]+[BG7]'

when [kpi id] = 'BG7' then 'BG7' else [kpi id]end

from #kpi

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 13

Anandkumar-SQL_Developer (9/16/2016)
Solution :
select [KPI ID]
,[KPI Name],
[KPIFormula] = case when [kpi id] = 'SA2' then '[QW1]+[SA2]+[BG7]'
when [kpi id] = 'XS3' then '[QW1]+[BG7]'
when [kpi id] = 'BG7' then 'BG7' else [kpi id]end
from #kpi

...and if there are thousands (or millions) of KPI's???

I'm pretty sure I'd rather slit my own wrists than manually rewrite every formula.