• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

sql server 2008 t-sql

I'm using sql server 2008 R2.

I have a table called MyTestTable2 that looks like this:

my table
Here is the code to create this table:

CREATE TABLE [dbo].[MyTestTable2](
	[OrderID] [int] NULL,
	[MyCol1Detail] [char](20) NULL,
	[MyCol2Detail] [char](20) NULL,
	[MyCol3Detail] [char](20) NULL,
	[MyCol1] [tinyint] NULL,
	[MyCol2] [tinyint] NULL,
	[MyCol3] [tinyint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[MyTestTable2] ([OrderID], [MyCol1Detail], [MyCol2Detail], [MyCol3Detail], [MyCol1], [MyCol2], [MyCol3]) VALUES (1, N'd1                  ', N'd2                  ', N'Dept.               ', 0, 0, 6)
INSERT [dbo].[MyTestTable2] ([OrderID], [MyCol1Detail], [MyCol2Detail], [MyCol3Detail], [MyCol1], [MyCol2], [MyCol3]) VALUES (2, N'd1                  ', N'd2                  ', N'Dept.               ', 0, 0, 7)
INSERT [dbo].[MyTestTable2] ([OrderID], [MyCol1Detail], [MyCol2Detail], [MyCol3Detail], [MyCol1], [MyCol2], [MyCol3]) VALUES (3, N'd1                  ', N'd2                  ', N'Dept.               ', 0, 0, 3)
INSERT [dbo].[MyTestTable2] ([OrderID], [MyCol1Detail], [MyCol2Detail], [MyCol3Detail], [MyCol1], [MyCol2], [MyCol3]) VALUES (4, N'd1                  ', N'd2                  ', N'Dept.               ', 0, 0, 0)
INSERT [dbo].[MyTestTable2] ([OrderID], [MyCol1Detail], [MyCol2Detail], [MyCol3Detail], [MyCol1], [MyCol2], [MyCol3]) VALUES (5, N'd1                  ', N'd2                  ', N'Dept.               ', 0, 0, 9)
INSERT [dbo].[MyTestTable2] ([OrderID], [MyCol1Detail], [MyCol2Detail], [MyCol3Detail], [MyCol1], [MyCol2], [MyCol3]) VALUES (6, N'd1                  ', N'd2                  ', N'Dept.               ', 0, 0, 14)
INSERT [dbo].[MyTestTable2] ([OrderID], [MyCol1Detail], [MyCol2Detail], [MyCol3Detail], [MyCol1], [MyCol2], [MyCol3]) VALUES (7, N'd1                  ', N'd2                  ', N'Dept.               ', 0, 0, 0)
INSERT [dbo].[MyTestTable2] ([OrderID], [MyCol1Detail], [MyCol2Detail], [MyCol3Detail], [MyCol1], [MyCol2], [MyCol3]) VALUES (8, N'd2                  ', N'd2                  ', N'Dept.               ', 0, 0, 8)

Open in new window


So I want to look at the columns called MyCol1,MyCol2,MyCol3.
I only want to show one of these columns.
I want to show only the column out of these that contains values.

So I have a query that looks like this:

SELECT tt.[OrderID]
      ,tt.[MyCol1Detail]
      ,tt.[MyCol2Detail]
      ,tt.[MyCol3Detail]
      ,CASE WHEN tt.[MyCol1] <> 0 THEN tt.[MyCol1]
            WHEN tt.[MyCol2] <> 0 THEN tt.[MyCol2]
            WHEN tt.[MyCol3] <> 0 THEN tt.[MyCol3]
       END AS TestColumnWithValue
FROM [Test].[dbo].[MyTestTable2] tt

When I run this query I get this:

my current query result

In my example since MyCol3 is the column with values, that is the column I want to show.
So my case statement gets whatever out of these 3 columns has values and calls it TestColumnWithValue.
 

But sometime in the column that I am showing, a row may contain a zero value.

Right now with the query I have, it's putting NULL in the places where there is a zero.

Is there a way to say, after running the CASE statemeent.
Look at the value in my column called TestColumnWithValue and if the value is Null replace the NULL value
with whatever value was in that column prior to running the case statement.

So I want my result set to look like this drawing I drew in excel:

desired result
0
maqskywalker
Asked:
maqskywalker
2 Solutions
 
Paul JacksonSoftware EngineerCommented:
Just use an Else statement with the CASE :

SELECT tt.[OrderID]
      ,tt.[MyCol1Detail]
      ,tt.[MyCol2Detail]
      ,tt.[MyCol3Detail]
      ,CASE WHEN tt.[MyCol1] <> 0 THEN tt.[MyCol1]
            WHEN tt.[MyCol2] <> 0 THEN tt.[MyCol2]
            WHEN tt.[MyCol3] <> 0 THEN tt.[MyCol3]
            ELSE 0
       END AS TestColumnWithValue
FROM [Test].[dbo].[MyTestTable2] tt

Open in new window

0
 
Sreedhar VengalaSr. Consultant - Business IntelligenceCommented:
Try this

SELECT tt.[OrderID]
      ,tt.[MyCol1Detail]
      ,tt.[MyCol2Detail]
      ,tt.[MyCol3Detail]
      ,isnull(CASE WHEN tt.[MyCol1] <> 0 THEN tt.[MyCol1]
            WHEN tt.[MyCol2] <> 0 THEN tt.[MyCol2]
            WHEN tt.[MyCol3] <> 0 THEN tt.[MyCol3]
       END,0) as xyz
	 
FROM [dbo].[MyTestTable2] tt

Open in new window


check your 'Case' result whether ISNULL if null either replace it '0' or with any other value you desire.
0
 
DcpKingCommented:
Both answers so far have put in zeroes explicitly, whereas the question posed asked for "whatever was there before the query ran to remain instead of being replaced by null.
Therefore I would suggest you alter sree-ven's answer very slightly:

isnull(CASE WHEN tt.[MyCol1] <> 0 THEN tt.[MyCol1]
            WHEN tt.[MyCol2] <> 0 THEN tt.[MyCol2]
            WHEN tt.[MyCol3] <> 0 THEN tt.[MyCol3]
       END, TestColumnWithValue) as xyz   

Open in new window


hth

Mike
0
 
John_VidmarCommented:
If the first 2 columns are zero then pick the last column:
SELECT	tt.[OrderID]
,	tt.[MyCol1Detail]
,	tt.[MyCol2Detail]
,	tt.[MyCol3Detail]
,	CASE	WHEN tt.[MyCol1] <> 0 THEN tt.[MyCol1]
		WHEN tt.[MyCol2] <> 0 THEN tt.[MyCol2]
		ELSE tt.[MyCol3]
	END AS TestColumnWithValue
FROM	[Test].[dbo].[MyTestTable2] tt

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now