help about sql statement ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

help about sql statement ?

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2007-01-15 : 21:55:57

Hi all,
I have two table with structure :
Table1:
IDA Name
1 ---A
2 ---B
3 ---C
Table2:
IDA NameR Result
1-----T1----10
1-----L1----9
1-----Y1----3
2-----T1----7
2-----L1----2
2-----Y1----8
3-----T1----2
3-----L1----4
3-----Y1----8

My problem :I need to view,
IDA Name T1 L1 Y1
1---A----10-9--3
2---B----7--2--8
3---C----2--4--8

Thank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-15 : 22:02:29

[code]
select t1.IDA, t2.Name,
max(case when NameR = 'T1' then Result end) as [T1],
max(case when NameR = 'L1' then Result end) as [L1],
max(case when NameR = 'Y1' then Result end) as [Y1]
from Table1 t1 inner join Table2 t2
on t1.IDA = t2.IDA
group by t1.IDA, t2.Name
[/code]

pamyral_279
Posting Yak Master

143 Posts

Posted - 2007-01-15 : 22:28:36

quote:

Originally posted by khtan


select t1.IDA, t2.Name,
       max(case when NameR = 'T1' then Result end) as [T1],
       max(case when NameR = 'L1' then Result end) as [L1],
       max(case when NameR = 'Y1' then Result end) as [Y1]
from   Table1 t1 inner join Table2 t2
       on   t1.IDA = t2.IDA
group by t1.IDA, t2.Name

t2.Name is not found in my table2.
Can you post again ? Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-15 : 22:37:28

[code]
select t1.IDA, t1.Name,
max(case when NameR = 'T1' then Result end) as [T1],
max(case when NameR = 'L1' then Result end) as [L1],
max(case when NameR = 'Y1' then Result end) as [Y1]
from Table1 t1 inner join Table2 t2
on t1.IDA = t2.IDA
group by t1.IDA, t1.Name
[/code]

pamyral_279
Posting Yak Master

143 Posts

Posted - 2007-01-15 : 23:01:28

quote:

Originally posted by khtan


select t1.IDA, t1.Name,
       max(case when NameR = 'T1' then Result end) as [T1],
       max(case when NameR = 'L1' then Result end) as [L1],
       max(case when NameR = 'Y1' then Result end) as [Y1]
from   Table1 t1 inner join Table2 t2
       on   t1.IDA = t2.IDA
group by t1.IDA, t1.Name

It's nice.Thank you very much.
Can you explain for me a little above statement ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 00:50:17

What does your instinct tell you?
How do you think the query works?

Peter Larsson
Helsingborg, Sweden

pamyral_279
Posting Yak Master

143 Posts

Posted - 2007-01-16 : 01:25:25

quote:
Originally posted by Peso

What does your instinct tell you?
How do you think the query works?

Peter Larsson
Helsingborg, Sweden

I am really begginer sql ! I only know basic sql .But complex statement sql help me a lot in programming.If may,help understand it.
Forum is good place to learn.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-16 : 01:38:36

This is not a very complex query.
How do you think your understanding of SQL will improve, if you do not gve it a try?
Or do you prefer to get all explanation on a silver platter, and learn nothing?

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-16 : 01:46:22

Run the queries below and see the result for each query & the corresponding result. You may want to mask out all and run one at a time. It should help you understand better.


declare @table1 table
(
	IDA	int,
	[Name]	varchar(10)
)

declare @table2 table
(
	IDA 	int,
	NameR 	varchar(10),
	Result	int
)

insert into @table1
select	1, 'A'	union all
select	2, 'B'	union all
select	3, 'C'

insert into @table2
select	1, 'T1', 10	union all
select	1, 'L1', 9	union all
select	1, 'Y1', 3	union all
select	2, 'T1', 7	union all
select	2, 'L1', 2	union all
select	2, 'Y1', 8	union all
select	3, 'T1', 2	union all
select	3, 'L1', 4	union all
select	3, 'Y1', 8

-- Query 1
select t1.IDA, t1.Name, t2.NameR, t2.Result
from   @table1 t1 inner join @table2 t2
       on   t1.IDA = t2.IDA

-- Query 2
select t1.IDA, t1.Name,
       case when NameR = 'T1' then Result end as [T1],
       case when NameR = 'L1' then Result end as [L1],
       case when NameR = 'Y1' then Result end as [Y1]
from   @table1 t1 inner join @table2 t2
       on   t1.IDA = t2.IDA

-- Query 3
select t1.IDA, t1.Name
from   @table1 t1 inner join @table2 t2
       on   t1.IDA = t2.IDA
group by t1.IDA, t1.Name

-- Query 4 (Final)
select t1.IDA, t1.Name,
       max(case when NameR = 'T1' then Result end) as [T1],
       max(case when NameR = 'L1' then Result end) as [L1],
       max(case when NameR = 'Y1' then Result end) as [Y1]
from   @table1 t1 inner join @table2 t2
       on   t1.IDA = t2.IDA
group by t1.IDA, t1.Name

pamyral_279
Posting Yak Master

143 Posts

Posted - 2007-01-16 : 02:22:08

Thank khtan very very much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources