SQL Query to get result from multi records to one row

Question

I want a multi records result in one row like this detail...

SELECT Sampleid, Test, [Result] FROM [tblSartoriusFinal]
where sampleid='77-5080-08'

''''''''''''''''''''''''''''''''''' Output

Sampleid    Test     Result
77-5080-08  Test1    0.0910
77-5080-08  Test2    33.9700
77-5080-08  Test3    16908.0000

............. My requirement is one line output and result field in name against its test like

SampleID,  Test1,  Test2,   Test3
77-5080-08  0.0910  33.9700  16908.0000

What's your dbms? you are looking for pivot table. you can try to use case when aggregate functions — D-Shih, Feb 22 '19 at 06:16
Possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — JeffUK, Feb 22 '19 at 06:25

score 0 · Answer 1 · answered Feb 22 '19 at 06:18

If you are certain that you would only ever expect three test columns in the output, and your database supports ROW_NUMBER, then we can try:

WITH cte AS (
    SELECT SampleID, Test, Result,
        ROW_NUMBER() OVER (PARTITION BY SampleID ORDER BY Test) rn
    FROM [tblSartoriusFinal]
)

SELECT
    SampleID,
    MAX(CASE WHEN rn = 1 THEN Result END) AS Test1,
    MAX(CASE WHEN rn = 2 THEN Result END) AS Test2,
    MAX(CASE WHEN rn = 3 THEN Result END) AS Test3
FROM cte
GROUP BY
    SampleID;

Demo

The above demo is for SQL Server, but it should run without changes needed on Oracle, Postgres, MySQL 8+, and probably a few others as well.

Fahmi · Accepted Answer · 2019-02-22T06:27:43.443

0

You can use conditional aggregation

select Sampleid,max(case when Test='Test1' then Result end) as test1,
,max(case when Test='Test2' then Result end) as test2,
,max(case when Test='Test3' then Result end) as test3
from tablename
group by Sampleid

OR you can use PIVOT if your dbms is sql server

select sampleid,pv.*
from tablename
pivot (max(result) for test in ([Test1],[Test2],[Test3]))

edited Feb 22 '19 at 06:27

answered Feb 22 '19 at 06:19

Fahmi

37,315
5
22
31

score 0 · Answer 3 · answered Feb 22 '19 at 06:22

I think in above detail query result is not clear, i want to mention it again output of query... Sampleid Test Result 77-5080-08 Test1 0.0910 77-5080-08 Test2 33.9700 77-5080-08 Test3 16908.0000

and I want output like this

Sampleid, Test1, Test2, Test3

77-5080-08 0.0910 33.9700 16908.0000

score 0 · Answer 4 · answered Feb 22 '19 at 06:30

You could use a standard pivot for the test column. If there is only 1 result per test and the sample id is the same across all tests then this should work.

Select sampleid, [Test1], [test2], [test3]
From
(Select sampleid, test, result from tblSartoriusFinal) tests
Pivot
(Max(result) for test in ([Test1], [test2], [test3]) pvt;

SQL Query to get result from multi records to one row

4 Answers4

Demo