如何把表1的记录查询结果变成表2 的效果ID Tour_Code Tour_Type Low_Price Shoulder_Price High_Price Hotel_Price
-- -------- ---------- --------- ------------- ---------- -----------
1 TCE-Y01 1 Pax 2363.0 2412.0 2487.0 0
2 TCE-Y01 2-5 Pax 2166.0 2212.0 2280.0 514
3 TCE-Y01 6-9 Pax 1702.0 1738.0 1792.0 477
4 TCE-Y01 10 Pax & Above 1566.0 1599.0 1648.0 454ID Tour_Code Price_Type Tour_Type Price Hotel_Price
-- --------- ----------- ----------- ----- -------------
1 TCE-Y01 Low 1 Pax 2363 0
2 TCE-Y01 Low 2-5 Pax 2166 514
3 TCE-Y01 Low 6-9 Pax 1702 477
4 TCE-Y01 Low 10 Pax & Above 1566 454
5 TCE-Y01 Regular 1 Pax 2412 0
6 TCE-Y01 Regular 2-5 Pax 2212 514
7 TCE-Y01 Regular 6-9 Pax 1738 477
8 TCE-Y01 Regular 10 Pax & Above 1599 454
9 TCE-Y01 High 1 Pax 2487 0
10 TCE-Y01 High 2-5 Pax 2280 514
11 TCE-Y01 High 6-9 Pax 1792 477
12 TCE-Y01 High 10 Pax & Above 1648 454
-- -------- ---------- --------- ------------- ---------- -----------
1 TCE-Y01 1 Pax 2363.0 2412.0 2487.0 0
2 TCE-Y01 2-5 Pax 2166.0 2212.0 2280.0 514
3 TCE-Y01 6-9 Pax 1702.0 1738.0 1792.0 477
4 TCE-Y01 10 Pax & Above 1566.0 1599.0 1648.0 454ID Tour_Code Price_Type Tour_Type Price Hotel_Price
-- --------- ----------- ----------- ----- -------------
1 TCE-Y01 Low 1 Pax 2363 0
2 TCE-Y01 Low 2-5 Pax 2166 514
3 TCE-Y01 Low 6-9 Pax 1702 477
4 TCE-Y01 Low 10 Pax & Above 1566 454
5 TCE-Y01 Regular 1 Pax 2412 0
6 TCE-Y01 Regular 2-5 Pax 2212 514
7 TCE-Y01 Regular 6-9 Pax 1738 477
8 TCE-Y01 Regular 10 Pax & Above 1599 454
9 TCE-Y01 High 1 Pax 2487 0
10 TCE-Y01 High 2-5 Pax 2280 514
11 TCE-Y01 High 6-9 Pax 1792 477
12 TCE-Y01 High 10 Pax & Above 1648 454
from 表1
union all
select Tour_Code,'Regular' as Price_Type,Tour_Type,Regular_Price as Price,Hotel_Price
from 表1
union all
select Tour_Code,'High' as Price_Type,Tour_Type,High_Price as Price,Hotel_Price
from 表1
SELECT
ID, Tour_Code,
Price_Type = 'Low',
Tour_Type,
Price = Low_Price,
Hotel_Price
FROM 表1
UNION ALL
SELECT
ID, Tour_Code,
Price_Type = 'Regular',
Tour_Type,
Price = Shoulder_Price,
Hotel_Price
FROM 表1
UNION ALL
SELECT
ID, Tour_Code,
Price_Type = 'High',
Tour_Type,
Price = High_Price,
Hotel_Price
FROM 表1
union all
select Tour_Code,Price_Type='Regular',Tour_Type,Shoulder_Price,Hotel_Price from tb
union all
select Tour_Code,Price_Type='High ',Tour_Type,High_Price,Hotel_Price from tb
ID Tour_Sorts Tour_Code Tour_Type Low_Price Shoulder_Price High_Price Hotel_Price
-- -- -------- ---------- --------- ------------- ---------- -----------
1 1 TCE-Y01 1 Pax 2363.0 2412.0 2487.0 0
2 1 TCE-Y01 2-5 Pax 2166.0 2212.0 2280.0 514
3 1 TCE-Y01 6-9 Pax 1702.0 1738.0 1792.0 477
4 1 TCE-Y01 10 Pax & Above 1566.0 1599.0 1648.0 454 ID Tour_Code Price_Type Tour_Type Price Hotel_Price Tour_Sorts
-- --------- ----------- ----------- ----- ------------- ----------
1 TCE-Y01 Low 1 Pax 2363 0 1
2 TCE-Y01 Low 2-5 Pax 2166 514 1
3 TCE-Y01 Low 6-9 Pax 1702 477
4 TCE-Y01 Low 10 Pax & Above 1566 454 1
5 TCE-Y01 Regular 1 Pax 2412 0 1
6 TCE-Y01 Regular 2-5 Pax 2212 514 1
7 TCE-Y01 Regular 6-9 Pax 1738 477 1
8 TCE-Y01 Regular 10 Pax & Above 1599 454 1
9 TCE-Y01 High 1 Pax 2487 0 1
10 TCE-Y01 High 2-5 Pax 2280 514 1
11 TCE-Y01 High 6-9 Pax 1792 477 1
12 TCE-Y01 High 10 Pax & Above 1648 454 1
13 TCE-Y02 Low 1 Pax 1648 454 2
14 TCE-Y02 Low 1 Pax 1648 454 2
. . . . . .
GO
CREATE TABLE TB(ID INT, Tour_Sorts INT, Tour_Code VARCHAR(10), Tour_Type VARCHAR(20), Low_Price MONEY, Shoulder_Price MONEY, High_Price MONEY, Hotel_Price MONEY)
-- -- -------- ---------- --------- ------------- ---------- -----------
INSERT TB SELECT 1, 1, 'TCE-Y01' ,'1 Pax' , 2363.0 , 2412.0 , 2487.0 , 0
INSERT TB SELECT 2, 1, 'TCE-Y01' ,'2-5 Pax' , 2166.0, 2212.0, 2280.0, 514
INSERT TB SELECT 3, 1, 'TCE-Y01' , '6-9 Pax' , 1702.0 , 1738.0, 1792.0 , 477
INSERT TB SELECT 4, 1 , 'TCE-Y01', '10 Pax & Above' , 1566.0 , 1599.0 , 1648.0 , 454
select Tour_Code,Price_Type='low',Tour_Type,Low_Price PRICE,Hotel_Price, Tour_Sorts from tb
union all
select Tour_Code,Price_Type='Regular',Tour_Type,Shoulder_Price,Hotel_Price,Tour_Sorts from tb
union all
select Tour_Code,Price_Type='High ',Tour_Type,High_Price,Hotel_Price,Tour_Sorts from tb
/*Tour_Code Price_Type Tour_Type PRICE Hotel_Price Tour_Sorts
---------- ---------- -------------------- --------------------- --------------------- -----------
TCE-Y01 low 1 Pax 2363.0000 .0000 1
TCE-Y01 low 2-5 Pax 2166.0000 514.0000 1
TCE-Y01 low 6-9 Pax 1702.0000 477.0000 1
TCE-Y01 low 10 Pax & Above 1566.0000 454.0000 1
TCE-Y01 Regular 1 Pax 2412.0000 .0000 1
TCE-Y01 Regular 2-5 Pax 2212.0000 514.0000 1
TCE-Y01 Regular 6-9 Pax 1738.0000 477.0000 1
TCE-Y01 Regular 10 Pax & Above 1599.0000 454.0000 1
TCE-Y01 High 1 Pax 2487.0000 .0000 1
TCE-Y01 High 2-5 Pax 2280.0000 514.0000 1
TCE-Y01 High 6-9 Pax 1792.0000 477.0000 1
TCE-Y01 High 10 Pax & Above 1648.0000 454.0000 1
*/
/*Tour_Code Price_Type Tour_Type PRICE Hotel_Price Tour_Sorts
---------- ---------- -------------------- --------------------- --------------------- -----------
TCE-Y01 low 1 Pax 2363.0000 .0000 1
TCE-Y01 low 2-5 Pax 2166.0000 514.0000 1
TCE-Y01 low 6-9 Pax 1702.0000 477.0000 1
TCE-Y01 low 10 Pax & Above 1566.0000 454.0000 1
TCE-Y01 Regular 1 Pax 2412.0000 .0000 1
TCE-Y01 Regular 2-5 Pax 2212.0000 514.0000 1
TCE-Y01 Regular 6-9 Pax 1738.0000 477.0000 1
TCE-Y01 Regular 10 Pax & Above 1599.0000 454.0000 1
TCE-Y01 High 1 Pax 2487.0000 .0000 1
TCE-Y01 High 2-5 Pax 2280.0000 514.0000 1
TCE-Y01 High 6-9 Pax 1792.0000 477.0000 1
TCE-Y01 High 10 Pax & Above 1648.0000 454.0000 1
TCE-Y02 low 1 Pax 2363.0000 .0000 1
TCE-Y02 low 2-5 Pax 2166.0000 514.0000 1
TCE-Y02 low 6-9 Pax 1702.0000 477.0000 1
TCE-Y02 low 10 Pax & Above 1566.0000 454.0000 1
TCE-Y02 Regular 1 Pax 2412.0000 .0000 1
TCE-Y02 Regular 2-5 Pax 2212.0000 514.0000 1
TCE-Y02 Regular 6-9 Pax 1738.0000 477.0000 1
TCE-Y02 Regular 10 Pax & Above 1599.0000 454.0000 1
TCE-Y02 High 1 Pax 2487.0000 .0000 1
TCE-Y02 High 2-5 Pax 2280.0000 514.0000 1
TCE-Y02 High 6-9 Pax 1792.0000 477.0000 1
TCE-Y02 High 10 Pax & Above 1648.0000 454.0000 1tce-yo3
union all
select Tour_Code,Price_Type='Regular',Tour_Type,Shoulder_Price,Hotel_Price,Tour_Sorts from tb
union all
select Tour_Code,Price_Type='High ',Tour_Type,High_Price,Hotel_Price,Tour_Sorts from tb