行列互换，分组，查询

Question

CREATE TABLE [dbo].[CRM_SurveyQuestion] (
[CSQ_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CSQ_CST_ID] [int]  ,
[CST_Describe] [nvarchar] (100)  ,
[CST_SelectMode] [int]  ,
[CST_State] [int]  
) CREATE TABLE [dbo].[CRM_SurveyAnswer] (
[CSA_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CSA_CSQ_ID] [int]  ,
[CSA_Answer] [nvarchar] (100) 
)INSERT INTO CRM_SurveyQuestion VALUES(1,'我们的服务态度是否满意?',0,1)
INSERT INTO CRM_SurveyQuestion VALUES(1,'我们的维修质量如何?',0,1)
INSERT INTO CRM_SurveyQuestion VALUES(1,'你对我们哪方面不满意?',1,1)INSERT INTO CRM_SurveyAnswer VALUES(1,'非常满意')
INSERT INTO CRM_SurveyAnswer VALUES(1,'满意')
INSERT INTO CRM_SurveyAnswer VALUES(1,'一般')
INSERT INTO CRM_SurveyAnswer VALUES(1,'不满意')
INSERT INTO CRM_SurveyAnswer VALUES(2,'非常好')
INSERT INTO CRM_SurveyAnswer VALUES(2,'一般')
INSERT INTO CRM_SurveyAnswer VALUES(2,'很差')
INSERT INTO CRM_SurveyAnswer VALUES(3,'价格')
INSERT INTO CRM_SurveyAnswer VALUES(3,'质量')
INSERT INTO CRM_SurveyAnswer VALUES(3,'服务')
INSERT INTO CRM_SurveyAnswer VALUES(3,'环境')DROP TABLE CRM_SurveyQuestion
DROP TABLE CRM_SurveyAnswer--要求结果：
/*
1  我们的服务态度是否满意   非常满意   满意   一般   不满意
2  我们的维修质量如何       非常好     一般   很差  
3  你对我们哪方面不满意     价格    质量   服务   环境
*/

Answer 1

http://topic.csdn.net/u/20080614/17/22e73f33-f071-46dc-b9bf-321204b1656f.html

Answer 2

declare @sql varchar(8000)
set @sql='select a.CSQ_ID,a.CST_Describe'
select @sql=@sql+',['+ltrim(px)+']=max(case px when '''+ltrim(px)+''' then b.CSA_Answer else '''' end)'
from (select distinct px=(select count(1) from CRM_SurveyAnswer where CSA_CSQ_ID=a.CSA_CSQ_ID and CSA_ID<=a.CSA_ID) from CRM_SurveyAnswer a)b
set @sql=@sql+' from CRM_SurveyQuestion a left join (select *,px=(select count(1) from CRM_SurveyAnswer where CSA_CSQ_ID=a.CSA_CSQ_ID and CSA_ID<=a.CSA_ID) from CRM_SurveyAnswer a)b
on a.CSQ_ID=b.CSA_CSQ_ID group by a.CSQ_ID,CST_Describe'
exec(@sql)

Answer 3

set nocount on
CREATE TABLE [dbo].[CRM_SurveyQuestion] (
    [CSQ_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [CSQ_CST_ID] [int]  ,
    [CST_Describe] [nvarchar] (100)  ,
    [CST_SelectMode] [int]  ,
    [CST_State] [int]  
) CREATE TABLE [dbo].[CRM_SurveyAnswer] (
    [CSA_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [CSA_CSQ_ID] [int]  ,
    [CSA_Answer] [nvarchar] (100) 
)INSERT INTO CRM_SurveyQuestion VALUES(1,'我们的服务态度是否满意?',0,1)
INSERT INTO CRM_SurveyQuestion VALUES(2,'我们的维修质量如何?',0,1)
INSERT INTO CRM_SurveyQuestion VALUES(3,'你对我们哪方面不满意?',1,1)INSERT INTO CRM_SurveyAnswer VALUES(1,'非常满意')
INSERT INTO CRM_SurveyAnswer VALUES(1,'满意')
INSERT INTO CRM_SurveyAnswer VALUES(1,'一般')
INSERT INTO CRM_SurveyAnswer VALUES(1,'不满意')
INSERT INTO CRM_SurveyAnswer VALUES(2,'非常好')
INSERT INTO CRM_SurveyAnswer VALUES(2,'一般')
INSERT INTO CRM_SurveyAnswer VALUES(2,'很差')
INSERT INTO CRM_SurveyAnswer VALUES(3,'价格')
INSERT INTO CRM_SurveyAnswer VALUES(3,'质量')
INSERT INTO CRM_SurveyAnswer VALUES(3,'服务')
INSERT INTO CRM_SurveyAnswer VALUES(3,'环境')
go
declare @s varchar(8000)
select @s = isnull(@s+',','') + '[col'+ltrim(px)+'] = max(case when px = '+ltrim(px)+' then cast(csa_answer as varchar) end)'
from (
select distinct px=(select count(1) from CRM_SurveyAnswer where a.CSA_CSQ_ID = CSA_CSQ_ID and CSA_ID <= a.CSA_ID) 
from CRM_SurveyAnswer a
) b
set @s = 'select c.CSQ_CST_ID,cast(c.CST_Describe as varchar) CST_Describe,'+@s+ ' 
from CRM_SurveyQuestion c left join (
select px=(select count(1) from CRM_SurveyAnswer where a.CSA_CSQ_ID = CSA_CSQ_ID and CSA_ID <= a.CSA_ID),* 
from CRM_SurveyAnswer a
) d on d.CSA_CSQ_ID = c.CSQ_CST_ID
group by c.CSQ_CST_ID,c.CST_Describe order by c.CSQ_CST_ID'exec( @s)/*
CSQ_CST_ID  CST_Describe                   col1                           col2                           col3                           col4                           
----------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ 
1           我们的服务态度是否满意?                   非常满意                           满意                             一般                             不满意
2           我们的维修质量如何?                     非常好                            一般                             很差                             NULL
3           你对我们哪方面不满意?                    价格                             质量                             服务                             环境警告: 聚合或其它 SET 操作消除了空值。
*/DROP TABLE CRM_SurveyQuestion
DROP TABLE CRM_SurveyAnswer
set nocount off

Answer 4

CREATE TABLE [dbo].[CRM_SurveyQuestion] (
    [CSQ_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [CSQ_CST_ID] [int]  ,
    [CST_Describe] [nvarchar] (100)  ,
    [CST_SelectMode] [int]  ,
    [CST_State] [int]  
) CREATE TABLE [dbo].[CRM_SurveyAnswer] (
    [CSA_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [CSA_CSQ_ID] [int]  ,
    [CSA_Answer] [nvarchar] (100) 
)INSERT INTO CRM_SurveyQuestion VALUES(1,N'我们的服务态度是否满意?',0,1)
INSERT INTO CRM_SurveyQuestion VALUES(1,N'我们的维修质量如何?',0,1)
INSERT INTO CRM_SurveyQuestion VALUES(1,N'你对我们哪方面不满意?',1,1)INSERT INTO CRM_SurveyAnswer VALUES(1,N'非常满意')
INSERT INTO CRM_SurveyAnswer VALUES(1,N'满意')
INSERT INTO CRM_SurveyAnswer VALUES(1,N'一般')
INSERT INTO CRM_SurveyAnswer VALUES(1,N'不满意')
INSERT INTO CRM_SurveyAnswer VALUES(2,N'非常好')
INSERT INTO CRM_SurveyAnswer VALUES(2,N'一般')
INSERT INTO CRM_SurveyAnswer VALUES(2,N'很差')
INSERT INTO CRM_SurveyAnswer VALUES(3,N'价格')
INSERT INTO CRM_SurveyAnswer VALUES(3,N'质量')
INSERT INTO CRM_SurveyAnswer VALUES(3,N'服务')
INSERT INTO CRM_SurveyAnswer VALUES(3,N'环境')--drop table [CRM_SurveyAnswer],[CRM_SurveyQuestion]
declare @Max int,@s nvarchar(1000)
select top 1 @Max=count(1),@s='' from [CRM_SurveyAnswer] group by [CSA_CSQ_ID] order by count(1) desc
while @Max>0
select @s=','+quotename('COl'+rtrim(@Max))+'=max(case when row='+rtrim(@Max)+' then a.[CSA_Answer] else '''' end)'+@s,@Max=@Max -1
exec('select b.[CSQ_ID],b.[CST_Describe]'+@s+'
from  (select *,row=(select count(1) from CRM_SurveyAnswer where [CSA_CSQ_ID]=a.[CSA_CSQ_ID] and [CSA_ID]<=a.[CSA_ID]) from CRM_SurveyAnswer a)a
join CRM_SurveyQuestion b on b.[CSQ_ID]=a.[CSA_CSQ_ID] group by b.[CSQ_ID],b.[CST_Describe]')CSQ_ID      CST_Describe                                                                                         COl1                                                                                                 COl2                                                                                                 COl3                                                                                                 COl4
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1           我们的服务态度是否满意?                                                                                         非常满意                                                                                                 满意                                                                                                   一般                                                                                                   不满意
2           我们的维修质量如何?                                                                                           非常好                                                                                                  一般                                                                                                   很差                                                                                                   
3           你对我们哪方面不满意?                                                                                          价格                                                                                                   质量                                                                                                   服务                                                                                                   环境(3 個資料列受到影響)

Answer 5

DECLARE  @TA TABLE(ID int,CITYNAME varchar(50))
INSERT @TA SELECT 1,'上海'
UNION ALL SELECT 1,'北京'
UNION ALL SELECT 1,'浙江'  
UNION ALL SELECT 2,'江西'
UNION ALL SELECT 2,'北京'
UNION ALL SELECT 3,'浙江'  
--用临时表来处理
SELECT ID,CITYNAME
INTO #T FROM @TA
ORDER BY ID,CITYNAME
DECLARE @ID INT,@CITYNAME varchar(100)
UPDATE #T SET 
    @CITYNAME=CASE WHEN @ID=ID THEN @CITYNAME+','+ CITYNAME ELSE CITYNAME END,
    @ID=ID,
    CITYNAME=@CITYNAME
SELECT ID,CITYNAME=MAX(CITYNAME) FROM #T GROUP BY ID
DROP TABLE #T
--结果
/*
ID          CITYNAME                                           
----------- -------------------------------------------------- 
1           北京,上海,浙江
2           北京,江西
3           浙江（所影响的行数为 3 行）
*/

Answer 6

CREATE TABLE [dbo].[CRM_SurveyQuestion] (
[CSQ_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CSQ_CST_ID] [int]  ,
[CST_Describe] [nvarchar] (100)  ,
[CST_SelectMode] [int]  ,
[CST_State] [int]  
) CREATE TABLE [dbo].[CRM_SurveyAnswer] (
[CSA_ID] [int] IDENTITY (1, 1) NOT NULL ,
[CSA_CSQ_ID] [int]  ,
[CSA_Answer] [nvarchar] (100) 
)INSERT INTO CRM_SurveyQuestion VALUES(1,'我们的服务态度是否满意?',0,1)
INSERT INTO CRM_SurveyQuestion VALUES(1,'我们的维修质量如何?',0,1)
INSERT INTO CRM_SurveyQuestion VALUES(1,'你对我们哪方面不满意?',1,1)INSERT INTO CRM_SurveyAnswer VALUES(1,'非常满意')
INSERT INTO CRM_SurveyAnswer VALUES(1,'满意')
INSERT INTO CRM_SurveyAnswer VALUES(1,'一般')
INSERT INTO CRM_SurveyAnswer VALUES(1,'不满意')
INSERT INTO CRM_SurveyAnswer VALUES(2,'非常好')
INSERT INTO CRM_SurveyAnswer VALUES(2,'一般')
INSERT INTO CRM_SurveyAnswer VALUES(2,'很差')
INSERT INTO CRM_SurveyAnswer VALUES(2,'null')
INSERT INTO CRM_SurveyAnswer VALUES(3,'价格')
INSERT INTO CRM_SurveyAnswer VALUES(3,'质量')
INSERT INTO CRM_SurveyAnswer VALUES(3,'服务')
INSERT INTO CRM_SurveyAnswer VALUES(3,'环境')SELECT e.[CSQ_ID],e.[CST_Describe],a.[CSA_Answer],b.[CSA_Answer],c.[CSA_Answer],d.[CSA_Answer]
FROM CRM_SurveyAnswer a 
inner join CRM_SurveyAnswer b on  a.[CSA_CSQ_ID]=b.[CSA_CSQ_ID]
inner join CRM_SurveyAnswer c on  c.[CSA_CSQ_ID]=b.[CSA_CSQ_ID] 
inner join CRM_SurveyAnswer d on  c.[CSA_CSQ_ID]=d.[CSA_CSQ_ID] 
left join [CRM_SurveyQuestion] e on e.[CSQ_ID]=a.[CSA_CSQ_ID]
where a.[CSA_ID]<b.[CSA_ID] and b.[CSA_ID]<c.[CSA_ID] and c.[CSA_ID]<d.[CSA_ID]DROP TABLE CRM_SurveyQuestion
DROP TABLE CRM_SurveyAnswer--要求结果：
/*
1  我们的服务态度是否满意   非常满意   满意   一般   不满意
2  我们的维修质量如何       非常好     一般   很差  
3  你对我们哪方面不满意     价格    质量   服务   环境
*/