Select Query

Question

Post reply

Select Query

Raghavender Chavva

SSCrazy Eights

Points: 8669
More actions
July 26, 2010 at 5:14 am

#387426

Hi Team,
I have a table, when I select it results shows like below:
Instance name Verified Name
USMKESQLPRD001\SQL001NULL
NULL
NULL
NULL ------------------------------------------------------------
NULL --------------------------------------------------
NULL
NULL
NULL NULL
I need this should look like below:
Instance name Verified Name
USMKESQLPRD001\SQL001USMKESQLPRD001SQL001
can you please let me know how to do this ?
Thank You.
Regards,
Raghavender Chavva

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Dint this work for you?

Select instancename,verifiedname from table where instancename is not null and verifiedname is not null

As it seems as simple, i guess i am missing something.. if thats NOT what you are after, can you tell us clearly what you expected query is with sample data of your table?

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 2

Below is the Original result:

Instance name Verified Name

USMKESQLPRD001\SQL001 NULL

NULL

NULL ------------------------------------------------------------

NULL --------------------------------------------------

NULL USMKESQLPRD001\SQL001

NULL

NULL NULL

It need to be converted into like below:

Instance name Verified Name

USMKESQLPRD001\SQL001 USMKESQLPRD001\SQL001

Thank You.

Regards,
Raghavender Chavva

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Raghavender

Please will you describe what you're trying to do? It looks as if you want to display the server name and instance name without the separating backslash. To do this, have look in Books online about string functions - I think REPLACE is the one you need here.

John

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 4

ColdCoffee (7/26/2010)
Dint this work for you?
Select instancename,verifiedname from table where instancename is not null and verifiedname is not null
As it seems as simple, i guess i am missing something.. if thats NOT what you are after, can you tell us clearly what you expected query is with sample data of your table?

above query dint work for me...its showing null values.

Thank You.

Regards,
Raghavender Chavva

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 5

Sorry...not null values its showing no values.

Thank You.

Regards,
Raghavender Chavva

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Can u attach a screen shot of your "Results Pane" alone with your current result set? I am stil unclear on your request. Just a shot in the dark, are u trying to find only the non-null entries from each column into only single row?

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

Raghavender (7/26/2010)
Sorry...not null values its showing no values.

SELECT instance_name, verified_name

FROM dbo.Table1

WHERE (instance_name IS NOT NULL) OR (verified_name IS NOT NULL)

but this will give you two lines.....

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

-- create sample data

DROP TABLE #Sample

CREATE TABLE #Sample ([Instance name] varchar(60), [Verified Name] varchar(60))

INSERT INTO #Sample ([Instance name], [Verified Name])

SELECT 'USMKESQLPRD001\SQL001', NULL UNION ALL

SELECT NULL, '' UNION ALL

SELECT NULL, '------------------------------------------------------------' UNION ALL

SELECT NULL, '--------------------------------------------------' UNION ALL

SELECT NULL, '' UNION ALL

SELECT NULL, NULL

-- check sample data

SELECT * FROM #Sample

-- generate required results

SELECT [Instance name], [Verified Name] = [Instance name]

FROM #Sample

WHERE [Instance name] IS NOT NULL

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

Ok, I may be an idiot. BUT!

Did anyone, before providing answers, understood what this OP really have in his DB and what he is really trying to do?

He said that "I have a table", but he doesn't specify any columns and its datatype

Then he said "when I select it results shows like below: ...", but what query he is using?

Look at the end you can write a query to display his current and wanted results without having any tables...

Dear Raghavender, if you want to get relevant help quickly, please click the link located in my signature where you will find information about how to post your questions.

Please keep in mind that the most of people who is helping on this forum do it in their precious free-time.

As you have asked to convert results of your current query into one row, you can do just this:

SELECT (SELECT TOP 1 [Instance name] FROM (your query) yq WHERE [Instance name] IS NOT NULL) AS [Instance name]

,(SELECT TOP 1 [Verified Name] FROM (your query) yq WHERE [Verified Name] IS NOT NULL) AS [Verified Name]

Does it return results required by you? - YES

Does it do what you really want? - I doubt it...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 10

Raghavender Chavva

SSCrazy Eights

Points: 8669

July 26, 2010 at 8:15 am

#1198211

Wrong one

Thank You.

Regards,
Raghavender Chavva

Raghavender Chavva SSCrazy Eights Points: 8669 More actions · Answer 11

Chris Morris-439714 (7/26/2010)
-- create sample data DROP TABLE #Sample CREATE TABLE #Sample ([Instance name] varchar(60), [Verified Name] varchar(60)) INSERT INTO #Sample ([Instance name], [Verified Name]) SELECT 'USMKESQLPRD001\SQL001', NULL UNION ALL SELECT NULL, '' UNION ALL SELECT NULL, '' UNION ALL SELECT NULL, '------------------------------------------------------------' UNION ALL SELECT NULL, '--------------------------------------------------' UNION ALL SELECT NULL, '' UNION ALL SELECT NULL, '' UNION ALL SELECT NULL, NULL -- check sample data SELECT * FROM #Sample -- generate required results SELECT [Instance name], [Verified Name] = [Instance name] FROM #Sample
WHERE [Instance name] IS NOT NULL

From the above Query I got my required output.

Thanks every One for your precious time and your help.

Thank You.

Regards,
Raghavender Chavva

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Raghavender (7/26/2010)
Wrong one

Can you please post the query which solved your problem? It will satisfy the curiosity of those who have given their time to help you, and may help others in the future.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden