表一如下:
ID ProName NUM ProColor ProSize
1 产品 1 黑 1001
2 产品 1 白 1002
表二如下:
ID Size
1001 S
1002 M
1003 L要求输出:
ProName ProColor S M L
产品 黑 1 0 0
产品 白 0 1 0
ID ProName NUM ProColor ProSize
1 产品 1 黑 1001
2 产品 1 白 1002
表二如下:
ID Size
1001 S
1002 M
1003 L要求输出:
ProName ProColor S M L
产品 黑 1 0 0
产品 白 0 1 0
insert into @t1 select 1,'产品',1,'黑','1001'
union all select 2,'产品',1,'白','1002'declare @t2 table(ID varchar(10),Size varchar(5))
insert into @t2 select '1001','S'
union all select '1002','M'
union all select '1003','L'select * from
(
select ProName,ProColor,Size from @t1 a right join @t2 b
on a.ProSize=b.ID
)tb
pivot
(count(size)
for size in ([S],[M],[L])
)as pit
where ProName is not null
order by ProColor desc/*
ProName ProColor S M L
---------- -------------------- ----------- ----------- -----------
产品 黑 1 0 0
产品 白 0 1 0(2 行受影响)*/
drop table 表一
gocreate table 表一(ID int,ProName varchar(10),NUM int,ProColor varchar(10),ProSize int)
insert into 表一(ID,ProName,NUM,ProColor,ProSize) values(1,'产品',1,'黑','1001')
insert into 表一(ID,ProName,NUM,ProColor,ProSize) values(2,'产品',1,'白','1002')
if object_id('pubs..表二') is not null
drop table 表二
gocreate table 表二(ID int,Size varchar(10))
insert into 表二(ID,Size) values(1001, 'S')
insert into 表二(ID,Size) values(1002, 'M')
insert into 表二(ID,Size) values(1003, 'L')select proname , procolor ,
(case when size = 'S' then 1 else 0 end) as S,
(case when size = 'M' then 1 else 0 end) as M,
(case when size = 'L' then 1 else 0 end) as L
from
(
select a.proname,a.procolor,b.size from 表一 a,表二 b where a.prosize = b.id
) tdrop table 表一,表二
/*
proname procolor S M L
---------- ---------- ----------- ----------- -----------
产品 黑 1 0 0
产品 白 0 1 0(所影响的行数为 2 行)
*/
declare @t1 table(ID int, ProName varchar(10),NUM int ,ProColor varchar(20), ProSize varchar(10))
insert into @t1 select 1,'产品',1,'黑','1001'
union all select 2,'产品',1,'白','1002'declare @t2 table(ID varchar(10),Size varchar(5))
insert into @t2 select '1001','S'
union all select '1002','M'
union all select '1003','L'
select a.ID,a.ProName,sum(case when ProSize='1001' then NUM else 0 end) as S,
sum(case when ProSize='1002' then NUM else 0 end) as M,
sum(case when ProSize='1003' then NUM else 0 end) as L
FROM @t1 a left join @t2 b on a.ProSize=b.Size
group by a.ID,a.ProName
Insert Into T1 Select 1,N'产品',1,'黑','1001'
Union All Select 2,N'产品',1,'白','1002'Create Table T2 (ID Varchar(10),Size Varchar(5))
Insert Into T2 Select '1001','S'
Union All Select '1002','M'
Union All Select '1003','L'
GO
Declare @S Varchar(8000)
Select @S = ' Select A.ProName, A.ProColor'
Select @S = @S + ', SUM(Case B.[Size] When ''' + [Size] + ''' Then 1 Else 0 End) As ' + [Size]
From T2 Order By ID
Select @S = @S + ' From T1 A Inner Join T2 B On A.ProSize = B.ID Group By A.ProName, A.ProColor, A.ProSize Order By A.ProSize'
EXEC(@S)
GO
Drop Table T1, T2
--Result
/*
ProName ProColor L M S
产品 黑 1 0 0
产品 白 0 1 0
*/
ID ProName NUM ProColor ProSize
1 产品 1 黑 1001
2 产品 1 白 1002
2 产品 1 白 1001
2 产品 1 白 1003表二如下:(表二的记录不固定是S,M,L,可能还有XL,XXL,XXXL)
ID Size
1001 S
1002 M
1003 L要求输出:
ProName ProColor S M L
产品 黑 1 0 0
产品 白 1 1 1
declare @sql varchar(8000)
select @sql='select ProName, ProColor, '
select @sql=@sql+'sum(case when ProSize='''+b.ID+''' then NUM else 0 end) as '+b.Size +', ' FROM t2 b group by b.ID,b.Size
print @sql set @sql=left(@sql,len(@sql)-1)
exec(@sql+' from t1 group by ProName, ProColor')
drop table 表一
gocreate table 表一(ID int,ProName varchar(10),NUM int,ProColor varchar(10),ProSize int)
insert into 表一(ID,ProName,NUM,ProColor,ProSize) values(1,'产品',1,'黑','1001')
insert into 表一(ID,ProName,NUM,ProColor,ProSize) values(2,'产品',1,'白','1002')
insert into 表一(ID,ProName,NUM,ProColor,ProSize) values(2,'产品',1,'白','1001')
insert into 表一(ID,ProName,NUM,ProColor,ProSize) values(2,'产品',1,'白','1003')
if object_id('pubs..表二') is not null
drop table 表二
gocreate table 表二(ID int,Size varchar(10))
insert into 表二(ID,Size) values(1001, 'S')
insert into 表二(ID,Size) values(1002, 'M')
insert into 表二(ID,Size) values(1003, 'L')
insert into 表二(ID,Size) values(1004, 'XL')
insert into 表二(ID,Size) values(1005, 'XXL')
insert into 表二(ID,Size) values(1006, 'XXXL')
godeclare @sql varchar(8000)
set @sql = 'select ProName , ProColor'
select @sql = @sql + ' , max(case size when ''' + size + ''' then 1 else 0 end) [' + size + ']'
from (select distinct size from (select a.ProName , a.ProColor,b.size from 表一 a,表二 b where a.prosize = b.id) t) as m
set @sql = @sql + ' from (select a.ProName , a.ProColor,b.size from 表一 a,表二 b where a.prosize = b.id) t group by ProName , ProColor'
exec(@sql) drop table 表一,表二
/*
ProName ProColor L M S
---------- ---------- ----------- ----------- -----------
产品 白 1 1 1
产品 黑 0 0 1
*/
都达不到我的要求,可能是我问题没有描述清楚,是这样的------------------我的上面的寫的是固定情況下用的,但是我寫的就是在不固定的情況下使用的。
insert into @t1 select 1,'产品',1,'黑','1001'
union all select 2,'产品',1,'白','1002'
union all select 2,'产品',1,'白','1001'
union all select 2,'产品',1,'白','1003'declare @t2 table(ID varchar(10),Size varchar(5))
insert into @t2 select '1001','S'
union all select '1002','M'
union all select '1003','L'select * from
(
select ProName,ProColor,Size from @t1 a right join @t2 b
on a.ProSize=b.ID
)tb
pivot
(count(size)
for size in ([S],[M],[L])
)as pit
where ProName is not null
order by ProColor desc
/*
ProName ProColor S M L
---------- -------------------- ----------- ----------- -----------
产品 黑 1 0 0
产品 白 1 1 1(2 行受影响)*/