sql Query

[nikol]

Hi ,
UID NAME PHONE
1 peter 715-987-8656
1 jill 910-987-8778
2 mike 510-758-9767
2 mott 345-987-0876

Can I make the above DB columns as
UID NAME
1 peter;715-987-8656 jill;910-987-8778
2 mike;510-758-9767 mott;345-987-0876

 

[rmnastor]

it seems impossible unless you'd use SP (Stored Procedure)

 

[rmnastor]

create a another table then populate it trough ur SP

 

[r937]

if it's only 2 values into 1, you can use a simple self-join

if it's N values into 1, see faq183-2146

r937.com | rudy.ca

 

[adalbertotavera]

Use a view exp.

select UID,ltrim(NAME)+','+trim(PHONE)as Name from table

 

[nikol]

Hi,

I got my half of the answer.
UID NAME
1 peter;715-987-8656
1 jill;910-987-8778
2 mike;510-758-9767
2 mott;345-987-0876
Now is there any way I can do the following

UID NAME
1 peter;715-987-8656 jill;910-987-8778
2 mike;510-758-9767 mott;345-987-0876

 

[r937]

okay, with that exact data, you can use this:

select t1.UID
, t1.NAME + ' ' + t2.NAME as NAME
from somewhere as t1
inner
join somewhere as t2
on t1.UID = t2.UID
and t1.NAME < t2.NAME

r937.com | rudy.ca

 

[nikol]

Hi !! r937,
Its working.. From last 3 hrs I'm trying-trying & ofcourse I was still trying.. Thanks for the help..
I was just wondering what if a client_id has only 1 referral
like CLIENT_ID (UID 1) has only 1 referral
UID NAME
1 peter;715-987-8656
2 mike;510-758-9767 mott;345-987-0876
coz above query gives only UID's who have 2 referrals..

 

[SQLDenis]

Take a look at left join in BOL

“I sense many useless updates in you... Useless updates lead to defragmentation... Defragmentation leads to downtime...Downtime leads to suffering..Defragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[rmnastor]

how about we add more data

UID NAME
1 peter;715-987-8656
1 jill;910-987-8778
2 mike;510-758-9767
2 mott;345-987-0876
1 any1;???
1 any2;???
3 any3;???

the query above will not work...

 

[r937]

the query above will not work

that's right, it won't


but the techniques in the faq do

r937.com | rudy.ca

 

[rmnastor]

your right as i've mentioned on my first post that this can only be solved by the use of SP...

though i am still skeptical that this can be solved through plain single line query...