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?
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?
Éric Moreau
or a self-join
You are looking at a pivot table, yes.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
;SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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.
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.