Pivot rows to columns without aggregate

Question

Trying to figure how to write a dynamic pivot sql statement. Where TEST_NAME could have up to 12 different values (thus having 12 columns). Some of the VAL will be Int, Decimal, or Varchar data types. Most of the examples I have seen have some from of aggregate included. I am looking to for a straight value pivot.

Source Table   ╔═══════════╦══════╦═══════╗ ║ TEST_NAME ║ SBNO ║  VAL  ║ ╠═══════════╬══════╬═══════╣ ║ Test1     ║    1 ║ 0.304 ║ ║ Test1     ║    2 ║ 0.31  ║ ║ Test1     ║    3 ║ 0.306 ║ ║ Test2     ║    1 ║ 2.3   ║ ║ Test2     ║    2 ║ 2.5   ║ ║ Test2     ║    3 ║ 2.4   ║ ║ Test3     ║    1 ║ PASS  ║ ║ Test3     ║    2 ║ PASS  ║ ╚═══════════╩══════╩═══════╝   Desired Output  ╔══════════════════════════╗ ║ SBNO Test1 Test2   Test3 ║ ╠══════════════════════════╣ ║ 1    0.304  2.3    PASS  ║ ║ 2    0.31   2.5    PASS  ║ ║ 3    0.306  2.4    NULL  ║ ╚══════════════════════════╝ 

Answer 1

The PIVOT function requires an aggregation to get it to work. It appears that your VAL column is a varchar so you will have to use either the MAX or MIN aggregate functions.

If the number of tests is limited, then you can hard-code the values:

select sbno, Test1, Test2, Test3 from (   select test_name, sbno, val   from yourtable ) d pivot (   max(val)   for test_name in (Test1, Test2, Test3) ) piv; 

See SQL Fiddle with Demo.

In your OP, you stated that you will have an larger number of rows to turn into columns. If that is the case, then you can use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),     @query  AS NVARCHAR(MAX)  select @cols = STUFF((SELECT distinct ',' + QUOTENAME(TEST_NAME)                      from yourtable             FOR XML PATH(''), TYPE             ).value('.', 'NVARCHAR(MAX)')          ,1,1,'')  set @query = 'SELECT sbno,' + @cols + '              from               (                 select test_name, sbno, val                 from yourtable             ) x             pivot              (                 max(val)                 for test_name in (' + @cols + ')             ) p '  execute(@query) 

See SQL Fiddle with Demo.

Both versions will give the same result:

| SBNO | TEST1 | TEST2 |  TEST3 | --------------------------------- |    1 | 0.304 |   2.3 |   PASS | |    2 |  0.31 |   2.5 |   PASS | |    3 | 0.306 |   2.4 | (null) |