这个行列转换的sql该怎么写？

显示全部楼层 · 2005-10-30 17:05:33

I have a table as below:

hit name
comment
----------- -------------------------------------------------- --------------------------------------------------
1 DisplayOrder
NULL
1 ListFilter
NULL
1 ListIsTaxonomy
NULL
1 ListItemCode
6YX30Y
1 ListItemDescription
TENOR TYPE
1 ListItemValue
6 year by 30 year
1 ListLabel
NULL
1 ListLanguageCode
ENG
1 ListMultiLanguageRule
ENG
1 ListSource
NDA
1 ListSubType
TENOR TYPE
1 ListTaxonomySeparator
NULL
1 ListType
Other Codes
1 BusinessEntity
DICTIONARY DICTIONARYxLIST
1 DocDateTime
2008-04-09T12:30:47Z
1 PI
TENOR TYPE|6YX30Y
1 Source
SYS
2 DisplayOrder
NULL
2 ListFilter
NULL
2 ListIsTaxonomy
NULL
2 ListItemCode
6YX3Y
2 ListItemDescription
TENOR TYPE
2 ListItemValue
6 year by 3 year
2 ListLabel
NULL
2 ListLanguageCode
ENG
2 ListMultiLanguageRule
ENG
2 ListSource
NDA
2 ListSubType
TENOR TYPE
2 ListTaxonomySeparator
NULL
2 ListType
Other Codes
2 BusinessEntity
DICTIONARY DICTIONARYxLIST
2 DocDateTime
2008-04-09T12:30:47Z
2 PI
TENOR TYPE|6YX3Y
2 Source
SYS

Now I want to get:

hit DisplayOrder ListFilter ....ListItemCode ....PI Source
---------------------------------------------------------------------------------------------------------------------------
1 null null .....6YX30Y....TENOR TYPE|6YX30Y SYS
2 null null .....6YX30Y....TENOR TYPE|6YX3Y SYS

How sould I write SQL?It's 2005.Thanks!

千问 · 2005-10-30 17:05:33

Example:
CREATE TABLE SALES1
(
[Year] INT,
Quarter CHAR(2),
Amount FLOAT
)
GO
INSERT INTO SALES1 VALUES (2001, 'Q1', 80)
INSERT INTO SALES1 VALUES (2001, 'Q2', 70)
INSERT INTO SALES1 VALUES (2001, 'Q3', 55)
INSERT INTO SALES1 VALUES (2001, 'Q3', 110)
INSERT INTO SALES1 VALUES (2001, 'Q4', 90)
INSERT INTO SALES1 VALUES (2002, 'Q1', 200)
INSERT INTO SALES1 VALUES (2002, 'Q2', 150)
INSERT INTO SALES1 VALUES (2002, 'Q2', 40)
INSERT INTO SALES1 VALUES (2002, 'Q2', 60)
INSERT INTO SALES1 VALUES (2002, 'Q3', 120)
INSERT INTO SALES1 VALUES (2002, 'Q3', 110)
INSERT INTO SALES1 VALUES (2002, 'Q4', 180)
GO
SELECT * FROM SALES1
PIVOT
(SUM (Amount) --Aggregate the Amount column using SUM
FOR [Quarter] --Pivot the Quarter column into column headings
IN (Q1, Q2, Q3, Q4)) --use these quarters
AS P
GO

千问 · 2005-10-30 17:05:33

ms你这个不好弄。呵呵。

千问 · 2005-10-30 17:05:33

处理数字我知道。关键这里是字符型的。用不了sum

千问 · 2005-10-30 17:05:33

俺就是这个意思。
所以说不好弄，嘿嘿。

千问 · 2005-10-30 17:05:33

SELECT hit, DisplayOrder, ListFilter, ListItemCode, PI, Source
FROM
(SELECT hit, name, comment
from qr_dictionary_result) as Source
PIVOT
(
max(comment)
for name in ([displayorder], [listfilter], [listitemcode], [pi], [source])
) as pvt
这样就可以了。

千问 · 2005-10-30 17:05:33

strong！

千问 · 2005-10-30 17:05:33

假设源表为tttt
select distinct name into #1 from tttt
declare@sqlvarchar(5000)
set@sql='selecttttt.hit'
select@sql=@sql+',max(case#1.namewhen'''+#1.name+'''thentttt.commentelse''''end)['+#1.name+']'
from tttt right join #1 on tttt.name=#1.name group by #1.name
set@sql= @sql+'from tttt left join #1 on tttt.name=#1.name group by tttt.hit'
exec(@sql)