Select Statement Problem?

pinoyz

Hi every1,

Recently i have this exam about sql statements, it shows that it has 1 table like this one.

name telephone1 telephone2

ian 1234567 7654321

test 1111111 2222222

How can we make a select query where the output is this

name telephone_numbers

ian 1234567

7654321

test 1111111

2222222

pyeung

Try this:

Select [name], [telephone1] + ' ' + [telephone2] As [telephone_numbers] from [yourTableName]

Arnie Rowland

You have a table that has not been 'normalized', and as you can easily determine, it will often be difficult to work with. You would be far better off to have only one Telephone number column -adding as many rows per individual as needed.

For the purposes of your 'exam', here is one suggested solution:

SET NOCOUNT ON

DECLARE @MyTable table
( RowID int IDENTITY,
[Name] varchar(20),
Telephone1 varchar(20),
Telephone2 varchar(20)
)

INSERT INTO @MyTable VALUES ( 'ian', '1234567', '7654321' )
INSERT INTO @MyTable VALUES ( 'Sean', '1111111', '2222222' )

SELECT
[Name],
Telephone = Telephone1
FROM @MyTable

UNION

SELECT
[Name],
Telephone2
FROM @MyTable

Name Telephone
-------------------- --------------------
ian 1234567
ian 7654321
Sean 1111111
Sean 2222222

oj

A single pass through the table and just concatenate tel1 and tel2.

e.g.

Code Snippet

select name, telephone1 +isnull(char(13)+char(10)+telephone2,'') [telephone]

from tb

Manivannan.D.Sekaran

Here It is,

Code Snippet

Create Table #data (

[name] Varchar(100) ,

[telephone1] Varchar(100) ,

[telephone2] Varchar(100)

);

Insert Into #data Values('ian','1234567','7654321');

Insert Into #data Values('test','1111111','2222222');

Matt Tolhurst

Hi,

An alternatives to Arnie, although I think Arnie's is probably the easier and probably the one they are expecting in the exam

But:

CREATE TABLE pvt (name varchar(20), telephone1 varchar(20), telephone2 varchar(20))

INSERT INTO pvt VALUES ('ian','1234567', '7654321' )

INSERT INTO pvt VALUES ('sean','1111111', '2222222' )

SELECT Name, Line, Number

FROM

(SELECT Name, telephone1, telephone2

FROM pvt) p

UNPIVOT

(Number FOR Line IN

(telephone1, telephone2)

)AS unpvt

Result:

Name Line Number

ian telephone1 1234567
ian telephone2 7654321
sean telephone1 1111111
sean telephone2 2222222

Hope that helps

Matt