April 28, 2010 at 10:07 pm
Hello!
I'm having problem to create a query in SQL Server 2000 and definitely need help to solve it.
The following table has 17 million lines. :w00t:
CREATE TABLE Problem
(
id VARCHAR(1),
record VARCHAR(1)
)
INSERT INTO Problem
SELECT '1','A'
UNION ALL
SELECT '1','B'
UNION ALL
SELECT '1','C'
UNION ALL
SELECT '2','Y'
UNION ALL
SELECT '2','Z'
UNION ALL
SELECT '3','A'
UNION ALL
SELECT '3','B'
UNION ALL
SELECT '3','C'
UNION ALL
SELECT '3','D'
id record
---- ------
1 A
1 B
1 C
2 Y
2 Z
3 A
3 B
3 C
3 D
(9 row(s) affected)
I need to get the following output :unsure:
id c1 c2
---- ---- ----
1 A B
1 B C
2 Y Z
3 A B
3 B C
3 C D
Any help would be great because I'm stuck in this problem!
Thanks!
April 29, 2010 at 10:52 am
Nobody to help me solve this problem? Please help!
I forgot to inform you that the table has a ascending non-sequencial column for each ID group. This can help?
CREATE TABLE Problem
(
id VARCHAR(1),
record VARCHAR(1),
seq int
)
INSERT INTO Problem
SELECT '1','A','2'
UNION ALL
SELECT '1','B','6'
UNION ALL
SELECT '1','C','9'
UNION ALL
SELECT '2','Y','2'
UNION ALL
SELECT '2','Z','8'
UNION ALL
SELECT '3','A','3'
UNION ALL
SELECT '3','B','5'
UNION ALL
SELECT '3','C','6'
UNION ALL
SELECT '3','D','9'
id record seq
---- ------ -----------
1 A 2
1 B 6
1 C 9
2 Y 2
2 Z 8
3 A 3
3 B 5
3 C 6
3 D 9
(9 row(s) affected)
Thanks!
April 29, 2010 at 11:51 am
This code uses an in-frequently used form of the UPDATE statement. Read the article mentioned in the code, and understand it (especially the rules) before putting this code into a production system.
if OBJECT_ID('dbo.Problem') IS NOT NULL DROP TABLE dbo.problem
GO
CREATE TABLE dbo.Problem
(
id VARCHAR(1) NOT NULL,
record VARCHAR(1) NOT NULL,
RowNum int, -- add a column to hold sequential row number. It will be partitioned by the id.
-- need a clustered index for the quirky update that will be performed shortly!
CONSTRAINT [PK_Problem] PRIMARY KEY CLUSTERED (id, record)
)
INSERT INTO Problem (id, record)
SELECT '1','A'
UNION ALL
SELECT '1','B'
UNION ALL
SELECT '1','C'
UNION ALL
SELECT '2','Y'
UNION ALL
SELECT '2','Z'
UNION ALL
SELECT '3','A'
UNION ALL
SELECT '3','B'
UNION ALL
SELECT '3','C'
UNION ALL
SELECT '3','D'
-- need some variables for the quirky update
declare @RowNum int,
@id varchar(1)
set @RowNum = 0
set @id = ''
-- This form of the UPDATE statement has some particular rules.
-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
-- for a complete discussion of how this works, and all of the rules for utilizing it.
UPDATE Problem
SET @RowNum = CASE WHEN id <> @id THEN 1 ELSE @RowNum + 1 END,
RowNum = @RowNum,
@id = ID
FROM dbo.Problem WITH (TABLOCKX) -- <<<< REQUIRED
OPTION (MAXDOP 1) -- <<<< REQUIRED
SELECT p1.id,
c1 = p1.record,
c2 = p2.record
FROM dbo.Problem p1
JOIN dbo.Problem p2
ON p1.id = p2.id
AND p2.RowNum = p1.RowNum + 1
This simpler code can be used when you finally migrate to sql 2005 or higher. Note that you no longer need the clustered index, the additional column, or need to use the quirky update.
-- if you migrate to sql 2005+, you can use this instead.
-- No need to change the table structure, add a clustered index,
-- or do the quirky update!
;WITH CTE AS
(
SELECT id,
record,
RowNum = ROW_NUMBER() OVER (PARTITION BY id ORDER BY record)
FROM dbo.Problem
)
select p1.id,
c1 = p1.record,
c2 = p2.record
FROM CTE p1
JOIN CTE p2
ON p1.id = p2.id
AND p2.RowNum = p1.RowNum + 1
Edit: Note that if you have a record without a following one of the same id, these do NOT show anything for that record. i.e. if you add to your test data:
UNION ALL
SELECT '4', 'A'
nothing will be produced for id '4'
Edit2: corrected name misspelling
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy