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 ║
╚══════════════════════════╝
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) |