Every 2 records returned in 1 record? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Every 2 records returned in 1 record?

Author

Topic

Sintwar
Starting Member

11 Posts

Posted - 2009-08-20 : 22:26:11

I need data from a table like this:

ID Item
-------------------
1 Car
2 House
3 Dog
4 Cat
5 Plane
6 Train

to be returned like this:

ID1 ID2 Item1 Item2
-----------------------------------------
1 2 Car House
3 4 Dog Cat
5 6 Plane Train

Is this possible? If so, how would this be done?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-20 : 22:33:05

[code]
DECLARE @sample TABLE
(
ID int,
Item varchar(5)
)
INSERT INTO @sample
SELECT 1, 'Car' UNION ALL
SELECT 2, 'House' UNION ALL
SELECT 3, 'Dog' UNION ALL
SELECT 4, 'Cat' UNION ALL
SELECT 5, 'Plane' UNION ALL
SELECT 6, 'Train'

SELECT ID1 = MIN(ID),
ID2 = MAX(ID),
Item1 = MAX(CASE WHEN ID % 2 = 1 THEN Item END),
Item2 = MAX(CASE WHEN ID % 2 = 0 THEN Item END)
FROM @sample
GROUP BY (ID - 1)/ 2
/*
ID1 ID2 Item1 Item2
----------- ----------- ----- -----
1 2 Car House
3 4 Dog Cat
5 6 Plane Train

(3 row(s) affected)
*/
[/code]

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

Sintwar
Starting Member

11 Posts

Posted - 2009-08-20 : 23:29:22

Cool khtan thanks!

I ran into a problem with this method though. My ID's are not going to be in perfect order, so for example, if the ID order is 1,4,6,20,22,30, etc. instead of 1,2,3,4,5,6,etc., it breaks your script.

I have attempted to remedy this by first creating a temporary table with it's own identity ID column, inserting the original data into that table, and then using your select command to extract the data from the temp table.

Do you think this is a bit excessive? Or is there a better way to remedy this.

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-20 : 23:33:51

temp table is probably the best way for you. Unless you are using SQL 2005 / 2008 then you can make use of the row_number() function

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

Sintwar
Starting Member

11 Posts

Posted - 2009-08-20 : 23:44:51

Awesome. Works for me. Thank for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources