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?
soozhAsked:
Who is Participating?
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
0
arnoldCommented:
You are looking at a pivot table, yes.
0
dbbishopCommented:
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
FROM	(
		SELECT docid, [A], [B], [C]
		FROM	#t
		PIVOT	
			(
			MAX(fieldvalue)
			FOR fieldname IN ([A], [B], [C])
			) AS pvt
		) x
GROUP BY DocID

Open in new window

0

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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

PortletPaulfreelancerCommented:
One does not HAVE to use the word "pivot" in SQL to achieve the wanted transformation e.g.
SELECT
      documentid
    , 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
GROUP BY
      documentid
;

Open in new window

This "old fashioned way" to "pivot" can be more suitable if the requirement is complex.
0
arnoldCommented:
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.

https://technet.microsoft.com/en-us/library/ms177410%28v=sql.105%29.aspx
0
PortletPaulfreelancerCommented:
@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.
0
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.

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.