how to get a single row from a table from multiple rows

Question

0.00/5 (No votes)

See more:

Hi
I have 4 rows in a table with fields name,model,country.
in each row only the country differes. When i display these rows i must be able to show them in a single row with country being show with comma separated,like this.

Name Model Country
---------------------------------------------------------------------------
ABC XYZ India,US,UK

Please help me out

Posted 10-Sep-12 23:20pm

Arjun Menon U.K

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Answer 1 · 2012-09-10T23:33:00

Solution 1

SQL

select [Name],Model,substring(Country,0,Len(country)) as Country from
    (
        select distinct [Name],Model,
        (select Country + ',' as [text()] from tabel where [Name]=a.[Name] and Model=a.Model for xml path('')) as Country
        from tabel as a
    )
as a

Happy Coding!
:)

Posted 10-Sep-12 23:33pm

Aarti Meswania

Updated 10-Sep-12 23:38pm

v3

Comments

vangapally Naveen Kumar 11-Sep-12 5:41am

Good Answer

Aarti Meswania 11-Sep-12 5:46am

Thank you! :)

sunandandutt 11-Sep-12 5:44am

My 5!

Aarti Meswania 11-Sep-12 5:45am

thank you!
:)

Mohamed Mitwalli 11-Sep-12 5:46am

5+

Aarti Meswania 11-Sep-12 5:47am

Thank you! :)

Arjun Menon U.K 11-Sep-12 6:44am

Hi aarti a doubt what's with the [ for xml path('') ] .. I am new to SQL that's why my doubt

Aarti Meswania 11-Sep-12 6:51am

+ ',' as [text()] ... for xml path('')

for xml path('') merge resultant rows and convert to xml

as [text()] will convert xml to plain text (if not write this it will show result string in xml tags)

',' is externally added by us to display as separator between rows' values.

Arjun Menon U.K 11-Sep-12 6:53am

i am showing the result in a HTMl page... and i don't know the "abcd" of xml and it seems i cant use XML too... So is there another way...

Arjun Menon U.K 11-Sep-12 6:56am

It will be very helpful if you can provide some kind off query so that i can retrieve using DataTable and loop it and display the result

Aarti Meswania 11-Sep-12 7:02am

this query not giving you desire output?

Arjun Menon U.K 11-Sep-12 7:17am

The xml part is still confusing me... sorry mam if i am making you angry may be i am not thinking the right way :D...

Arjun Menon U.K 11-Sep-12 7:21am

This is the situation.. am creating a device(in portal for storing in DB) and i can select country where it will be available plus the agencies that are gng to fulfill the device order. I am storing these details in "device detail". Suppose i selected 2 countries there will be two rows with exact same value but with countryid being the only difference. when i list the page there r 2rows now as the country id is different,but my TL said to show in a single row provided the country shown separated by commas.. hope this will make things bit more clear

Aarti Meswania 11-Sep-12 7:26am

my query is giving same output as you said,
first you tell me it worked or not?
Is it giving you error?

Arjun Menon U.K 11-Sep-12 7:29am

I am on it will let u know the result soon... Thnks in adv :)

Aarti Meswania 11-Sep-12 7:44am

ok, use this query if any error then tell me
and don't worry about 'for xml path()'
you will get information how it's working.

Looping is not solution when a sql's inbuilt function serves you same facility with good performance, right?

and 'for xml path()' is same thing suppose there is loop collecting data from each row and fill it in a xml format inside sql's memory and as the loop completes you get a single xml string for that loop, and then '[text()]' function display that xml string in to a simple text format.
:)

Kuthuparakkal · Answer 2 · 2012-09-10T23:58:00

Solution 2

SQL

DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString = COALESCE(@combinedString + ', ', '') + Country
FROM MyTable

SELECT DISTINCT [Name],Model, @combinedString AS Country 
FROM MyTable

Posted 10-Sep-12 23:58pm

Kuthuparakkal