how to make 2 rows appear in 1 in result query

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

how to make 2 rows appear in 1 in result query

Author

Topic

chessmonster
Starting Member

6 Posts

Posted - 2011-04-28 : 08:43:02

hi!

i was wondering what are the things that i need to see for me to make a query that can make 2 rows from a source table appear in 1 row in a result query.

like I have a table that has columns like, id-name-type. the type can be father and son. what I would like to do is to get the rows with the same id and type in 1 column. so I can have father and son in 1 column.

what are the things that I can use to make this happen? my instructor said I can try to use inner joins. what about other options?

any suggestions would be appreciated

toughness and stubborness

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2011-04-28 : 10:11:19

A table can join on itself. Can you give us an idea of what columns are in the table and what sort of data is in it?

Hey, it compiles.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-28 : 10:23:21

You can use the CASE statement or PIVOT

Jim

Everyday I learn something that somebody else already knew

chessmonster
Starting Member

6 Posts

Posted - 2011-04-28 : 12:27:08

hey poolman,

like the columns are.

id, name, type.

and sample rows would be

(the first one)
1, sonny, son.
(second row would be)
1, daddy, dad

i wish to have a result set like

1, sonny, daddy

rows with same id, I would like to join.
hey jim, I will also look at those things you said there

thanks for all the replies. :D

toughness and stubborness

chessmonster
Starting Member

6 Posts

Posted - 2011-04-30 : 05:17:34

hi, I already did it using the inner join and aliases

thanks for the suggestions anyway :D a
appreciate it.
regards,
simon

toughness and stubborness

raghuveer125
Constraint Violating Yak Guru

285 Posts

Posted - 2011-05-02 : 03:58:13

May be this will help.......

DECLARE @T TABLE (id INT, name NVARCHAR(23), type NVARCHAR(23))
INSERT INTO @T
SELECT 1, 'sonny', 'son' UNION ALL
SELECT 1, 'daddy', 'dad' UNION ALL
SELECT 2, 'HI','tHINK' UNION ALL
SELECT 2,'THINK','HI'

SELECT * FROM (SELECT DISTINCT A.ID,A.NAME as FNAME,B.NAME AS BNAME,ROW=ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY B.ID) FROM @T AS B,@T AS A WHERE A.NAME<>B.NAME AND A.ID=B.ID)AS TT where ROW=1

SELECT * FROM (SELECT DISTINCT A.ID,A.NAME+' '+B.NAME AS NAME,ROW=ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY B.ID) FROM @T AS B,@T AS A WHERE A.NAME<>B.NAME AND A.ID=B.ID)AS TT where ROW=1

Raghu' S

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources