Solved

sql server 2008 t-sql

Posted on 2014-03-12
4
327 Views
Last Modified: 2014-03-13
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
Comment
Question by:maqskywalker
4 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 39925206
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
 
LVL 9

Assisted Solution

by:Sreedhar Vengala
Sreedhar Vengala earned 200 total points
ID: 39925228
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
 
LVL 16

Expert Comment

by:DcpKing
ID: 39925330
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
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 300 total points
ID: 39926125
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now