Moving values into the same row

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)

Moving values into the same row

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2008-01-29 : 20:08:27

Hello All,

I have the below table (before) that has Col3 and Col4 values in different row based on the Col2 and Col1 values. What I want is to have Col3 and Col4 values in the same row based on the values of Col2 and Col1 the results will be inserted into a new table. The “before” and “after” tables are shown below for illustration. Does anyone know how to accomplish this task? Please advice. Thanks.

BEFORE
Col1 Col2 Col3 Col4
1/7/2008 RED 13
1/8/2008 RED 16
1/8/2008 RED 6
1/9/2008 RED 22
1/9/2008 RED 10
1/7/2008 WHITE 6
1/7/2008 WHITE 2
1/8/2008 WHITE 5
1/9/2008 WHITE 2
1/10/2008 WHITE 3
1/7/2008 BLUE 20
1/7/2008 BLUE 6
1/8/2008 BLUE 11
1/8/2008 BLUE 5
1/9/2008 BLUE 15
1/9/2008 BLUE 8

AFTER
Col1 Col2 Col3 Col4
1/7/2008 RED 13
1/8/2008 RED 6 16
1/9/2008 RED 10 22
1/7/2008 WHITE 2 6
1/8/2008 WHITE 5
1/9/2008 WHITE 2
1/10/2008 WHITE 3
1/7/2008 BLUE 6 20
1/8/2008 BLUE 5 11
1/9/2008 BLUE 8 15

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2008-01-30 : 01:37:05

DECLARE @T TABLE (Col1 datetime, Col2 varchar(5), Col3 int)
INSERT INTo @T SELECT '1/7/2008', 'RED', 13
UNION ALL SELECT '1/8/2008', 'RED', 16
UNION ALL SELECT '1/8/2008', 'RED', 6
UNION ALL SELECT '1/9/2008', 'RED', 22
UNION ALL SELECT '1/9/2008', 'RED', 10
UNION ALL SELECT '1/7/2008', 'WHITE', 6
UNION ALL SELECT '1/7/2008', 'WHITE', 2
UNION ALL SELECT '1/8/2008', 'WHITE', 5
UNION ALL SELECT '1/9/2008', 'WHITE', 2
UNION ALL SELECT '1/10/2008', 'WHITE', 3
UNION ALL SELECT '1/7/2008', 'BLUE', 20
UNION ALL SELECT '1/7/2008', 'BLUE', 6
UNION ALL SELECT '1/8/2008', 'BLUE', 11
UNION ALL SELECT '1/8/2008', 'BLUE', 5
UNION ALL SELECT '1/9/2008', 'BLUE', 15
UNION ALL SELECT '1/9/2008', 'BLUE', 8

SELECT A.Col1, A.Col2, B.Col3, CASE WHEN B.Col4 = B.Col3 THEN '' ELSE CONVERT(varchar, B.Col4) END
FROM (SELECT A.Col1, A.Col2 FROM @T A GROUP BY A.Col1, A.Col2) A
INNER JOIN (SELECT A.Col1, A.Col2, Col3 = MIN(A.Col3), Col4 = MAX(A.Col3) FROM @T A GROUP BY A.Col1, A.Col2) B ON B.Col1 = A.Col1 AND B.Col2 = A.Col2
ORDER BY CASE A.Col2 WHEN 'RED' THEN 1 WHEN 'WHITE' THEN 2 ELSE 3 END, A.Col1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources