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)
 an specific query with alias

Author  Topic 

sebastian11c
Posting Yak Master

129 Posts

Posted - 2013-08-22 : 19:07:50
hi there

one more time i need your help

i got 2 tables ( info an alias)


table info



CREATE TABLE [dbo].[Info](
 [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](50) NOT NULL,
[phonenumber] [int] NOT NULL,
CONSTRAINT [PK_Info] PRIMARY KEY CLUSTERED
(
 ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF



lets put one record on it


INSERT INTO [Pruebas].[dbo].[Info]
([name]
,[phonenumber])
VALUES
('john'
,4445555)
GO



a top (1) select

code name phonenumber
1 john 4445555



now the second table "alias"




CREATE TABLE [dbo].[alias](
[fullname] [varchar](50) NOT NULL,
[alias] [varchar](50) NOT NULL,
CONSTRAINT [PK_alias] PRIMARY KEY CLUSTERED
(
[fullname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


lets put 3 records on it



[code]INSERT INTO [Pruebas].[dbo].[alias]
([fullname]
,[alias])
VALUES
('code'
,'cd')
GO
INSERT INTO [Pruebas].[dbo].[alias]
([fullname]
,[alias])
VALUES
('name'
,'nm')

gO
INSERT INTO [Pruebas].[dbo].[alias]
([fullname]
,[alias])
VALUES
('phonenumber'
,'pn')



i need to cross these 2 tables to get this result


RESULT TABLE

[code]

ALIAS INFO
cd 1
nm john
pn 4445555



i really need your help, i have no idea how to do it

many thanks in advanced

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-08-22 : 19:30:21
Your schema needs adjusting. The final output listed has an identity, string and integer all being treated as the same type of data. Your Alias table would need to know (a priori) the names of the columns in the Info table. While it would be possible to convert all of these to a common type (nvarchar(max)?), it doesn't seem worth the effort. What are you actually trying to get accomplished? Perhaps if you can elaborate a bit, we can find a solution.

=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Go to Top of Page

sebastian11c
Posting Yak Master

129 Posts

Posted - 2013-08-22 : 20:17:38
thanks bustaz for your answer

all of these can be convert to a common type (nvarchar(max), so any idea how to achieve that??

im trying to build an string in visual studio like this &cd=1&pn=4445555&nm=john

and thanks again
Go to Top of Page
   

- Advertisement -