January 20, 2011 at 10:38 am
declare @T table (i int,p varchar(5),status varchar(10),amt decimal(10,2))
insert into @T
values(1,'A','T',100),
(1,'B','T',100),
(1,'C','T',100),
(1,'A','F',100),
(1,'B','F',100),
(1,'C','F',100),
(2,'A','T',10),
(2,'B','T',10),
(2,'C','T',10),
(2,'A','F',10),
(2,'B','F',10),
(2,'C','F',10),
(3,'A','T',100),
(3,'B','T',100),
(3,'C','T',100),
(4,'A','T',10),
(4,'B','T',10),
(4,'C','T',10),
(4,'A','F',10),
(5,'A','T',10),
(5,'B','T',10),
(5,'C','F',10),
(5,'C','F',10),
(6,'A','T',0),
(6,'B','T',0),
(6,'C','T',0),
(6,'A','F',10),
(6,'B','F',10),
(6,'C','F',10)
-- Select * from @T
declare @T1 table (i int,p varchar(5),status varchar(10),Tamt decimal(10,2),Famt decimal(10,2))
insert into @T1
values(1,'A','T',100,100),
(1,'B','T',100,100),
(1,'C','T',100,100),
(2,'A','T',10,10),
(2,'A','T',10,10),
(2,'A','T',10,10),
(3,'A','T',100,0),
(3,'B','T',100,0),
(3,'C','T',100,0),
(4,'A','T',10,10),
(4,'B','T',10,0),
(4,'C','T',10,0),
(5,'A','T',10,0),
(5,'B','T',10,0),
(5,'C','F',10,10),
(6,'A','T',0,10),
(6,'B','T',0,10),
(6,'C','T',0,10)
Select * from @T1
I need to get amt from @Temp as Tamt and Famt as @Temp1
for 1 and 2 there is no problem
for 3 'T' is 4 and no 'F' even though i need to get 0 there
for 4 3'T' and 1 'F' is there then 10 , 0 , 0 should come in appro column of Famt
for 5 2'T' is there then 0 , 0 should come in appro column of Tamt and 2 'F' 10,10
for 6 3'T' are there and 2 'F' which is vise versa of 1 ... this is just sameple i need to get as @Temp1
Note: i need without using subquery or correlated subquery
Thanks
Parthi
Thanks
Parthi
January 20, 2011 at 10:50 am
Please post your expected result either as a list of SELECT statements or as a comma separated list.
Your verbal description is rather confusing.
And why no subqry or CTE?
January 20, 2011 at 10:53 am
Hi,
@Temp is actual i need as @Temp1
Thanks
Parthi
Thanks
Parthi
January 20, 2011 at 11:36 am
There are several issues being unclear:
1) Why are there three rows for 5,C,F in @T but none for 5,C,T?
2) Why is the row for 5,C,F in @T and not 5,C,T?
3) Why are there three rows for 2,A,T in @T1 but none for 2,B,T and 2,C,T?
The requirement is still unclear. But it seems like a CrossTab would help here. Please have a look at the related link in my signature and give it a try. Post back where you get stuck.
January 20, 2011 at 12:02 pm
LutzM (1/20/2011)
There are several issues being unclear:1) Why are there three rows for 5,C,F in @T but none for 5,C,T?
2) Why is the row for 5,C,F in @T and not 5,C,T?
3) Why are there three rows for 2,A,T in @T1 but none for 2,B,T and 2,C,T?
The requirement is still unclear. But it seems like a CrossTab would help here. Please have a look at the related link in my signature and give it a try. Post back where you get stuck.
1) Why are there three rows for 5,C,F in @T but none for 5,C,T?
2) Why is the row for 5,C,F in @T and not 5,C,T?
Here there may be some time data or may not
5AT
5BT
data is there but not
5AF
5BF
it is having (Wrong in first post)
5CT
5CF
3) Why are there three rows for 2,A,T in @T1 but none for 2,B,T and 2,C,T
Sorry for 2 it is wrong my mistake
2AT10.0010.00
2AT10.0010.00
2AT10.0010.00
it should be
2AT10.0010.00
2BT10.0010.00
2CT10.0010.00
We can use CTE or some other method other than Correlated subquery or subquery.
I think you are about to get my point. Getting some idea on it thanks for correcting me on 2 🙂
Thanks
Parthi
Thanks
Parthi
January 20, 2011 at 12:10 pm
I see your point. Therefore I think using the CrossTab method would help here.
So I recommended to read about it and give it a try. 😉
January 20, 2011 at 12:25 pm
Hi
I dont think so Cross tab will help here since i dont want data in horizontal or vertical i need just to add one column (Need Column4 value as Column4 and Column5 ) any thing like join or cte will be able to do this kind of work ?i dont know
Thanks
Parthi
Thanks
Parthi
January 20, 2011 at 12:56 pm
parthi-1705 (1/20/2011)
HiI dont think so Cross tab will help here since i dont want data in horizontal or vertical i need just to add one column (Need Column4 value as Column4 and Column5 ) any thing like join or cte will be able to do this kind of work ?i dont know
Thanks
Parthi
So you don't want to split the values of amt into two separate columns based on the value of the status column (= pivoting the data)? Now I'm lost...
January 20, 2011 at 1:06 pm
So you don't want to split the values of amt into two separate columns based on the value of the status column (= pivoting the data)? Now I'm lost...
Yes i need to split amt to Tamt and Famt based on Column P and Column Status ,but how cum pvt can do this ,whether we need to hard-cord the data or how :w00t: :w00t:
My aim is to get something as
i p status Tamt Famt
----------- ----- ---------- ---------------------------------------
1 A T 100.00 100.00
1 B T 100.00 100.00
1 C T 100.00 100.00
Thanks
Parthi
Thanks
Parthi
January 20, 2011 at 1:14 pm
Did you actually read the article and try to follow the concept behind it?
Here's the crosstab version for your scenario.
SELECT
i,
p,
MAX(status) AS status,
MAX(CASE WHEN status='T' THEN amt ELSE 0 END) AS Tamt,
MAX(CASE WHEN status='F' THEN amt ELSE 0 END) AS Famt
FROM @T
GROUP BY i,p
ORDER BY i,p
January 20, 2011 at 2:02 pm
Hi
Thanks for your replay.I think this what i need now i get confused with my exact req,but this is what i need as the o/p as i specified once again a spl thanks for u to clear this.
Thanks
Parthi
Thanks
Parthi
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply