Select from different Table to 1 result

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

Select from different Table to 1 result

Author

Topic

amran
Starting Member

3 Posts

Posted - 2011-03-03 : 05:09:06

Hi All

I'm new in SQL, need some help on below Query

I need to Query data from a few different database
for normal select statement I can do it, problem is I not sure how to split data from 1 of the table to be in 1 Column

I got a few hundred line of data in few database that I need to use the Microsoft Query to extract it to Excel

sample data as below

Table1
key name address
1 joe USA
2 jamie UK

Table2
key type tel
1 OFF 11111
1 HP 22222
1 FAX 33333
2 OFF 44444
2 HP 55555
2 FAX 66666

Table3
Key Company Position
1 Alpha Manager
2 Bravo Executive

end Result should be as below

Query Result
key name address Company Position OFF HP FAX
1 joe USA Alpha Manager 11111 22222 33333
2 Jamie UK Bravo Executive 44444 55555 66666

Appreciate you guys can help me out

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 05:21:48

are you using SQL 2005 / 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2011-03-03 : 05:27:10

Value in column type are fixed ?
like OFF, HP, FAX only three values or may be more than that ?

Vaibhav T

If I cant go back, I want to go fast...

amran
Starting Member

3 Posts

Posted - 2011-03-03 : 20:11:03

Hi

I'm Using SQL 2008

The Value in Column "Type" is fix.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-03 : 22:29:31

[code]
select *
from (
select t1.[key], t1.[name], t1.[address], t3.[Company], t3.[Position], t2.[type], t2.[tel]
from Table1 t1
inner join Table3 t3 on t1.[key] = t3.[key]
inner join Table2 t2 on t1.[key] = t2.[key]
) d
pivot
(
max([tel])
for [type] in ([OFF], [HP], [FAX])
)p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

amran
Starting Member

3 Posts

Posted - 2011-03-04 : 02:53:38

Thanks

but I made a mistake, we do run sql server 2008, but the Database Compatibility is set to 80 (SQL Server 2000) for this Database, I don't think I can change this as it's the vendor Program/Database, worry after change the Level then the program don't work

did try your code in my Test Environment with that database level alter to 100, work perfectly

Do you have the code to do it in SQL 2000

Appreciate it

Thanks.

quote:

Originally posted by khtan


select	*
from	(
		select	t1.[key], t1.[name], t1.[address], t3.[Company], t3.[Position], t2.[type], t2.[tel]
		from	Table1 t1
			inner join Table3 t3	on	t1.[key]	= t3.[key]
			inner join Table2 t2	on	t1.[key]	= t2.[key]
	) d
	pivot
	(
		max([tel])
		for [type] in ([OFF], [HP], [FAX])
	)p

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-04 : 06:48:51

[code]
select t1.[key], t1.[name], t1.[address], t3.[Company], t3.[Position],
[OFF] = max(case when t2.[type] = 'OFF' then t2.[tel] end),
[HP] = max(case when t2.[type] = 'HP' then t2.[tel] end),
[FAX] = max(case when t2.[type] = 'FAX' then t2.[tel] end)
from Table1 t1
inner join Table3 t3 on t1.[key] = t3.[key]
inner join Table2 t2 on t1.[key] = t2.[key]
group by t1.[key], t1.[name], t1.[address], t3.[Company], t3.[Position]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources