Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sql server 2008 t-sql

Posted on 2014-03-12
4
Medium Priority
?
371 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 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

972 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