Move columns to the left if there are NULLs in output
-
Hi
I have a requirement to output table results to the left most column if there are nulls involved.e.g table output example using 4 columns
Mdfe NULL G543 K943
NULL Df34 NULL T321
Should be output like.......
Mdfe G543 K943 NULL
Df34 T321 NULL NULL
Need some help on this one.
Thx
-
Why would you want to do that? Why would you have 4 different columns in the same table with the same data (time1, time2, time3, time4)?
Check out the case statement to do this.
-
This violates a cardinal rule of data sets in that you are now having the first column, column one, representing data from different physical columns. I suppose you could have answers to questions or comments or something, but I think it's a bad idea.
-
Is something that someone here asked if it is possible to do. I thought I would check it out, and its probably possible to write it in some long winded script, and thought maybe there could be an easier way. Maybe I'll just tell them it cant be done.
-
It can be done, quite easily, I might add. We're just telling you that it's a very bad idea.
If you insist, I can give you some working code to get you on your way.
-
If you have some working code that would be great.
-
if these guys here say it's a bad idea.....then i really don't think you should be doing it. Unless u have some very good reason to
-
Like everyone has stated... bad idea. Should be done with reporting software or a GUI if you have one. I, too, question the table design for having what appears to be a gross corruption of 3rd normal form.
That not withstanding, here's a self supporting example using your data... just remember that you asked for "output", not a result set...
--===== Create a simple test table and populate it with data from the post
DECLARE @test-2 TABLE (Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10), Col4 VARCHAR(10))
INSERT INTO @test-2 (Col1,Col2,Col3,Col4)
SELECT 'Mdfe',NULL,'G543','K943' UNION ALL
SELECT NULL,'Df34',NULL,'T321'
--===== Demo one possible solution
SELECT ISNULL(Col1+CHAR(9),'')
+ ISNULL(Col2+CHAR(9),'')
+ ISNULL(Col3+CHAR(9),'')
+ ISNULL(Col4+CHAR(9),'')
FROM @test-2
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
OK - thx guys - your comments noted - I'm going to put this one to bed.
Cheers
-
Select column1 = coalesce(col1,col2,col3,col4),
column2 = case when NonNulls = 1 then NULL
When NonNulls = 4 then col2
when NonNulls = 3 then Coalesce(Col3,Col4)
else Coalesce(col4, col3,col2) end ,
column3 = case When NonNulls = 4 then col3
when NonNulls < 3 then Null
else Coalesce(Col4,col3)end,
column4 = case when (col1+col2+col3+col4) is null then Null
else col4 end
from (Select col1,col2,col3,col4,
(Case when col1 is null then 0 else 1 end) +
(Case when col2 is null then 0 else 1 end) +
(Case when col3 is null then 0 else 1 end) +
(Case when col4 is null then 0 else 1 end) as NonNulls
from @test-2) t
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply