October 5, 2007 at 12:25 am
Hi everyone
I have two table
Table1
id date
101 10/01/2007
102 10/04/2007
103 10/05/2007
104 10/06/2007
Table 2
id Plan
101 A
101 B
102 A
102 B
102 C
102 D
103 C
103 D
104 A
i need a query to display the following result
id date A B C D
101 10/01/2007 Y Y N N
102 10/04/2007 Y Y Y Y
103 10/05/2007 N N Y Y
104 10/06/2007 Y N N N
thanks in advance
regards
sthanika
October 5, 2007 at 12:33 am
hey sthanika,
Use --> Case....when in Select statement to perform this task,
e.i :
Select x, y,
case z
when 'a' then 'b'
when 'c' then 'd'
from Tb1 Join Tb2
......
Please let me know, Is this working or not?
Cheers!
Sandy.
--
October 5, 2007 at 2:50 am
Hi
Still i am getting row wise only
thanks
sthanika
October 5, 2007 at 3:06 am
hi,
check this
CREATE TABLE #tb1
(
id varchar(10),
date datetime
)
----
INSERT INTO #tb1
SELECT 101,'01/01/2000'
UNION
SELECT 102,'01/01/2001'
union
SELECT 103,'01/01/2002'
union
SELECT 104,'01/01/2003'
----
CREATE TABLE #tb2
(
id1 varchar(10),
id_Plan varchar(10)
)
----
INSERT INTO #tb2
SELECT 101,'A'
UNION
SELECT 101,'B'
union
SELECT 102,'A'
union
SELECT 102,'B'
union
SELECT 102,'C'
union
SELECT 102,'D'
union
SELECT 103,'C'
union
SELECT 103,'D'
union
SELECT 104,'A'
-----
SELECT * FROM #tb1
SELECT * FROM #tb2
------
SELECT t.id, t.date,
(CASE id_plan WHEN 'A' THEN 'Y' ELSE 'N' END) AS A ,
(CASE id_plan WHEN 'B' THEN 'Y' ELSE 'N' END) AS B,
(CASE id_plan WHEN 'C' THEN 'Y' ELSE 'N' END) AS C,
(CASE id_plan WHEN 'D' THEN 'Y' ELSE 'N' END) AS D
FROM #tb1 t
LEFT OUTER JOIN #tb2 t2
ON t.id = t2.id1
---
Next-->
Create a temp Table and filter the Data as per your requirement.
Cheers! 🙂
Sandy.
--
October 5, 2007 at 4:45 am
Hi
I tried with you example and i am getting the following result
1012000-01-01 00:00:00.000YNNN
1012000-01-01 00:00:00.000NYNN
1022001-01-01 00:00:00.000YNNN
1022001-01-01 00:00:00.000NYNN
1022001-01-01 00:00:00.000NNYN
1022001-01-01 00:00:00.000NNNY
1032002-01-01 00:00:00.000NNYN
1032002-01-01 00:00:00.000NNNY
1042003-01-01 00:00:00.000YNNN
but my resultset should be like
101 2000-01-01 Y Y N N
only one record for 101, 102, 103 ,104
and other values
regards
sthanika
October 9, 2007 at 5:23 pm
For simplicity sake, is this the output your looking for?
CREATE TABLE #tb1
(
id varchar(10),
date datetime
)
----
INSERT INTO #tb1
SELECT 101,'01/01/2000'
UNION
SELECT 102,'01/01/2001'
union
SELECT 103,'01/01/2002'
union
SELECT 104,'01/01/2003'
----
CREATE TABLE #tb2
(
id1 varchar(10),
id_Plan varchar(10)
)
----
INSERT INTO #tb2
SELECT 101,'A'
UNION
SELECT 101,'B'
union
SELECT 102,'A'
union
SELECT 102,'B'
union
SELECT 102,'C'
union
SELECT 102,'D'
union
SELECT 103,'C'
union
SELECT 103,'D'
union
SELECT 104,'A'
SELECT t.id, CONVERT(varchar(10),t.date ,101),
(CASE WHEN t2.id_plan IS NULL THEN 'N' ELSE 'Y' END) AS A ,
(CASE WHEN t3.id_plan IS NULL THEN 'N' ELSE 'Y' END) AS B ,
(CASE WHEN t4.id_plan IS NULL THEN 'N' ELSE 'Y' END) AS C ,
(CASE WHEN t5.id_plan IS NULL THEN 'N' ELSE 'Y' END) AS D
FROM #tb1 t
LEFT OUTER JOIN #tb2 t2 on
t.id = t2.id1 and t2.id_plan = 'A'
LEFT OUTER JOIN #tb2 t3 on
t.id = t3.id1 and t3.id_plan = 'B'
LEFT OUTER JOIN #tb2 t4 on
t.id = t4.id1 and t4.id_plan = 'C'
LEFT OUTER JOIN #tb2 t5 on
t.id = t5.id1 and t5.id_plan = 'D'
October 9, 2007 at 5:50 pm
Just add GROUP BY to Sandy's query:
SELECT t.id, t.date,
MAX((CASE id_plan WHEN 'A' THEN 'Y' ELSE 'N' END)) AS A ,
MAX((CASE id_plan WHEN 'B' THEN 'Y' ELSE 'N' END)) AS B,
MAX((CASE id_plan WHEN 'C' THEN 'Y' ELSE 'N' END)) AS C,
MAX((CASE id_plan WHEN 'D' THEN 'Y' ELSE 'N' END)) AS D
FROM #tb1 t
LEFT OUTER JOIN #tb2 t2
ON t.id = t2.id1
GROUP BY t.id, t.date
October 9, 2007 at 10:44 pm
Hey bbychkov,
Thanks for Helping in Script.
Cheers!
Have A Nice Day.
Sandy.
--
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply