Pivot rows to columns without aggregate – This article will take you through the common SQL errors that you might encounter while working with sql, pivot, . The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
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 ║ ╚══════════════════════════╝
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
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) |
There is no any way to PIVOT without aggregating.
CREATE TABLE #table1 ( TEST_NAME VARCHAR(10), SBNO VARCHAR(10), VAL VARCHAR(10) ); INSERT INTO #table1 (TEST_NAME, SBNO, VAL) VALUES ('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') WITH T AS ( SELECT SBNO, VAL, TEST_NAME FROM #table1 ) SELECT * FROM T PIVOT (MAX(VAL) FOR TEST_NAME IN([Test1], [Test2], [Test3])) P
A workaround might be to ensure that the obligatory aggregation is only ever applied to a single record. In Excel for example the output could be:
where Row Labels includes at the bottom a column of cells with unique index numbers.
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.