- Original posted: http://community.9cbs.net/expert/topic/4617/4617995.XML? Temp = 4.882449E-02 Time Sales amount 2006-01-02 502006-01-02 No. 202006- 01-02 Night 302006-01-03 年 02006-01-03 02006-01-03 Night 502006-01-03 Zero 502006-01-04 802006-01 02006, China 01-04 night 202006-01-04 Zero 40 .................................... ................................................ ... Can I use a row to transfer the query results of the above data in the data query:
Date in the morning, noon, noon, the month, small, small count 2006-01-02 50 20 30 40 1402006-01-03 40 60 50 50 2002006-01-04 80 60 30 20 190 ............. .......
- Handling method in SQL 20005:
- Test Environment CREATE TABLE T (Date DateTime, Time Varchar (20), Sales Cost Int) Insert Into T Select '2006-01-02', 'Morned', 50Union All Select '2006-01-02', ' Noon ', 20Union All Select' 2006-01-02 ',' Night ', 30UNION All Select' 2006-01-02 ',' Zero, 40Union All Select '2006-01-03', 'Morned', 40Union All select '2006-01-03', 'noon', 60UNION All Select '2006-01-03', 'Night', 50UNION All Select '2006-01-03', 'Zero, 50Union All Select' 2006 -01-04 ',', 80UNION All Select '2006-01-04', 'noon', 60UNION All Select '2006-01-04', 'Night', 20UNION All Select '2006-01-04' , 'Zero, 40 - query select *, amount small count = (SELECT SUM) from T wherere date = pt. Date) from t as tabpivot (Max) for time in ([ Morning], [noon], [evening], [zero])) AS PT - Results / * Date Morning noon Morning Morning Tree ----------------- ------ ------------------------------------------- ----------- 2006-01-02 00: 00: 00.000 50 20 30 40 1402006-01-03 00: 00: 00 0.0 60 50 50 2002006-01-04 00: 00: 01-04 00: 00: 80 60 20 40 200 (3 lines affected) * / - Delete Test Environment DROP TABLE T
--- Dynamic SQL
Declare @s varchar (max) set @s = '' SELECT @ s = @ s ', [' time ']' from t group by time set @ s = stuff (@ s, 1, 1, '') Exec ('SELECT date,' @ S ', amount small count = (SELECT SUM) from t wherere date = pt. Date) from t as tabpivot (Max (sales amount) for time in (' @ S ')) AS PT')
