Help me with a Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help me with a Query

Author

Topic

sqlilliterate
Starting Member

40 Posts

Posted - 2008-01-04 : 08:33:45

Hi all,

I’ve a table with the following data.


Col1		Col2
----------------------------
NULL		0
Mr.		0
Mrs.		0
Ms		0
Ms.		0
Sr.		0
Sra.		0
NULL		1
Mr.		1
Mrs.		1
Ms.		1
Sr.		1
Sra.		1
NULL		2
Mr.		2
Ms.		2
Sr.		2

I want the output like 

Col1		Col2
----------------------------
Sr.		0
Mrs.		1
NULL		2
Ms.		
Sra.
Mr.
Ms

Help me writing a query for this…

Thanks...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-04 : 08:41:54

ABRACADABRA...HULA HULA HUKA BOOOO....CHIKI CHIKI BUM..

BINGO!!!!

You got the output!!

Well that's what I do when I don't know business rules, table structure and clear problem description...and even sometimes work for me...why don't you try??

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-04 : 08:42:22

The sample o/p doesnt make much sense. Can you explain what you want in more detail?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-04 : 09:12:13

start with this and follow the pattern:

select 'Sr.', 0 union all
select 'Mrs.', 1 union all
select ...

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-04 : 09:24:24

quote:
Originally posted by sqlilliterate

Hi all,

I’ve a table with the following data.

Col1 Col2
----------------------------
NULL 0
Mr. 0
Mrs. 0
Ms 0
Ms. 0
Sr. 0
Sra. 0
NULL 1
Mr. 1
Mrs. 1
Ms. 1
Sr. 1
Sra. 1
NULL 2
Mr. 2
Ms. 2
Sr. 2

I want the output like

Col1 Col2
----------------------------
Sr. 0
Mrs. 1
NULL 2
Ms.
Sra.
Mr.
Ms

Help me writing a query for this…

Thanks...

Did you post your complete result?

Madhivanan

Failing to plan is Planning to fail

sqlilliterate
Starting Member

40 Posts

Posted - 2008-01-05 : 00:40:12

Sorry for not being clear in my post...

Actually my output should contain only the distinct values of each columns. No sorting on any column is required.

Thanks again...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-05 : 02:39:30

I didnt understand what you are looking for here? Can you explain your requirement?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-05 : 09:17:45

Try this:-

CREATE TABLE #temp1
(
ID int IDENTITY(1,1),
Col varchar(10)
)

CREATE TABLE #temp2
(
ID int IDENTITY(1,1),
Col int
)


INSERT INTO #temp1(Col)
SELECT DISTINCT Col1
FROM Table


INSERT INTO #temp2(Col)
SELECT DISTINCT Col2
FROM Table

SELECT t1.Col AS 'Col1',t2.Col AS 'Col2'
FROM #temp1 t1
LEFT OUTER JOIN #temp2 t2
ON t1.ID=t2.ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources