Posted by Erland Sommarskog on 07/25/06 21:16

sql guy123 (stoppal@hotmail.com) writes:
> Thank you for your information. I never learned the actual formula,
> only how to click the linear regression button in excel.
>
> Is there a way to transform the table into the correct format? Maybe a
> tranverse function in SQL?

There is an UNPIVOT operator in SQL 2005. But it's mainly syntactic
sugar, and you can easily unpivot without it:

SELECT year, value = CASE year WHEN 2001 THEN [2001]
WHEN 2002 THEN [2002]
...
END
FROM tbl
CROSS JOIN (SELECT 2001 UNION ALL
SELECT 2002 UNION ALL
...) AS year


My thought, though, was that you would redesign the table on a
permanent basis.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]