can u know - 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 2005 Forums

Transact-SQL (2005)

can u know

Author

Topic

sqlserver5
Starting Member

19 Posts

Posted - 2008-01-07 : 04:10:24

Hi
table is likewise

name dep location
raja cs chennai
roja a/c madurai
raja a/c chennai
roja cs madurai

write a query for the following output

name dep loc
raja cs madurai
roja a/c chennai

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-07 : 04:21:57

select distinct name, dep, location from Table1

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-07 : 05:23:16

There is no way you can get that output. The requirement itself is absurd.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-07 : 07:10:18

[code]DECLARE @DistCol1 TABLE
(
ID int IDENTITY(1,1),
name varchar(50)
)

DECLARE @DistCol2 TABLE
(
ID int IDENTITY(1,1),
dep varchar(50)
)

DECLARE @DistCol3 TABLE
(
ID int IDENTITY(1,1),
location varchar(50)
)

INSERT INTO @DistCol1 (name)
SELECT DISTINCT name
FROM YourTable

INSERT INTO @DistCol2 (dep)
SELECT DISTINCT dep
FROM YourTable

INSERT INTO @DistCol3 (location)
SELECT DISTINCT location
FROM YourTable

SELECT t1.name,t2.dep,t3.location as 'loc'
FROM @DistCol1 t1
LEFT OUTER JOIN @DistCol2 t2
ON t2.ID=t1.ID
LEFT OUTER JOIN @DistCol3 t3
ON t3.ID=t1.ID[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-07 : 07:11:20

quote:
Originally posted by sqlserver5

Hi
table is likewise

name dep location
raja cs chennai
roja a/c madurai
raja a/c chennai
roja cs madurai

write a query for the following output

name dep loc
raja cs madurai
roja a/c chennai

Define your output

Madhivanan

Failing to plan is Planning to fail

sqlserver5
Starting Member

19 Posts

Posted - 2008-01-08 : 02:05:43

select dinstinct column1,column2,column3 from table
will not work.

sqlserver5
Starting Member

19 Posts

Posted - 2008-01-08 : 02:11:34

hi

output is likewise i need

name dep loc
raja cs madurai
roja a/c chennai

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-08 : 02:11:37

I know, but you didn't provide enough ionformation about your output.
How do you now which department belongs to which name & location?

E 12°55'05.25"
N 56°04'39.16"

sqlserver5
Starting Member

19 Posts

Posted - 2008-01-08 : 02:13:32

hi peso

i need distinct column (for all column in a table)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-08 : 02:17:58

Did you try my query?

sqlserver5
Starting Member

19 Posts

Posted - 2008-01-08 : 02:22:20

hello peso sir,

can u get my query

sqlserver5
Starting Member

19 Posts

Posted - 2008-01-08 : 02:23:20

dear visakh
it is too long

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-08 : 02:27:05

quote:
Originally posted by sqlserver5

can u get my query

Yes I can, if you provide information about the business rules to apply for departments and how to distribute them among locations.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-08 : 02:39:22

[code]-- Prepare sample data
DECLARE @Sample TABLE (Name VARCHAR(4), Dep VARCHAR(3), Location VARCHAR(7))

INSERT @Sample
SELECT 'raja', 'cs', 'chennai' UNION ALL
SELECT 'roja', 'a/c', 'madurai' UNION ALL
SELECT 'raja', 'a/c', 'chennai' UNION ALL
SELECT 'roja', 'cs', 'madurai'

-- Show the expected output
SELECT p1.Name,
p2.Dep,
p1.Location
FROM (
SELECT Name,
Location,
ROW_NUMBER() OVER (ORDER BY Name, Location) AS RecID
FROM (
SELECT DISTINCT s2.Name,
s1.Location
FROM @Sample AS s1
CROSS JOIN @Sample AS s2
WHERE s1.Location <> s2.Location
AND s1.Dep = s2.Dep
) AS d
) AS p1
INNER JOIN (
SELECT Dep,
ROW_NUMBER() OVER (ORDER BY Dep DESC) AS RecID
FROM (
SELECT DISTINCT Dep
FROM @Sample
) AS d
) AS p2 ON p2.RecID = p1.RecID[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-08 : 03:38:09

quote:
Originally posted by sqlserver5

Hi
table is likewise

name dep location
raja cs chennai
roja a/c madurai
raja a/c chennai
roja cs madurai

write a query for the following output

name dep loc
raja cs madurai
roja a/c chennai

raja's location is chennai. Why do you need madhurai for him?

Madhivanan

Failing to plan is Planning to fail

sqlserver5
Starting Member

19 Posts

Posted - 2008-01-08 : 03:47:47

Dear madhi

there is no relation between columns

i need only distinct column (for all column in a table)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-08 : 04:00:31

If there is no relation, you can just do this:

Select *
from
(
Select 'raja' as [name], 'cs' as dep, 'madurai' as location union all
Select 'roja', 'a/c', 'chennai'
) temp

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-08 : 04:15:19

One of the methods

declare @t table(name varchar(10), dept varchar(5), location varchar(20))
insert into @t
select 'raja','cs','chennai' union all
select 'roja', 'a/c', 'madurai' union all
select 'raja', 'a/c', 'chennai' union all
select 'roja', 'cs' ,'madurai'

select t1.name,t2.dept,t3.location from
(
select name, row_number() over(order by name) as n from @t
group by name
) as t1 inner join
(
select dept, row_number() over(order by dept) as n from @t
group by dept
) as t2 on t1.n=t2.n inner join
(
select location, row_number() over(order by location) as n from @t
group by location
) as t3 on t2.n=t3.n

But I still dont know why you want output like this
Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

sqlserver5
Starting Member

19 Posts

Posted - 2008-01-08 : 07:07:37

HI madhi,

why i need?

cmd = New SqlCommand("select distinct(catagory) from main", con)
r = cmd.ExecuteReader()
While r.Read
Catagory.Items.Add(r.GetValue(0))
End While
r.Close()

cmd = New SqlCommand("select distinct(releasor) from main", con)
r = cmd.ExecuteReader()
While r.Read
Releasor.Items.Add(r.GetValue(0))
End While
r.Close()

cmd = New SqlCommand("select distinct(album) from main", con)
r = cmd.ExecuteReader()
While r.Read
Album.Items.Add(r.GetValue(0))
End While
r.Close()

To reduce the code i asked

distinct column ( for all column in a table)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-08 : 07:40:36

quote:
Originally posted by sqlserver5

HI madhi,

why i need?

cmd = New SqlCommand("select distinct(catagory) from main", con)
r = cmd.ExecuteReader()
While r.Read
Catagory.Items.Add(r.GetValue(0))
End While
r.Close()

cmd = New SqlCommand("select distinct(releasor) from main", con)
r = cmd.ExecuteReader()
While r.Read
Releasor.Items.Add(r.GetValue(0))
End While
r.Close()

cmd = New SqlCommand("select distinct(album) from main", con)
r = cmd.ExecuteReader()
While r.Read
Album.Items.Add(r.GetValue(0))
End While
r.Close()

To reduce the code i asked

distinct column ( for all column in a table)

The output you are looking for cant be done straight forward
I advise you to maipulate it in the front end application
Dont worry about the lengthy code in .NET

Madhivanan

Failing to plan is Planning to fail

sqlserver5
Starting Member

19 Posts

Posted - 2008-01-08 : 08:11:51

thank you madhi
i will do likewise

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources