Moving Null Column Values for Novice SQL User

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)

Moving Null Column Values for Novice SQL User

Author

Topic

Maccaman
Starting Member

16 Posts

Posted - 2009-08-18 : 06:16:41

Hi everyone,
I was hoping someone could help me create a query where for every row that has Null values, the null values are moved to the right and are replaced by data that may exist in columns to the left of the Null Values. Confused? Here is what I am trying to achieve in table form (note different columns are indicated by commas, and the Row column is there for easy reference (not actually in my data)).

ROW, Level 1, Level 2, Level 3, Level 4, Level 5, Level 6, Level 7, Level 8,
1, AX, T1, NULL, NULL, NULL, NULL, J1, K1,
2, AX, T1, U1, V1, NULL, NULL, J1, K1,
3, AX, T2, U1, V1, W2, NULL, J2, K2,
4, AX, T2, U2, V2, W4, X1, J3, K3,
5, AX, T3, U3, V3, W8, X5, J3, K4,
6, AX, T3, U3, V4, W4, X4, J6, K5,
7, AX, T4, NULL, NULL, NULL, NULL, NULL, K5,
8, AX, T4, NULL, NULL, W3, X4, J9, K8,
9, AX, T4, U4, NULL, NULL, X4, J10, K12,
10, AX, T4, U5, V8, W1, X7, J31, K12,
11, AX, T5, U7, V8, NULL, NULL, NULL, K17,

Obviously this table has 8 columns called level 1 through to Level 8, and some of these contain number Null Values between columns 1 and 8.
What I meant when I said that I would like to have these null values moved to the left is that I would like to create a query that results in the following table (again row column in there for easy reference):

ROW, Level 1, Level 2, Level 3, Level 4, Level 5, Level 6, Level 7, Level 8,
1, AX, T1, J1, K1, NULL, NULL, NULL, NULL,
2, AX, T1, U1, V1, J1, K1, NULL, NULL,
3, AX, T2, U1, V1, W2, J2, K2, NULL,
4, AX, T2, U2, V2, W4, X1, J3, K3,
5, AX, T3, U3, V3, W8, X5, J3, K4,
6, AX, T3, U3, V4, W4, X4, J6, K5,
7, AX, T4, K5, NULL, NULL, NULL, NULL, NULL,
8, AX, T4, W3, X4, J9, K8, NULL, NULL,
9, AX, T4, U4, X4, J10, K12, NULL, NULL,
10, AX, T4, U5, V8, W1, X7, J31, K12,
11, AX, T5, U7, V8, K17, NULL, NULL, NULL,

If you compare Row 1 in the first table with row 1 in the second table you can see that the Null Values have been moved across to the right, and the values that were in the columns titled "Level 7" and "Level 8" have been moved to the columns where the first two Null values appeared for that row. Hopefully this makes sense.

Can anyone please help me?

Thanks,
Maccaman :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 06:27:07

[code]DECLARE @Sample TABLE
(
ROW INT,
Level1 CHAR(3),
Level2 CHAR(3),
Level3 CHAR(3),
Level4 CHAR(3),
Level5 CHAR(3),
Level6 CHAR(3),
Level7 CHAR(3),
Level8 CHAR(3)
)

INSERT @Sample
SELECT 1, 'AX', 'T1', NULL, NULL, NULL, NULL, 'J1', 'K1' UNION ALL
SELECT 2, 'AX', 'T1', 'U1', 'V1', NULL, NULL, 'J1', 'K1' UNION ALL
SELECT 3, 'AX', 'T2', 'U1', 'V1', 'W2', NULL, 'J2', 'K2' UNION ALL
SELECT 4, 'AX', 'T2', 'U2', 'V2', 'W4', 'X1', 'J3', 'K3' UNION ALL
SELECT 5, 'AX', 'T3', 'U3', 'V3', 'W8', 'X5', 'J3', 'K4' UNION ALL
SELECT 6, 'AX', 'T3', 'U3', 'V4', 'W4', 'X4', 'J6', 'K5' UNION ALL
SELECT 7, 'AX', 'T4', NULL, NULL, NULL, NULL, NULL, 'K5' UNION ALL
SELECT 8, 'AX', 'T4', NULL, NULL, 'W3', 'X4', 'J9', 'K8' UNION ALL
SELECT 9, 'AX', 'T4', 'U4', NULL, NULL, 'X4', 'J10', 'K12' UNION ALL
SELECT 10, 'AX', 'T4', 'U5', 'V8', 'W1', 'X7', 'J31', 'K12' UNION ALL
SELECT 11, 'AX', 'T5', 'U7', 'V8', NULL, NULL, NULL, 'K17'

-- Before
SELECT *
FROM @Sample

-- After
SELECT p.ROW,
p.[1] AS Level1,
p.[2] AS Level2,
p.[3] AS Level3,
p.[4] AS Level4,
p.[5] AS Level5,
p.[6] AS Level6,
p.[7] AS Level7,
p.

AS Level8
FROM (
SELECT u.ROW,
u.theValue,
ROW_NUMBER() OVER (PARTITION BY u.ROW ORDER BY u.theCol) AS theCol
FROM @Sample AS s
UNPIVOT (
theValue
FOR theCol IN (s.Level1, s.Level2, s.Level3, s.Level4, s.Level5, s.Level6, s.Level7, s.Level8)
) AS u
) AS d
PIVOT (
MAX(d.theValue)
FOR d.theCol IN ([1], [2], [3], [4], [5], [6], [7],

)
) AS p[/code]

N 56°04'39.26"
E 12°55'05.63"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-18 : 07:31:06

Hi Also try this ,using cross tab

SELECT ROW,
MAX(CASE WHEN RID = 1 THEN COLUMNSS END ) AS Level1,
MAX(CASE WHEN RID = 2 THEN COLUMNSS END ) AS Level2,
MAX(CASE WHEN RID = 3 THEN COLUMNSS END ) AS Level3,
MAX(CASE WHEN RID = 4 THEN COLUMNSS END ) AS Level4,
MAX(CASE WHEN RID = 5 THEN COLUMNSS END ) AS Level5,
MAX(CASE WHEN RID = 6 THEN COLUMNSS END ) AS Level6,
MAX(CASE WHEN RID = 7 THEN COLUMNSS END ) AS Level7,
MAX(CASE WHEN RID = 8 THEN COLUMNSS END ) AS Level8
FROM (
SELECT
ROW_NUMBER() OVER( PARTITION BY ROW ORDER BY ROW) AS RID,
ROW,COLUMNSS
FROM @SAMPLE
UNPIVOT ( [COLUMNSS] FOR [COLUMNS] IN (LEVEL1,LEVEL2,LEVEL3,LEVEL4,LEVEL5,LEVEL6,LEVEL7,LEVEL8)) P
) T
GROUP BY T.ROW

Maccaman
Starting Member

16 Posts

Posted - 2009-08-18 : 07:51:55

Thank-you both for your replies. I am just a little confused with how to run this query so it pulls the data directly from my table.

My table name is sheet1, and I've been trying to alter the queries you provided to not include @sample ect.

This is what ive been using but it only returns the table in its original structure:

SELECT p.ROW,
p.[1] AS Level1,
p.[2] AS Level2,
p.[3] AS Level3,
p.[4] AS Level4,
p.[5] AS Level5,
p.[6] AS Level6,
p.[7] AS Level7,
p.[8 ]AS Level8
FROM (
SELECT u.ROW,
u.theValue,
ROW_NUMBER() OVER (PARTITION BY u.ROW ORDER BY u.theCol) AS theCol
FROM sheet1 AS s
UNPIVOT (
theValue
FOR theCol IN (s.Level1, s.Level2, s.Level3, s.Level4, s.Level5, s.Level6, s.Level7, s.Level8)
) AS u
) AS d
PIVOT (
MAX(d.theValue)
FOR d.theCol IN ([1], [2], [3], [4], [5], [6], [7], [8 ] )
) AS p

Where would I have to change this? (using SQL Server 2005)
Thanks again :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 08:11:46

You want to update the original table to the new data layout?

N 56°04'39.26"
E 12°55'05.63"

Maccaman
Starting Member

16 Posts

Posted - 2009-08-18 : 09:16:55

Hi Peso,
No, I just wish to use a query to pull back the data from the table in this fashion described in my first post. As I mentioned, I am only a novice when it comes to using SQL and I cannot get the query to run with out all of this:

DECLARE @Sample TABLE
(
ROW INT,
Level1 CHAR(3),
Level2 CHAR(3),
Level3 CHAR(3),
Level4 CHAR(3),
Level5 CHAR(3),
Level6 CHAR(3),
Level7 CHAR(3),
Level8 CHAR(3)
)

INSERT @Sample
SELECT 1, 'AX', 'T1', NULL, NULL, NULL, NULL, 'J1', 'K1' UNION ALL
SELECT 2, 'AX', 'T1', 'U1', 'V1', NULL, NULL, 'J1', 'K1' UNION ALL
SELECT 3, 'AX', 'T2', 'U1', 'V1', 'W2', NULL, 'J2', 'K2' UNION ALL
SELECT 4, 'AX', 'T2', 'U2', 'V2', 'W4', 'X1', 'J3', 'K3' UNION ALL
SELECT 5, 'AX', 'T3', 'U3', 'V3', 'W8', 'X5', 'J3', 'K4' UNION ALL
SELECT 6, 'AX', 'T3', 'U3', 'V4', 'W4', 'X4', 'J6', 'K5' UNION ALL
SELECT 7, 'AX', 'T4', NULL, NULL, NULL, NULL, NULL, 'K5' UNION ALL
SELECT 8, 'AX', 'T4', NULL, NULL, 'W3', 'X4', 'J9', 'K8' UNION ALL
SELECT 9, 'AX', 'T4', 'U4', NULL, NULL, 'X4', 'J10', 'K12' UNION ALL
SELECT 10, 'AX', 'T4', 'U5', 'V8', 'W1', 'X7', 'J31', 'K12' UNION ALL
SELECT 11, 'AX', 'T5', 'U7', 'V8', NULL, NULL, NULL, 'K17'

Because my actual table has about 250 rows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 09:39:19

This is what you need!

SELECT	p.ROW,
	p.[1] AS Level1,
	p.[2] AS Level2,
	p.[3] AS Level3,
	p.[4] AS Level4,
	p.[5] AS Level5,
	p.[6] AS Level6,
	p.[7] AS Level7,
	p. AS Level8
FROM	(
		SELECT	u.ROW,
			u.theValue,
			ROW_NUMBER() OVER (PARTITION BY u.ROW ORDER BY u.theCol) AS theCol
		FROM	YourTableNameHere AS s
		UNPIVOT	(
				theValue
				FOR theCol IN (s.Level1, s.Level2, s.Level3, s.Level4, s.Level5, s.Level6, s.Level7, s.Level8)
			) AS u
	) AS d
PIVOT	(
		MAX(d.theValue)
		FOR d.theCol IN ([1], [2], [3], [4], [5], [6], [7], )
	) AS p

N 56°04'39.26"
E 12°55'05.63"

Maccaman
Starting Member

16 Posts

Posted - 2009-08-18 : 09:48:52

Hi Peso,
Thats what I thought originally. But when I run that query it returns my original table. Ive been trying to figure out the difference between the query where I use my table, and the query that creates a sample table.

"I know the human being and fish can co-exist peacefully" - George W. Bush.
Obviously Mr Bush has never been fishing with me :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-18 : 10:15:32

Of course the query posted 08/18/2009 : 09:39:19 returns your original table.
But look closely on the NULL columns. They are all at the "end", to the rightmost columns.
And the other values is pushed to the left, keeping their relative positions.

N 56°04'39.26"
E 12°55'05.63"

Maccaman
Starting Member

16 Posts

Posted - 2009-08-18 : 18:13:18

I think I see the problem, but I have no idea how to fix it....

When this part of the SQL is executed

SELECT u.ROW,
u.theValue,
ROW_NUMBER() OVER (PARTITION BY u.ROW ORDER BY u.theCol) AS theCol
FROM test1 AS s
UNPIVOT (
theValue
FOR theCol IN (s.Level1, s.Level2, s.Level3, s.Level4, s.Level5, s.Level6, s.Level7, s.Level8)
) AS u

it returns Null values when the original query that included 'Declare @samlpe table'....did not return any null values (for this part of the query). Any ideas how to solve this dilemma? Also, is there a way to do this without 'ROW_NUMBER() OVER (PARTITION BY u.ROW ORDER BY u.theCol) AS theCol"? (just out of curiosity)
Thanks

"I know the human being and fish can co-exist peacefully" - George W. Bush.
Obviously Mr Bush has never been fishing with me :)

Maccaman
Starting Member

16 Posts

Posted - 2009-08-19 : 00:23:05

Just out of curiosity, does anyone know of a way to do this in SSIS with a derived column tool?

"I know the human being and fish can co-exist peacefully" - George W. Bush.
Obviously Mr Bush has never been fishing with me :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources