asked on

How to group columns into rows

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!

Guy Hengel [angelIII / a3]

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

Wayne Burr

ASKER

uggg.
MatTests.jpg

Guy Hengel [angelIII / a3]

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

Wayne Burr

ASKER

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

Wayne Burr

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Wayne Burr

ASKER

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

Wayne Burr

ASKER

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]

yes, sorry for that...

Wayne Burr

ASKER

Thank-you!