We help IT Professionals succeed at work.

How to group columns into rows

wayneburr
wayneburr asked
on
207 Views
Last Modified: 2011-10-19
I am trying to change the column values into rows.  I included what the data currently looks like and below is what I want to return etc:

Materialtestresults_id       Tests
17185                                  0
17185                                  25
17185                                  70
17185                                   90
17200                                  0
...                                       ...
(No Null rows etc)

I'm stuck on getting a correct output etc.

Thanks!
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
the corresponding "output" sample is "missing".. :)

Author

Commented:
uggg.
MatTests.jpg
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I fear you must give a bit more information about how (logically, not the SQL syntax stuff) the "transformation" has to happen?
I assume the R25, R1, R2 etc is some column value in the "input" which you did not show?

let me "guess":
SELECT MATERIALTESTRESULTS_ID, MATERIALID
, MAX( CASE WHEN R_Column = 'R25' THEN Tests END) R25
, MAX( CASE WHEN R_Column = 'R1' THEN Tests END) R1
, MAX( CASE WHEN R_Column = 'R2' THEN Tests END) R2
, MAX( CASE WHEN R_Column = 'R3' THEN Tests END) R3
, MAX( CASE WHEN R_Column = 'R4' THEN Tests END) R4
FROM yourtable
GROUP BY MATERIALTESTRESULTS_ID, MATERIALID

Open in new window

Author

Commented:
For this example the column names are as you see them in my attachment.  So all the 5 columns's data need to be transposed into one column (excluding NULL's) base on materaltestresults_ID

Author

Commented:
Here is the Select statement that provide the result set.

SELECT     MTR.MATERIALTESTRESULTS_ID, NTC.MATERIALID, R25,R1,R2,R3,R4
FROM         MATERIALTESTRESULTS MTR INNER JOIN
                   [NTC-TRAVELER] NTC ON MTR.TRAVELER_ID = NTC.TRAVELERID
ORDER BY NTC.MATERIALID, MTR.SPLIT
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I'm getting "Invalid column name R" error.  Any suggestions?

Author

Commented:
Ok I messedwith it and added the alias R on each column.
ex:
.....
SELECT TRAVELER_ID,MATERIALTESTRESULTS_ID, R25 R FROM MATERIALTESTRESULTS WHERE R25 IS NOT NULL
....
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
yes, sorry for that...

Author

Commented:
Thank-you!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.