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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

24180951|Type1| 10987              
24180951|Type2 | 65852            
24180951|Type3 |8545
PortletPaulEE Topic AdvisorCommented:
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  ...

Open in new window

Can we see the query that produced the "data" please?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Duh, yeah an aggregate is a much simpler method. Try Paul's suggestion first.
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

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

Glad I could help, Cheers Paul
@PortletPaul - Yeah, it's been a looong day ;-)
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
   (select fieldno, anothervalue, valuetoaggregate, valueforcolumns  from datasource ) src
   (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
   (select fieldno, anothervalue, valuetoaggregate, valueforcolumns  from datasource ) src
   (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
PortletPaulEE Topic AdvisorCommented:
@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.
Mark WillsTopic AdvisorCommented:

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.