tsql help - 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 2008 Forums

Transact-SQL (2008)

tsql help

Author

Topic

tishri
Yak Posting Veteran

95 Posts

Posted - 2011-12-30 : 02:43:32

hi guys,

i need help with this one...



TABLE tblpersons
[personID]	[name]			[address]		[sex]		[dateofbirth]	
1001		Clarin, Tishri		Pasig, Metro Manila	Male		10/29/1983
1002		Sample, Name		My Sample Address	Male		11/11/2000

TABLE [tblotherinfo]
	[otherInfoID]	[personid]	[info]		[value]	
	11		1001		SSID		1298-2342-23423
	12		1001		EMAIL		tishriclarin@gmail.com
	13		1002		EMAIL		sample@email.com


EXPECTED QUERY OUTPUT

[personID]	[name]			[address]		[sex]		[dateofbirth]		[SSID]			[EMAIL]
1001		Clarin, Tishri		Pasig, Metro Manila	Male		10/29/1983		1298-2342-23423		tishriclarin@gmail.com
1002		Sample, Name		My Sample Address	Male		11/11/2000		NULL			sample@email.com


help is greatly appreciated...

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-30 : 02:58:23

have a look at the PIVOT operator

http://msdn.microsoft.com/en-us/library/ms177410.aspx

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

tishri
Yak Posting Veteran

95 Posts

Posted - 2011-12-30 : 03:15:21

thanks.. great help

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-30 : 03:27:22

welcome

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

tishri
Yak Posting Veteran

95 Posts

Posted - 2011-12-31 : 03:39:09

Hi khtan,
I'm really having some issues with PIVOT and UNPIVOT, is there a solution or alternative to this function. Because I'm using an old version of SQL server. We cannot easily upgrade our SQL server due to license cost of the new version of SQL server.

Please help. Thanks

TCC

brian147
Starting Member

10 Posts

Posted - 2011-12-31 : 04:50:46

Not sure if this will work in your version, but try this:

select p.* , ssid.value as SSID, email.value as EMAIL
from tblPersons p
outer apply (select [value] from tblOtherinfo o where o.personID = p.personID and o.info = 'SSID') as ssid
outer apply (select [value] from tblOtherinfo o where o.personID = p.personID and o.info = 'EMAIL') as email

tishri
Yak Posting Veteran

95 Posts

Posted - 2012-01-01 : 00:52:20

here's the problem. TABLE [tblotherinfo] field [info] is dynamic. this means that this may contain other values... anyway thanks

TCC

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-01-01 : 01:41:03

what version of SQL Server are you using ?

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

whatamouth
Starting Member

16 Posts

Posted - 2012-01-05 : 09:37:50

quote:
Originally posted by brian147

Not sure if this will work in your version, but try this:

select p.* , ssid.value as SSID, email.value as EMAIL
from tblPersons p
outer apply (select [value] from tblOtherinfo o where o.personID = p.personID and o.info = 'SSID') as ssid
outer apply (select [value] from tblOtherinfo o where o.personID = p.personID and o.info = 'EMAIL') as email

this should work

If it's not the case, try converting tblOtherinfo table to its 3NF.

Neil Matias
neilmatias@yahoo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources