sql multiple rows into multiple columns

multiple rows into multiple columns

this is how my data looks now
FieldNo      FieldValue1      FeildValue2      FieldValue3
24180951                                                            8545
24180951      10987            
24180951                              65852      
36643541      65852            
36643541                              8545      
36643541                                                            10987

this is how I want it to look
FieldNo          FieldValue1      FeildValue2      FieldValue3
24180951      10987              65852             8545
36643541      65852              8545                 10987
eluceroAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
It looks like you may have already used a pivot query, but have failed to use aggregates and grouping. e.g. you need something like this:
SELECT FieldNo, max(FieldValue1), max(FieldValue2), max(FieldValue3)
FROM   YourTable
WHERE  ...
GROUP BY FieldNo

Open in new window

Can we see the query that produced the "data" please?
1
 
_agx_Commented:
Try a self join

SELECT t1.FieldNo, t1.FieldValue1, t2.FieldValue2, t3.FieldValue3
FROM   YourTable t1
	  LEFT JOIN YourTable t2 ON t2.FieldNo = t1.FieldNo AND t2.FieldValue2 IS NOT NULL
	  LEFT JOIN YourTable t3 ON t3.FieldNo = t1.FieldNo AND t3.FieldValue3 IS NOT NULL
WHERE  t1.FieldValue1 IS NOT NULL

Open in new window


Though unless this is some sort of data warehouse table, it sounds like it might be better to normalize the table structure

FieldNo|Type|FieldValue
24180951|Type1| 10987              
24180951|Type2 | 65852            
24180951|Type3 |8545
....
0
 
_agx_Commented:
Duh, yeah an aggregate is a much simpler method. Try Paul's suggestion first.
1
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
eluceroAuthor Commented:
Thank you PortletPaul.  I knew I was missing something.
0
 
PortletPaulfreelancerCommented:
@_agx_ ... :-) "it happens" to us all

Glad I could help, Cheers Paul
0
 
_agx_Commented:
@PortletPaul - Yeah, it's been a looong day ;-)
0
 
Mark WillsTopic AdvisorCommented:
If it was a PIVOT, it means that you have additional columns not needed / not used for final results

So, might not be a case of " I knew I was missing something." as much as maybe you need to be missing something :)

Possibly something like

select fieldno, fieldvalue1,fieldvalue2,fieldvalue3
from
   (select fieldno, anothervalue, valuetoaggregate, valueforcolumns  from datasource ) src
pivot
   (sum(valuetoaggregate) for valueforcolumns in ([fieldvalue1],[fieldvalue2],[fieldvalue3])) pvt

Open in new window

You have a couple of choices,
1) remove anothervalue from selection - which is preferred approach
2) do a select * from the pivot to reveal anothervalue
3) wrap it all in a group by - which sould not be needed in a well formed PIVOT because it is doing the aggregation.

select FieldNo, max(FieldValue1) as FieldValue1, max(FieldValue2) as FieldValue2, max(FieldValue3) as FieldValue3
from
   (select fieldno, anothervalue, valuetoaggregate, valueforcolumns  from datasource ) src
pivot
   (sum(valuetoaggregate) for valueforcolumns in ([fieldvalue1],[fieldvalue2],[fieldvalue3])) pvt
group by fieldno

Open in new window

Have written articles about pivot : https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
But could be other possibilities, so would be good to see the query generating your unwanted results
0
 
PortletPaulfreelancerCommented:
@Mark To me, the use of conditional aggregates to perform a transformation of rows to columns is still a "pivot query". (If one has been using case expressions, but forgets the aggregation, the output looks exactly like the first table shown in the question.) This is what was in my head when I used the term "pivot query" in my original reply.
0
 
Mark WillsTopic AdvisorCommented:
@partletPaul,

Understand that there are a few ways other than using the PIVOT function, and agree with your appraisal.

But having that pesky "anothervalue" will also upset a PIVOT,

And if not using a PIVOT then it would be an option.

So, my post was two fold. The first objective was the all too common "anothervalue" as a rogue column, and the second - albeit less obvious - was you could use the pivot function.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.