converting rows to columns

I have a table that has three columns:

id int
documentid int
fieldname varchar(100)
fieldvalue varchar(100)

the data can look like:

id  docuementid    fieldname    fieldvalue
1   20                        A                   123
2   20                        B                   456
3   20                        C                   111

6   22                        A                   777
7   22                        B                   888
8   22                        C                   999

I would like to produce a rows of data like this:

documentid      A             B               C
20                       123        456           111
22                       777        888           999      

How do i do that?  A pivot?
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.

Éric MoreauSenior .Net ConsultantCommented:
or a self-join
You are looking at a pivot table, yes.
Doug BishopDatabase DeveloperCommented:
This will give you the results. Bear in mind that you have to code you table for all anticipated values of fieldvalue. If they can change, or ar variable, a dynamic query is required (see example about 1/2 way through this article)
SELECT DocID, MAX(a) AS a, MAX(b) AS b, MAX(c) AS c
		SELECT docid, [A], [B], [C]
		FROM	#t
			FOR fieldname IN ([A], [B], [C])
			) AS pvt
		) x

Open in new window

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
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

PortletPaulEE Topic AdvisorCommented:
One does not HAVE to use the word "pivot" in SQL to achieve the wanted transformation e.g.
    , MAX(CASE WHEN fieldname = 'A' THEN fieldvalue END) AS A
    , MAX(CASE WHEN fieldname = 'B' THEN fieldvalue END) AS B
    , MAX(CASE WHEN fieldname = 'C' THEN fieldvalue END) AS C
FROM thattable

Open in new window

This "old fashioned way" to "pivot" can be more suitable if the requirement is complex.
Given DocumentID is not unique in the table, you can not presume that there is a unique index comprised of DocumentID+fieldname to prevent duplicate entries.
as such while the example to group by will alter the display to match the question, it might not provide an accurate representation of the data.

pivot need not need the group by and I think the group by example in dbbishop led to the confusion with Paul's example.

Here is the writeup example about pivot/unpivot tables.
PortletPaulEE Topic AdvisorCommented:
@arnold, there is no confusion as far as I can see

While it is a good point you raise about the uniqeuness of  DocumentID+fieldname; the PIVOT operator ALSO is uses aggregate functions like MAX() MIN() etc to achieve the result, so despite there being no explicit group by it is still being performed that way.
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
Query Syntax

From novice to tech pro — start learning today.