August 2, 2013 at 11:28 am
i am trying to explain it here
example:
ID pcondition scondition
1 HF NULL
1 CA NULL
1 CF NULL
2 CA NULL
2 AST NULL
the order for updating data is
1 HF,
2 CA
3 CF
4 AST
if for particular ID ( ID 1)
WE HAVE 3 pcondition, HF,CA,CF
based on order ,here HF will be pcondition and CA and CF WILL MOVE TO scondition
for ID(2), WE have CA,AST
Based on order among 4 condition,whiever comes first will be primary
so for ID(2) CA will be pcondition and AST will move to scondition
result
ID pcondition scondition
1 PF NULL
1 NULL CA
1 null CF
2 CA NULL
2 NULL AST
thanks
August 2, 2013 at 12:08 pm
riya_dave (8/2/2013)
i am trying to explain it here
Why do you find it so difficult to read the article about best practices? Why is it so difficult to take a few minutes of your time to put together something that we can actually use to write some code with to help you with your problem? This is really NOT a difficult query to write. It would take Lowell or I probably less than 5 minutes to put together a fully functional query that does exactly what you want. The problem is that writing a query requires 2 things: a table and data. You have so far provided neither of those things in anything resembling a format that is easy to use.
Maybe the issue here is a language barrier. That is not really a problem. We help people that speak many different languages on a regular basis. How is that we help all those other people so easily? They post ddl and sample in the language that ALL of us on this site have in common. SQL.
Post your two tables in a format that we can use and also what you expect as the output based on your sample data. If you had done that yesterday this issue would be far behind you and you could get on with your next issue.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2013 at 12:35 pm
HERE YOU GO
CREATE TABLE CustomCode
(ID int,
pcondition varchar(50),
Pinten int,
scondition varchar(50),
sinten int)
INSERT into CustomCode
SELECT 1,'HF',1,NULL,NULL
UNION
SELECT 1,'CA',1,NULL,NULL
UNION
SELECT 1,'AF',1,NULL,NULL
UNION
SELECT 2,'CAD',2,null,NULL
UNION
SELECT 2,'AST',2,null,null
UNION
SELECT 3,'CA',1,NULL,NULL
UNION
SELECT 4,NULL,NULL,'HF',1
Order to Update :
If ID has more than one pcondition
one of them will be pconditrion and pinten
other will be scondition and sinten
Based on followind order
1 - AF
2- CA
3 - CAD
4 - AST
5- hf
Output :
IDpconditionPintensconditionsinten
1AF 1NULL NULL
1NULL NULLCA 1
1NULL NULL HF 1
2NULL NULL AST 2
2CAD 2NULL NULL
3CA 1 NULL NULL
4NULL NULLHF1
August 2, 2013 at 1:00 pm
Thanks! That makes it so that those of us helping have enough information to work with. I took the liberty of turning your sort logic into a table too so you can actually do something with it.
create table #SortSomething
(
pcondition varchar(3),
SortOrder int
)
insert #SortSomething(SortOrder, pcondition)
select 1, 'AF' union all
select 2, 'CA' union all
select 3, 'CAD' union all
select 4, 'AST' union all
select 5, 'hf'
;with SortedData as
(
select cc.*, s.SortOrder, ROW_NUMBER() over(partition by ID order by s.SortOrder) as RowNum
from CustomCode cc
left join #SortSomething s on cc.pcondition = s.pcondition
)
select ID,
case when RowNum = 1 AND SortOrder is not null then pcondition else null end as pcondition,
case when RowNum = 1 AND SortOrder is not null then Pinten else null end as Pinten,
case when RowNum > 1 then pcondition
when RowNum = 1 AND SortOrder is null then scondition
else null end as scondition,
case when RowNum > 1 then Pinten
when RowNum = 1 AND SortOrder is null then sinten
else null end as sinten
from SortedData sd
order by sd.ID, RowNum
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2013 at 1:16 pm
but i dont need select ,i just need update statement, the store proc should not have select
August 2, 2013 at 1:26 pm
riya_dave (8/2/2013)
but i dont need select ,i just need update statement, the store proc should not have select
So turn the select into an update. 😉
;with SortedData as
(
select cc.*, s.SortOrder, ROW_NUMBER() over(partition by ID order by s.SortOrder) as RowNum
from CustomCode cc
left join #SortSomething s on cc.pcondition = s.pcondition
)
update SortedData
set pcondition = case when RowNum = 1 AND SortOrder is not null then pcondition else null end,
Pinten = case when RowNum = 1 AND SortOrder is not null then Pinten else null end,
scondition = case when RowNum > 1 then pcondition
when RowNum = 1 AND SortOrder is null then scondition
else null end ,
sinten = case when RowNum > 1 then Pinten
when RowNum = 1 AND SortOrder is null then sinten
else null end
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2013 at 1:57 pm
u r updating cte i need to update customecode table
August 2, 2013 at 2:05 pm
this code is not working
it snot necessary that all 5 condition will exists for that particular ID, there can be 3 or 4 ,not all 5
in that case it has to go with order
August 2, 2013 at 2:35 pm
IF YOU EXECUTING IT SECOND TIME it moves all the values to scondition
also if there is duplicate example
id pcondition scondition
1 sa null
1 sa null
it should not do anything coz its same pcondition , but the code moves it too
August 2, 2013 at 2:35 pm
my best guess; not sure what you expect on the last record, it doesn't seem to follow the right pattern, but at elast with a framework like this, oyu could modify it to fix that edge case:
;WITH RequiredOrder
AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY MyTarget.ID ORDER BY CASE WHEN pcondition='AF' THEN 1
WHEN pcondition='CA' THEN 2
WHEN pcondition='CAD' THEN 3
WHEN pcondition='AST' THEN 4
WHEN pcondition='HF' THEN 5
ELSE 6
END) AS RW,
MyTarget.ID,
MyTarget.pcondition,
CASE
WHEN pcondition='AF' THEN 1
WHEN pcondition='CA' THEN 2
WHEN pcondition='CAD' THEN 3
WHEN pcondition='AST' THEN 4
WHEN pcondition='HF' THEN 5
ELSE 6
END AS Indicator,
MyTarget.Pinten,
MyTarget.scondition,
MyTarget.sinten
FROM CustomCode MyTarget)
SELECT
RW,
ID,
CASE WHEN RW = 1 THEN pcondition ELSE NULL END AS Newpcondition,
CASE WHEN RW = 1 THEN Pinten ELSE NULL END AS NewPinten,
CASE WHEN RW = 1 THEN NULL ELSE scondition END AS Newscondition,
CASE WHEN RW = 1 THEN NULL ELSE sinten END AS Newsinten
FROM RequiredOrder ORDER BY ID,RW
Lowell
August 2, 2013 at 2:47 pm
riya_dave (8/2/2013)
u r updating cte i need to update customecode table
Did you actually notice what happens when you do that? It updates the base table. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2013 at 2:48 pm
riya_dave (8/2/2013)
IF YOU EXECUTING IT SECOND TIME it moves all the values to sconditionalso if there is duplicate example
id pcondition scondition
1 sa null
1 sa null
it should not do anything coz its same pcondition , but the code moves it too
Are there anymore rules that you have not yet mentioned? This is like coding against a moving target.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 2, 2013 at 2:57 pm
no there is no more rule, but if you execute is second time it moves everything to scondition
and also there may be duplicate values
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply