Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql server 2008 t-sql

Posted on 2014-03-12
4
Medium Priority
?
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 800 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 1200 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

722 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