如何写SQL语句

Question

--drop table [TotalInfo];
 CREATE TABLE [dbo].[TotalInfo](
[MID] [int] NULL,
[value] [varchar](50) NULL,
[type] [int] NULL   --类型
)insert into TotalInfo values(1001,23,1)
insert into TotalInfo values(1002,30,1)
insert into TotalInfo values(1003,40,1)
insert into TotalInfo values(1001,23,2)
insert into TotalInfo values(1002,31,2)
insert into TotalInfo values(1002,32,3)
insert into TotalInfo values(1004,4,1)
insert into TotalInfo values(1004,3,2)
insert into TotalInfo values(1004,1,3)
insert into TotalInfo values(1004,6,4)DECLARE @type NVARCHAR(4000)='1,2,3';DECLARE @typeCount INT=(SELECT isnull(COUNT(1),0) 
                        FROM dbo.[Split](@type,',') s);
                        
DECLARE @colNamesShow NVARCHAR(4000);
DECLARE @colNamesIn NVARCHAR(4000);
DECLARE @sql NVARCHAR(MAX);
set @colNamesShow=(SELECT stuff((SELECT ',p.'+'value'+LTRIM(n.Rn)+' '+'value' 
                                 FROM dbo.Nums n 
                                 WHERE n.Rn<=3 FOR XML PATH('')),1,1,''))
set @colNamesIn=(SELECT stuff((SELECT ','+'value'+LTRIM(n.Rn) 
                               FROM dbo.Nums n 
                               WHERE n.Rn<=3 FOR XML PATH('')),1,1,''))
SET @sql='
SELECT COUNT(DISTINCT  type) ct,MID INTO #mids  FROM TotalInfo ti 
WHERE ti.[type] IN(
SELECT s.[Value] FROM dbo.[Split](@type,'','') s
) GROUP BY ti.MID HAVING COUNT(DISTINCT  type)>=@typeCount
 
 SELECT p.MID,'+@colNamesShow+' FROM (
SELECT ti.MID,''value''+ltrim(ROW_NUMBER() 
OVER (PARTITION BY ti.MID ORDER BY GETDATE())) colName,ti.[value] AS v
FROM TotalInfo ti WHERE ti.MID IN(SELECT m.MID FROM #mids m)
 ) t
 PIVOT (MAX(t.v) FOR t.colName IN('+@colNamesIn+')) p ';
 PRINT @sql
 EXEC sp_executesql @sql
 ,N'@type NVARCHAR(4000),@typeCount int'
 ,@type=@type,@typeCount=@typeCount;
 
 GO
 
--结果MID         value      value      value
----------- ---------- ---------- ----------
1002        30         31         32
1004        4          3          1(2 行受影响)sp_executesql 执行的语句
SELECT COUNT(DISTINCT  type) ct,MID INTO #mids  FROM TotalInfo ti 
WHERE ti.[type] IN(
SELECT s.[Value] FROM dbo.[Split](@type,',') s
) GROUP BY ti.MID HAVING COUNT(DISTINCT  type)>=@typeCount
 
 SELECT p.MID,p.value1 value,p.value2 value,p.value3 value FROM (
SELECT ti.MID,'value'+ltrim(ROW_NUMBER() 
OVER (PARTITION BY ti.MID ORDER BY GETDATE())) colName,ti.[value] AS v
FROM TotalInfo ti WHERE ti.MID IN(SELECT m.MID FROM #mids m)
 ) t
 PIVOT (MAX(t.v) FOR t.colName IN(value1,value2,value3)) p 
--其中使用了 dbo.[Split]方法，是分隔字符串的。。
--使用了 Nums 辅助表。 

Answer 1

请问楼上的，dbo.[Split] 这个方法怎么写，能提供下吗？

Answer 2

--用了临时表#B
CREATE TABLE [dbo].#A(
[MID] [int] NULL,
[value] [varchar](50) NULL,
[type] [int] NULL   --类型
)insert into #A values(1001,23,1)
insert into #A values(1002,30,1)
insert into #A values(1003,40,1)
insert into #A values(1001,23,2)
insert into #A values(1002,31,2)
insert into #A values(1002,32,3)
select * into #B from 
(select mid,[value],[type]=stuff((select ','+convert(varchar(2),[type]) from #A where mid=a.mid for xml path('')),1,1,'')  from #A a) a where type like '%1,2,3%'
declare @s varchar(max)
select @s=isnull(@s+',','')+'['+value+']' from #B group by [value]
set @s='select mid,'+@s+'from #B
 pivot (max(value) for value in ('+@s+'))b' exec(@s)

Answer 3

 declare  @sql varchar (500) 
set @sql= 'select * from table pivot(max(value) for type in('+substring(@type,2,len(@type)-2)+')) a' 
exec(@sql)

Answer 4

CREATE TABLE [dbo].[TotalInfo]
([MID] [int] NULL,
 [value] [varchar](10) NULL,
 [type] [int] NULL)insert into TotalInfo values(1001,'23',1)
insert into TotalInfo values(1002,'30',1)
insert into TotalInfo values(1003,'40',1)
insert into TotalInfo values(1001,'23',2)
insert into TotalInfo values(1002,'31',2)
insert into TotalInfo values(1002,'32',3)
insert into TotalInfo values(1001,'32',4)
insert into TotalInfo values(1002,'35',4)
declare @type nvarchar(50),@tsql nvarchar(4000)
select @type=N'1,2,3,4';  --> 传入参数select @tsql=N'
with t as
(select cast(substring(a.s,b.number,charindex('','',a.s+'','',b.number)-b.number) as int) ''type''
  from (select @type ''s'') a,master.dbo.spt_values b
  where b.type=''P'' and b.number between 1 and len(a.s)
  and substring('',''+a.s,b.number,1)='','')
select MID,['+replace(@type,N',','] ''value'',[')+'] ''value'' '+
'from
(select c.MID,c.value,c.type 
 from TotalInfo c
 where c.type in(select type from t)
 and (select count(distinct d.type) from TotalInfo d 
      where d.type<>c.type and d.MID=c.MID)
 =(select count(1) from t e where e.type<>c.type)) f
pivot(max(value) for type in(['+replace(@type,N',','],[')+'])) g 'exec sp_executesql @tsql,N'@type nvarchar(50)',@type=@type/*
MID         value      value      value      value
----------- ---------- ---------- ---------- ----------
1002        30         31         32         35(1 row(s) affected)
*/

Answer 5

if object_id('TotalInfo') is not null
drop table TotalInfo
go
CREATE TABLE TotalInfo(
 MID [int] NULL,
 value [varchar](50) NULL,
 type [int] NULL   --类型
)insert into TotalInfo values(1001,23,1)
insert into TotalInfo values(1002,30,1)
insert into TotalInfo values(1003,40,1)
insert into TotalInfo values(1001,23,2)
insert into TotalInfo values(1002,31,2)
insert into TotalInfo values(1002,32,3)
go
--动态sql
declare @sql varchar(max)
set @sql=''
select @sql=@sql+','+ '[value'+convert(nvarchar(20),row_number() over(order by mid))  +']'+
'= max(case when [value]='+convert(nvarchar(20),[value])+' then [value] else null end) '
FROM  [TotalInfo] GROUP BY [MID],[value]
--print @sql
exec('select mid'+@sql+' from [TotalInfo] group by mid')