Click here to Skip to main content
14,241,662 members
Rate this:
Please Sign up or sign in to vote.
See more:
i am having a table customer with 2 columns

customerno products
1001 mobile
1002 chair
1001 table
1004 dress
1005 fan
1002 chair
1003 cycle

i need a query to display different customerno with products

in that result no need to repeat(duplicate) the customerno

for example:
i need result as

customerno products
1001 mobile
1002 chair
1004 dress
1005 fan
1003 cycle

thanks in advance..
Posted
Updated 19-Oct-11 1:32am
v2

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Doesn't make much sense to me. Why is 1001 mobile the expected output and not 1001 table. What I try to say is the second column is useless in the output you desire, as no-one know if the customer has 0 or more other products only that, that the customer has that particular product.

that said you can do it with a nested select statement where you take only the first row, so something like this:
SELECT customerno, (select TOP 1 products from myTable where customerno = t.customerno) as products from myTable t group by customerno


However I would think that you should get all the products
(e.g. 1001 mobile, table) so I would personally do something like this:
SELECT customerno,
       REPLACE(RTRIM((SELECT [products] + ' ' FROM myTable
                      WHERE customerno = t.customerno) FOR XML PATH (''))),' ',', ') AS products
FROM myTable t GROUP BY customerno
   
v3
Comments
Toniyo Jackson 19-Oct-11 7:50am
   
Correct answer, 5!
Simon Bang Terkildsen 19-Oct-11 7:53am
   
Thank you, TJ.
ganesan2510 19-Oct-11 7:56am
   
thanks
Simon Bang Terkildsen 19-Oct-11 8:05am
   
My pleasure
Espen Harlinn 20-Oct-11 5:21am
   
Good reply :)
Simon Bang Terkildsen 20-Oct-11 7:24am
   
Thank you, Espen.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100