Question

sql 一列如何按条件分成多列?

表Ith中有以下列item date type quty
1000 20100101 A 100
2000 20100101 B 200
1000 20100101 C 100
2000 20100101 D 100
.... ........ . ...请问如何在SQL中实现
item date typeA typeB typeC tyteD
1000 20100101 100 0 100 0
2000 20100101 0 200 0 200
.... ....... ... ... ... ...

Answer 1

------------------------------------------------------------------------
-- author:jc_liumangtu(【DBA】小七)
-- date: 2010-03-26 09:37:30
-- version:
-- Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
-- Oct 14 2005 00:33:37
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
------------------------------------------------------------------------use test
set nocount on
if object_id('test4','U') is not null
drop table test4
go
create table test4
(item int ,date char(8),type char(2),quty int)
insert into test4
select 1000 , '20100101', 'A' , 100 union all
select 2000 , '20100101' ,'B' , 200 union all
select 1000 , '20100101' ,'C' , 100 union all
select 2000 , '20100101' , 'D' , 100
select * from test4select item ,date
,max(case type when 'A' then quty else 0 end) [typeA]
,max(case type when 'B' then quty else 0 end) [typeB]
,max(case type when 'C' then quty else 0 end) [typeC]
,max(case type when 'D' then quty else 0 end) [typeD]
from test4 group by item,date
item date type quty
----------- -------- ---- -----------
1000 20100101 A 100
2000 20100101 B 200
1000 20100101 C 100
2000 20100101 D 100item date typeA typeB typeC typeD
----------- -------- ----------- ----------- ----------- -----------
1000 20100101 100 0 100 0
2000 20100101 0 200 0 100

Answer 2

看下case语句