Only the 1st Value? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Only the 1st Value?

Author

Topic

Shanew
Starting Member

20 Posts

Posted - 2009-06-04 : 20:12:57

Hello,

I have table that looks like this:
-Table1-
NAME | NUM1 | NUM2 | NUM3
JEFF - 595 - NULL - NULL
JEFF - 599 - NULL - NULL
JEFF - 599 - 789 - NULL
JEFF - 599 - NULL - NULL
JEFF - 788 - 595 - NULL
JEFF - 784 - NULL - 328
Bill - 895 - NULL - NULL
Bill - 449 - NULL - 472
Bill - 119 - 239 - NULL

How can I query it so that i would get the 1st value found in each NUM filed on just one line per NAME.

So if a NUM filed has more the one value in the full list that is GROUP BY NAME filed it will use the value found in the first record?

OUTPUT should look like this.

JEFF - 595 - 789 - 328
Bill - 895 - 239 - 472

Thanks for all the help!
Shane

Shane Weddle
www.TechKnowPros.com

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-06-04 : 21:37:59

This can use some tweaking, but it should get you started.



declare @MyTable table (name varchar(15),num1 int,num2 int,num3 int)
insert into @MyTable(Name,num1,num2,num3)
select 'JEFF' , 595 , NULL , NULL Union all
select 'JEFF' , 599 , NULL , NULL Union all
select 'JEFF' , 599 , 789 , NULL Union all
select 'JEFF' , 599 , NULL , NULL Union all
select 'JEFF' , 788 , 595 , NULL Union all
select 'JEFF' , 784 , NULL , 328 Union all
select 'Bill' , 895 , NULL , NULL Union all
select 'Bill' , 449 , NULL , 472 Union all
select 'Bill' , 119 , 239 , NULL 



select a.Name,a.Num1,b.Num2,c.num3
from
(Select row_Number() over (Partition by name order by name) as rowID,*
from @mytable
where not num1 is null) a
Left Join
(Select row_Number() over (Partition by name order by name) as rowID,*
from @mytable
where not num2 is null
) b
on a.RowID = b.RowID
and a.Name = b.Name
Left join
(Select row_Number() over (Partition by name order by name) as rowID,*
from @mytable
where not num3 is null) c
on a.RowID = c.RowID
and a.Name = c.Name
where a.RowID =1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-05 : 02:33:33

select Name,max(num1) as num1,max(num2) as num2,max(num3) as num3 from @mytable
group by Name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources