Format Query Result - 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 2005 Forums

Transact-SQL (2005)

Format Query Result

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-01-14 : 01:19:53

Hi all,
I am using a qry to return the result as


Id Name Age
1  Dana  20
2  Sekar 10
3. Pod   40
.
.
10 Velan 40

How can retrive the record result as two rows combined into a single row

like


Id Name Age Id Name  Age
1  Dana  20 2  Sekar 10

Thanks in Advance
Dana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-14 : 02:00:47

How about ID 3 and the rest ? How do you want to show it ?

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

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-01-14 : 02:14:10

Dear Khtan,
Thanks for the reply. I want to join every two row into a single row

nr
SQLTeam MVY

12543 Posts

Posted - 2008-01-14 : 02:33:10

with a
as
(
select row_number() over(order by ID) as rownumber
...
from
...
)
select t1.*, t2.*
from a t1
left join a t2
on t1.rownumber + 1 = t2.rownumber
where
t1.rownumber % 2 = 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-14 : 02:33:28

Try this:-

;
With Your_CTE(RowNo,ID,Name,Age) AS
(SELECT ROW_NUMBER OVER (ORDER BY ID)+1 /2 AS RowNo,
ID,
Name,
Age
FROM Table)

SELECT t1.ID,t1.Name,t1.Age,t2.ID,t2.Name,t2.Age
FROM Your_CTE t1
INNER JOIN Your_CTE t2
ON t1.RowNo=t2.RowNo
ANd t1.ID <t2.ID

danasegarane76
Posting Yak Master

242 Posts

Posted - 2008-01-14 : 03:55:55

Thanks to Nr and visakh,
But I have posted this in wrong forum.I am using SQL 2000. How to do this in SQL 2000 ?

Dana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-14 : 04:24:29

[code]DECLARE @sample TABLE
(
[Id] int,
[Name] varchar(10),
Age int
)
INSERT INTO @sample
SELECT 1, 'Dana', 20 UNION ALL
SELECT 2, 'Sekar', 10 UNION ALL
SELECT 3, 'Pod', 40 UNION ALL
SELECT 4, 'Apple', 30 UNION ALL
SELECT 5, 'Orange',50

-- assuming that [Id] IS a continuous running number
SELECT [Id] = MAX(CASE WHEN ([Id]) % 2 = 1 THEN [Id] END),
[Name] = MAX(CASE WHEN ([Id]) % 2 = 1 THEN [Name] END),
[Age] = MAX(CASE WHEN ([Id]) % 2 = 1 THEN [Age] END),
[Id] = MAX(CASE WHEN ([Id]) % 2 = 0 THEN [Id] END),
[Name] = MAX(CASE WHEN ([Id]) % 2 = 0 THEN [Name] END),
[Age] = MAX(CASE WHEN ([Id]) % 2 = 0 THEN [Age] END)
FROM @sample
GROUP BY ([Id] - 1) / 2

/*
Id Name Age Id Name Age
----------- ---------- ----------- ----------- ---------- -----------
1 Dana 20 2 Sekar 10
3 Pod 40 4 Apple 30
5 Orange 50 NULL NULL NULL

(3 row(s) affected)
*/[/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