Showoing columns to row in a datatabl

Question

0.00/5 (No votes)

See more:

I will be very regretful if give me a proper solution of my question. From the following query I get the result as :

"select sp.MaintenceCode, sp.MDate, sp.PDes,sp.SPCode, s.SpareParts, " +
"em.EmpName as MaintainedBy, md.MachinaryName, sp.SPMCode "+
"from tblDailyMaintenceDetails dm "+
"inner join tblEmployeeInformation em on em.EmpCode=dm.EmpCode "+
"inner join tblSparePartsMaintenance sp on sp.MaintenceCode=dm.MaintenceCode "+
"inner join tblSpareParts s on s.SPCode=sp.SPCode "+
"inner join tblMachinaryDetails md on md.MDCode=dm.MDCode "+
"where md.MDCode='MDP-000001' and sp.MDate'15-May-2013' ";

The result is :

DMC-002 5/15/2013    not good    SPC-000002  Cable   HASNA BEGUM Toshiba SPM-000002
DMC-002 5/15/2013    not good    SPC-000001  Motor   HASNA BEGUM Toshiba SPM-000004

But I have to show this in crystal report like as follows :

DMC-002 5/15/2013    not good    SPC-000002  Cable,  Motor   HASNA BEGUM Toshiba SPM-000002

How can I do this? Please help.

Posted 10-May-13 17:16pm

Sumon562

Updated 11-May-13 18:39pm

v6

Add a Solution

Comments

Maciej Los 11-May-13 4:39am

What kind of database (Access, SQL Server, PostgreSQL, MySQL)?

What have you done / tried till now?

Sumon562 11-May-13 4:40am

Thank you Mr.Maciej Los
I have used sql server 2008

Maciej Los 11-May-13 6:33am

Please, append headers to your example data and provide more information about the structure of table(s). Use "Improve Question" widget.
Based on which criteria do you want to split rows to column?

Sumon562 12-May-13 3:02am

Hi, have anyone to help me?

1 solution

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)

Adarsh chauhan · Answer 1 · 2013-05-11T01:54:00

Hi Mohi Uddin,

what i think you want to do is like--

I have a table as table1

Date	       Bill_no	cust_name	Item_name
2/2/2013	234	 ABC	    wireless mouse
2/3/2013	234	 ABC	    wireless keyboard
2/4/2013	234	 ABC	    mouse pad

and i want to show result as

Date	        Bill_no	  Item_name	              cust_name				
2/4/2013	234	wireless mouse,wireless keyboard,mouse pad	 ABC

I can do it in following way:

SQL

select Date,Bill_no,
(
select Item_name +',' from table1 a (nolock)
where 
for xml path ('')
) as Item_Name,Cust_name
from table1 b(nolock)
where bill_no=234 and custName='ABC'
    group by Date,Bill_no,Cust_name

hope it will help you....