34,575
社区成员
发帖
与我相关
我的任务
分享
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 你对我们哪方面不满意 价格 质量 服务 环境
*/
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 你对我们哪方面不满意 价格 质量 服务 环境
*/
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 行)
*/
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 個資料列受到影響)
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
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)