The Windows setting do not affect SQL Server parsing dates. There is a server level default language that can be overridden by one set at the login level.
As it stands, the "safe" format for SQL Server is yyyymmdd anyway. Try to use that
From CREATE LOGIN
Specifies the default language to be assigned to the login. If this option is not included, the default language is set to the current default language of the server. If the default language of the server is later changed, the default language of the login remains unchanged.
For the server, you'd use "default language" via sp_configure
And you can run SET LANGUAGE or SET DATEFORMAT anytime you want of course
In sql-server-2005 or later, the the UNPIVOT and PIVOT operators can be used:
Sample data:
DECLARE @Source AS TABLE
(
ID char(1) PRIMARY KEY,
PD1 integer NOT NULL,
PD2 integer NOT NULL,
PD3 integer NOT NULL,
PD4 integer NOT NULL,
PD5 integer NOT NULL
);
INSERT @Source
(ID, PD1, PD2, PD3, PD4, PD5)
VALUES
('A', 1, 2, 3, 4, 5),
('B', 6, 7, 8, 9, 0);
Query:
SELECT
Pvt.Price,
Pvt.A,
Pvt.B
FROM @Source AS s
UNPIVOT
(
Val
FOR Price IN (PD1, PD2, PD3, PD4, PD5)
) AS Unpvt
PIVOT
(
MAX(Val)
FOR ID IN (A, B)
) AS Pvt;
Output:
╔═══════╦═══╦═══╗
║ Price ║ A ║ B ║
╠═══════╬═══╬═══╣
║ PD1 ║ 1 ║ 6 ║
║ PD2 ║ 2 ║ 7 ║
║ PD3 ║ 3 ║ 8 ║
║ PD4 ║ 4 ║ 9 ║
║ PD5 ║ 5 ║ 0 ║
╚═══════╩═══╩═══╝
Execution plan:
The query can also be written without PIVOT and UNPIVOT:
SELECT
f1.Price,
A = MAX(CASE WHEN s.ID = 'A' THEN f1.Val END),
B = MAX(CASE WHEN s.ID = 'B' THEN f1.Val END)
FROM @Source AS s
CROSS APPLY
(
SELECT 'PD1', s.PD1 UNION ALL
SELECT 'PD2', s.PD2 UNION ALL
SELECT 'PD3', s.PD3 UNION ALL
SELECT 'PD4', s.PD4 UNION ALL
SELECT 'PD5', s.PD5
) AS f1 (Price, Val)
GROUP BY
f1.Price;
Try the SQLFiddle here
Best Answer
A better solution would be something like...
Result: