SQL rows to columns without aggregation

Question

I have a table where measurement results are stored like...

╔═════════╦═══════╗
║ TEST_ID ║  VAL  ║
╠═════════╬═══════╣
║ 1       ║ 0.304 ║
║ 1       ║ 0.31  ║
║ 1       ║ 0.306 ║
║ 1       ║ 0.302 ║ 
║ 2       ║ 2.3   ║
║ 2       ║ 2.5   ║
║ 2       ║ 2.4   ║
║ 2       ║ 2.9   ║ 
║ 3       ║ 9.8   ║
║ 3       ║ 4.4   ║
║ 3       ║ 5.1   ║
║ 3       ║ 5.2   ║ 
╚═════════╩═══════╝

Now there will be an undetermined number of tests, but if that helps, I know that there always be four results for a test. Also, this is a result set of a query, all measurements have their unique ID, but they don't have a date/order field. What I would like to see in the result set is

╔═════════╦═══════╦═══════╦═══════╦═══════╗
║ TEST_ID ║  VAL1 ║  VAL2 ║  VAL3 ║  VAL4 ║
╠═════════╬═══════╬═══════╬═══════╬═══════╣
║ 1       ║ 0.304 ║ 0.31  ║ 0.306 ║ 0.302 ║
║ 2       ║ 2.3   ║ 2.5   ║ 2.9   ║ 2.4   ║
║ 3       ║ 9.8   ║ 4.4   ║ 5.1   ║ 5.2   ║
╚═════════╩═══════╩═══════╩═══════╩═══════╝

I examined PIVOT, but it doesn't help for two reasons:

It requires aggregation and I don't want to aggregate anything.
I don't know the number and ID of all the tests (in reality we are talking about hundreds of tests and practically any possible outcome between -10 and 10.)

So my question is if its even doable in (T)SQL and if it is, how to do it.

p.s. No, this question is neither a duplicate of

TSQL Pivot without aggregate function

nor a duplicate of Efficiently convert rows to columns in sql server

nor a duplicate of SQL Server dynamic PIVOT query?

Both questions have answers for cases where the header value set is present in the source table and finite. In my case neither of those premises are true.

This question is definitely a dupe of your prior question. The question curators have is whether your prior question is a dupe of something else. The trouble you have on your last question is that your employer won't let you share the code you have tried. In those circumstances the community will generally side with the gold badge holder. It would be good to get your employer change their policy - there are surely no real IP issues in SQL snippets. — halfer, Nov 01 '19 at 13:07
My prior question was closed before anyone could answer under the false accusation of being a duplicate. Since it was closed and I had no time to wait if somebody is kind enough to actually read it and vote for reopening, I wrote another one (to which I got proper answer before the same person closed it out of plain malice). If Stack Overflow provided a mechanism to flag the "closed as dupe" as harmful, I would have done that. I debated why my question isn't a dupe with the reporter in detail, and (s)he couldn't make a proper argument but (s)he has more rep, so it was closed. — mg30rg, Nov 01 '19 at 13:12
@halfer The very fact that this question has helpful votes proves that this is quality content which has viability on Stack Overflow. The problem might- or might not be trivial for DBAs, but as far as I am concern, this is Stack Overflow and not https://dba.stackexchange.com. — mg30rg, Nov 01 '19 at 13:17
This question, along with the marked duplicate, is being discussed on [meta](https://meta.stackoverflow.com/questions/390988/why-do-people-with-high-reputation-flag-perfectly-valid-questions-as-duplicates). — Thom A, Nov 01 '19 at 14:33

score 1 · Accepted Answer · answered Nov 01 '19 at 11:08

This should work. With the ROW_NUMBER() you will create the header values, which are missing from your table.

As for the aggregation bit, you can see in the answers of the linked post that "You can use the MAX aggregate, it would still work. MAX of one value = that value"

select
    test_id
    ,[1] as val1
    ,[2] as val2
    ,[3] as val3
    ,[4] as val4
from (
    select
        test_id
        ,val
        ,seq = ROW_NUMBER() over (PARTITION by test_id order by test_id asc)
    from @tbl
) t
pivot (
    max(val)
    for seq in ([1], [2], [3], [4])
)p

score 1 · Answer 2 · answered Nov 01 '19 at 11:12

You can add a sequence number using ROW_NUMBER() and then pivot.

select
    TEST_ID
    ,[1]
    ,[2]
    ,[3]
    ,[4]
FROM(
  SELECT *, ROW_NUMBER() OVER (PARTITION BY TEST_ID ORDER BY TEST_ID) 
  AS VALNUM from testResults
 ) dataTbl
 PIVOT (
    MAX(val)
    for VALNUM in ([1], [2], [3], [4])
) pivotTbl

Live example: http://sqlfiddle.com/#!18/cf668/11

SQL rows to columns without aggregation

2 Answers2