Move Values along a table when NOT NULL

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

Transact-SQL (2000)

Move Values along a table when NOT NULL

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-10-09 : 10:00:09

I am using SQL 200 and am starting work on an importing data. When the data is imported - it looks like this;

PK C2 C3 C4 C5 C6 C7
1 NULL NULL 5 NULL NULL NULL
2 NULL 1 12 NULL 6 8
3 NULL 3 NULL 14 NULL NULL
4 NULL NULL NULL 8 NULL NULL
5 NULL 6 7 9 4 NULL
6 NULL 7 NULL NULL NULL 18
7 NULL NULL 22 23 NULL 17

Is possible to write a stored procedure that would look at the imported and ask the values to slide along the table so as it would look like this;

PK C2 C3 C4 C5 C6 C7
1 5 NULL NULL NULL NULL NULL
2 1 12 6 8 NULL NULL
3 3 14 NULL NULL NULL NULL
4 8 NULL NULL NULL NULL NULL
5 6 7 9 4 NULL NULL
6 7 18 NULL NULL NULL NULL
7 22 23 17 NULL NULL NULL

Any help would be much appreciated.
Thanks

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2007-10-09 : 10:06:00

How are you importing? From where are you importing?

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com/SQL

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-10-09 : 10:23:53

Using DTS
A text file - I am using ' ' (space) as a separator - from an FTP location (it is an old report written from a legacy system...)

The import side is unusual as file format cannot be accounted for. It is easier to import the data into what will be a temp table - and then format. I.e. slide the data along - then I will know where the data should reside.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 10:29:29

[code]-- Prepare sample data
DECLARE @Source TABLE (PK INT, C2 INT, C3 INT, C4 INT, C5 INT, C6 INT, C7 INT)

INSERT @Source
SELECT 1, NULL, NULL, 5, NULL, NULL, NULL UNION ALL
SELECT 2, NULL, 1, 12, NULL, 6, 8 UNION ALL
SELECT 3, NULL, 3, NULL, 14, NULL, NULL UNION ALL
SELECT 4, NULL, NULL, NULL, 8, NULL, NULL UNION ALL
SELECT 5, NULL, 6, 7, 9, 4, NULL UNION ALL
SELECT 6, NULL, 7, NULL, NULL, NULL, 18 UNION ALL
SELECT 7, NULL, NULL, 22, 23, NULL, 17

-- Set up some staging
DECLARE @Stage TABLE (thePK INT, thePosition INT, theValue INT)

INSERT @Stage
SELECT PK, 1, C2 FROM @Source WHERE C2 IS NOT NULL UNION ALL
SELECT PK, 2, C3 FROM @Source WHERE C3 IS NOT NULL UNION ALL
SELECT PK, 3, C4 FROM @Source WHERE C4 IS NOT NULL UNION ALL
SELECT PK, 4, C5 FROM @Source WHERE C5 IS NOT NULL UNION ALL
SELECT PK, 5, C6 FROM @Source WHERE C6 IS NOT NULL UNION ALL
SELECT PK, 6, C7 FROM @Source WHERE C7 IS NOT NULL

-- Reposition values
UPDATE s1
SET s1.thePosition = (SELECT COUNT(*) FROM @Stage AS s2 WHERE s2.thePK = s1.thePK AND s2.thePosition < s1.thePosition)
FROM @Stage AS s1

-- Show the expected output
SELECT thePK AS PK,
MAX(CASE WHEN thePosition = 0 THEN theValue END) AS C2,
MAX(CASE WHEN thePosition = 1 THEN theValue END) AS C3,
MAX(CASE WHEN thePosition = 2 THEN theValue END) AS C4,
MAX(CASE WHEN thePosition = 3 THEN theValue END) AS C5,
MAX(CASE WHEN thePosition = 4 THEN theValue END) AS C6,
MAX(CASE WHEN thePosition = 5 THEN theValue END) AS C7
FROM @Stage
GROUP BY thePK[/code]

E 12°55'05.25"
N 56°04'39.16"

DLTaylor
Posting Yak Master

136 Posts

Posted - 2007-10-09 : 11:16:54

Amazing – exactly what I wanted!
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources