SQL table rows to Column

Question

0.00/5 (No votes)

See more:

Please check my doubt before you say duplicated question, As I have some tables and I trying to get report from that. I am posting my select statement below,

SQL

SELECT JobDetails.JobCode,JobDetails.ReceiptDate,JobDetails.DeliveryDate,JobDetails.CompletionDate,JobDetails.BranchName,RepairDetails.Repair_Remarks,JobDetails.FinalRemarks,
       JobDetails.JobCommentCSR,JobDetails.BrandName, JobDetails.ModelName,JobDetails.IMEICode,JobDetails.SerialNo,CustomerDTL.CustomerName,CustomerDTL.MobileNumber,JobDetails.WarrantyType,
       RepairDetails.Tech_Name,RepairDetails.Device_Condition,RepairDetails.Levels,

       ItemMaster.ItemName,ItemMaster.ItemCode,PartsIssueDetails.SparePrice
From JobDetails
Right Join CustomerDTL On
     JobDetails.JobCode=CustomerDTL.JobCode
Right Join RepairDetails On
     JobDetails.JobCode=RepairDetails.JobCode
Right Join PartsIssueDetails On
     JobDetails.JobCode=PartsIssueDetails.JobCode
Right Join ItemMaster On
     PartsIssueDetails.ItemName=ItemMaster.ItemCode
Where 
(JobDetails.CompletionDate Between @Date1 and @Date2) AND JobDetails.BrandName=@Brand
)

I am getting the result as

C#

JobCode	ReceiptDate	DeliveryDate	CompletionDate	BranchName	Repair_Remarks	FinalRemarks	JobCommentCSR	BrandName	ModelName	IMEICode	SerialNo	CustomerName	MobileNumber	WarrantyType	Tech_Name	Device_Condition	Levels	ItemName	ItemCode	SparePrice
201507501	15-Jul-15	30-Aug-15	23-Aug-15	xxx	need replace lcd approved 75	replace lcd approved 75	Screen broken need full check up	xx	ST3-GO-77		aaaa	aaa	1111	N	zzz	HW Part Discoloured	Level 2	LCD ST3-GO-77 (Tab3-GO)	26-05012-0003201	75
201507501	15-Jul-15	30-Aug-15	23-Aug-15	xxx	need replace lcd approved 75	replace lcd approved 75	Screen broken need full check up	xx	ST3-GO-77		aaaa	aaa	111	N	zzz	HW Part Discoloured	Level 2	LCD ST3-GO-77 (Tab3-GO)	26-05012-0003201	0
201508667	5-Aug-15	17-Aug-15	16-Aug-15	xxx	NEED RE-ASSEMBLE AND SOFTWARE	RE-ASSEMBLE AND SOFTWARE	Unit not charging 	xx	SSR2-1-50-5M	xxxxxxx	aaaa	bbb	11111	Y	aaa	HW Part Discoloured	Level 2	Service Level 2	Level L2	73.5
201508668	5-Aug-15	8-Aug-15	5-Aug-15	xxx	NEED REPLACE RECIEVER AND SOFTWARE	REPLACE RECEIVER AND SOFTWARE	Calling problem,Sound Speaker not good need full check up	xxx	SSR1-5-8M	xxxxxxx	aaaa	ccc	111	Y	zzz	HW Part Dirty	Level 2	RECEIVER 0.01W KINGSTATE SSR1-5-8M	29S02-106B0-SH0S	2.78
201508668	5-Aug-15	8-Aug-15	5-Aug-15	xxx	NEED REPLACE RECIEVER AND SOFTWARE	REPLACE RECEIVER AND SOFTWARE	Calling problem,Sound Speaker not good need full check up	xx	SSR1-5-8M	xxxxxxx	aaaa	ccc	111	Y	zzz	HW Part Dirty	Level 2	Service Level 2	Level L2	73.5

You can see here multiple times coming the jobcode, because of the parts. How I can get the result like below.

C#

JobCode	ReceiptDate	DeliveryDate	CompletionDate	BranchName	Repair_Remarks	FinalRemarks	JobCommentCSR	BrandName	ModelName	IMEICode	SerialNo	CustomerName	MobileNumber	WarrantyType	Tech_Name	Device_Condition	Levels	ItemName	ItemCode	SparePrice	ItemName	ItemCode	SparePrice
201507501	15-Jul-15	30-Aug-15	23-Aug-15	xxx	need replace lcd approved 75	replace lcd approved 75	Screen broken need full check up	xx	ST3-GO-77		aaaa	aaa	1111	N	zzz	HW Part Discoloured	Level 2	LCD ST3-GO-77 (Tab3-GO)	26-05012-0003201	75	Touch ST3-GO-77 (Tab3-GO)	26-05012-0003201	0
201508667	5-Aug-15	17-Aug-15	16-Aug-15	xxx	NEED RE-ASSEMBLE AND SOFTWARE	RE-ASSEMBLE AND SOFTWARE	Unit not charging 	xx	SSR2-1-50-5M	xxxxxxx	aaaa	bbb	11111	Y	aaa	HW Part Discoloured	Level 2	Service Level 2	Level L2	73.5			
201508668	5-Aug-15	8-Aug-15	5-Aug-15	xxx	NEED REPLACE RECIEVER AND SOFTWARE	REPLACE RECEIVER AND SOFTWARE	Calling problem,Sound Speaker not good need full check up	xxx	SSR1-5-8M	xxxxxxx	aaaa	ccc	111	Y	zzz	HW Part Dirty	Level 2	RECEIVER 0.01W KINGSTATE SSR1-5-8M	29S02-106B0-SH0S	2.78	Service Level 2	Level L2	73.5
201508671	5-Aug-15	23-Aug-15	9-Aug-15	xxx	NEED REPLACE LCD APPROVED 185 AED	REPLACE LCD Approved 185	Lcd Broken need full check up	xx	SSR1-5-8M	xxxxxxx	aaaa	cccc	111	N	zzz	HW Part Discoloured	Level 2	5" TOUCH PANEL AND LCM MODULE (LAMINATED) SSR1-5-8M	29C10-052A0-L00R	185	5" TOUCH PANEL AND LCM MODULE (LAMINATED) SSR1-5-8M	29C10-052A0-L00R	185

long time i am trying to get the answer for this question.
Help me to solve this issue...

If i simplify my result its like below

C#

JobCode  Date   CustName   ContactNo  ItemCode  ItemName   Price
  001      0012015  abc       000900    item1     item1 name   90
  001      0012015  abc       000900    item2     item2 name   80
  002      0012015  xyz       008000    part1     part1 name   30
  003      0012015  pqr       000900    part2     part2 name   70
  003      0012015  pqr       000900    item1     item1 name   90

i want to show this one to

C#

JobCode Date CustName No ItmCde ItmNme Pric ItmCde2 ItmNme2 Pric2 ItmCd3 ItmCd3 Pric3
 001 0012015 abc 000900 item1  item1 name 90  item2 item2 name 80  
 002 0012015 xyz 008000 part1  part1 name 30
 003 0012015 pqr 000900 part1  part1 name 30  part2 part2 name 70 item1 item1 name  90

Posted 20-Dec-15 0:43am

tastini

Updated 20-Dec-15 19:25pm

v2

Add a Solution

Comments

Kornfeld Eliyahu Peter 20-Dec-15 6:48am

Try DISTINCT

tastini 20-Dec-15 6:54am

if i use distinct , it will remove second one , my question something like pivot table.

[no name] 20-Dec-15 7:34am

Yes exactly, Pivot should solve it.

Kornfeld Eliyahu Peter 20-Dec-15 9:10am

As I see the first and second rows are exact the same, Can you simplify your extract to make more readable?

tastini 21-Dec-15 1:26am

please check the question now.. i changed the result.

Kornfeld Eliyahu Peter 21-Dec-15 1:59am

The problem of your desired result is that it is not tabular...SQL can handle only tabular data...
You have to rethink your problem/solution as the only way to get such data from SQL is using concatenated string...which is very bad...

tastini 20-Dec-15 7:38am

can you help me please ...

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)

Jiyaji_ · Answer 1 · 2015-12-21T19:14:00

SELECT JobDetails.JobCode, JobDetails.ReceiptDate, JobDetails.DeliveryDate, JobDetails.CompletionDate, JobDetails.BranchName, RepairDetails.Repair_Remarks, JobDetails.FinalRemarks, JobDetails.JobCommentCSR, JobDetails.BrandName, JobDetails.ModelName, JobDetails.IMEICode, JobDetails.SerialNo, CustomerDTL.CustomerName, CustomerDTL.MobileNumber, JobDetails.WarrantyType, RepairDetails.Tech_Name, RepairDetails.Device_Condition, RepairDetails.Levels, temMaster.ItemName, ItemMaster.ItemCode, PartsIssueDetails.SparePrice

from
(
select value, columnname
From JobDetails
) d
pivot
(
max(value)
for columnname in (JobDetails.JobCode, JobDetails.ReceiptDate, JobDetails.DeliveryDate, JobDetails.CompletionDate, JobDetails.BranchName, RepairDetails.Repair_Remarks, JobDetails.FinalRemarks, JobDetails.JobCommentCSR, JobDetails.BrandName, JobDetails.ModelName, JobDetails.IMEICode, JobDetails.SerialNo, CustomerDTL.CustomerName, CustomerDTL.MobileNumber, JobDetails.WarrantyType, RepairDetails.Tech_Name, RepairDetails.Device_Condition, RepairDetails.Levels, temMaster.ItemName, ItemMaster.ItemCode, PartsIssueDetails.SparePrice)
) piv;