Retrieve Distinct Value - 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 2008 Forums

Transact-SQL (2008)

Retrieve Distinct Value

Author

Topic

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-12-07 : 05:24:57

Table List Articles

Xvalue YValue ZValue Program#
300 400 500 1
300 600 200 2
400 400 100 3
100 200 500 4
200 600 200 5

Result should retrieve

Xvalue YValue ZValue
100 200 100
200 400 200
300 600 500
400

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-07 : 08:13:42

quote:
Originally posted by coagulance

Table List Articles

Xvalue YValue ZValue Program#
300 400 500 1
300 600 200 2
400 400 100 3
100 200 500 4
200 600 200 5

Result should retrieve

Xvalue YValue ZValue
100 200 100
200 400 200
300 600 500
400

What is the logic you applied to get this result?

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 08:46:15

[code]
declare @sample table(x int,y int,z int)
insert @sample
select * from
( -- table with example data
select 300 as x, 400 as y, 500 as z union all
select 300,600,200 union all
select 400,400,100 union all
select 100,200,500 union all
select 200,600,200)dt

--select * from @sample

-- solution
select tablex.x, tabley.y,tablez.z from
(
select
row_number() over (order by (select 1)) as rownum, x from
(select distinct x from @sample)dt
)tablex

left join
(
select
row_number() over (order by (select 1)) as rownum, y from
(select distinct y from @sample)dt
)tabley
on tablex.rownum = tabley.rownum

left join
(
select
row_number() over (order by (select 1)) as rownum, z from
(select distinct z from @sample)dt
)tablez
on tablez.rownum=tablex.rownum
[/code]

-- result:
[code]
x y z
100 200 100
200 400 200
300 600 500
400 NULL NULL
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-12-09 : 05:06:07

Can we do this without using @sample table ?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-09 : 06:37:43

The @sample table is just what webfred used to demonstrate his suggestion. Remember that we don't have a copy of your tables and data. You should replace the @sample table with the name of your own table, and you only need the bit webfred labels 'solution'.

BUT, to echo madhivanan's question: What is the logic you applied to get this result?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-12-09 : 06:46:42

Thank you for your reply , The logic is to get distict items from the three columns . Please note that there is no relevence to the row to row link here as I am using this as Menu items.

IF
declare @sample table(x int,y int,z int)
insert @sample
select * from
( -- table with example data
select 300 as x, 400 as y, 500 as z union all
select 300,600,200 union all
select 400,400,100 union all
select 100,200,500 union all
select 200,600,200)dt

NEEDS to be replacved by a query that returns x,y,z from a table , I was uanbel to figure out the changes that I need to make in the solutiuon sent by Webfred. Sorry for this dumb requset , i could not figure out.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-09 : 07:39:31

I concluded that either webfred is a mindreader or there was a similar post on this somewhere. I searched and found this [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=136929[/url].

Is that the only other related post or are there others (on this site or elsewhere)?

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-09 : 07:43:14

Using webfred's suggestion and removing the @sample table, try the following. You'll need to replace 'PutTheNameOfYourTableHere' for the name of your table.

select tablex.x, tabley.y,tablez.z from
(
select
row_number() over (order by (select 1)) as rownum, x from
(select distinct x from PutTheNameOfYourTableHere)dt
)tablex

left join
(
select
row_number() over (order by (select 1)) as rownum, y from
(select distinct y from PutTheNameOfYourTableHere)dt
)tabley
on tablex.rownum = tabley.rownum

left join
(
select
row_number() over (order by (select 1)) as rownum, z from
(select distinct z from PutTheNameOfYourTableHere)dt
)tablez
on tablez.rownum=tablex.rownum

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

coagulance
Yak Posting Veteran

78 Posts

Posted - 2009-12-09 : 08:36:13

Thank you , But the two posts I have put are different although it seems related ,Now with the solution that Ryan and WebFred havd suggested it was possible to crack the other one.
Thanks Ryan , Fred

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-10 : 07:51:55

glad I could help