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
LVL 1
maqskywalkerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
John_VidmarConnect With a Mentor Commented:
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
 
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 VengalaConnect With a Mentor Sr. 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
All Courses

From novice to tech pro — start learning today.