create table tb2
(Customer varchar(100),
product varchar(100),
number int
)
insert into tb2
select '中通冷氣','本田冷凝器',2 union all
select '中通冷氣','日産冷凝器',5 union all
select '中通冷氣','制冷霜',10 union all
select '中通冷氣','五十鈴壓縮機', 1 union all
select '大若公司','皇冠壓縮機',5 union all
select '大若公司','皇冠蒸發器',2 union all
select '天宇公司','皇冠蒸發器',5goselect identity(int,1,1) as id ,* into #temp from tb2
select case when product ='' then Customer else '' end as Customer,product,number from (
select min(id) as id,Customer,''as product,''as number from #temp
group by Customer
union all
select id,Customer,product,convert(varchar(100),number) from #temp
)a
order by id ,product
drop table #temp,tb2/*中通冷氣
本田冷凝器 2
日産冷凝器 5
制冷霜 10
五十鈴壓縮機 1
大若公司
皇冠壓縮機 5
皇冠蒸發器 2
天宇公司
皇冠蒸發器 5*/
(Customer nvarchar(50),
product nvarchar(50),
number int
)
insert into tb2
select '中通冷氣','本田冷凝器',2 union all
select '中通冷氣','日産冷凝器',5 union all
select '中通冷氣','制冷霜',10 union all
select '中通冷氣','五十鈴壓縮機', 1 union all
select '大若公司','皇冠壓縮機',5 union all
select '大若公司','皇冠蒸發器',2 union all
select '天宇公司','皇冠蒸發器',5goselect ID=Identity(int,1,1),* into #t from tb2select min(ID) as ID into #minID from #t group by Customerselect customer =case when a.ID=b.ID then a.Customer else '' end,a.product,a.number
from #t as a
left outer join #minID as b on a.ID=b.IDdrop table #minID
drop table #t
drop table tb2--Result
customer product number
-------------------- -------------------- -----------
中通冷氣 本田冷凝器 2
日?冷凝器 5
制冷霜 10
五十鈴壓縮機 1
大若公司 皇冠壓縮機 5
皇冠蒸發器 2
天宇公司 皇冠蒸發器 5
customer product number
中通冷氣 本田冷凝器 2
日?冷凝器 5
制冷霜 10
五十鈴壓縮機 1
大若公司 皇冠壓縮機 5
皇冠蒸發器 2
天宇公司 皇冠蒸發器 5
中通冷氣 本田冷凝器 2
日?冷凝器 5
制冷霜 10
五十鈴壓縮機 1
大若公司 皇冠壓縮機 5
皇冠蒸發器 2
天宇公司 皇冠蒸發器 5
關於我的問題中,發現你給出的語句有點不解,我數據中某表已有我給出的原資了,為何需要insert into tb2.... 呢?不知能不能加你QQ或MSN像你學習一下?
TO winternet(冬天) 你的語句得出的結果不符合我想要的哦..
這也不單明白
select customer =case when a.ID=b.ID then a.Customer else '' end,a.product,a.number
from #t as a
left outer join #minID as b on a.ID=b.ID
(Customer varchar(100),
product varchar(100),
number int
)
insert into tb2
select '中通冷氣','本田冷凝器',2 union all
select '中通冷氣','日産冷凝器',5 union all
select '中通冷氣','制冷霜',10 union all
select '中通冷氣','五十鈴壓縮機', 1 union all
select '大若公司','皇冠壓縮機',5 union all
select '大若公司','皇冠蒸發器',2 union all
select '天宇公司','皇冠蒸發器',5
select Customer,product,number from
(select '' as Customer ,T.product, cast(T.number as varchar) as number,(select count(*) from tb2 where Customer=T.Customer) as T_Flag from tb2 T
union
select Customer,'' as product,'' as number,T_Flag from
(select Customer,count(*) as T_Flag from tb2 group by Customer) T) M
order by T_Flag,case when Customer='' then 1 else 0 end
(select '' as Customer ,T.product, cast(T.number as varchar) as number,(select count(*) from tb2 where Customer=T.Customer) as T_Flag from tb2 T
union
select Customer,'' as product,'' as number,T_Flag from
(select Customer,count(*) as T_Flag from tb2 group by Customer) T) M
order by T_Flag DESC ,case when Customer='' then 1 else 0 end
select Customer,product,(case when product is null then null else number end) number
from
(
select Customer,product,max(number) number
from [table]
group by Customer,product with rollup
) t
where customer is not null
order by 1,2,3
(case when product is null then Customer else null end) Customer1,
product,
(case when product is null then null else number end) number
from
(
select Customer,product,max(number) number
from [tb2]
group by Customer,product with rollup
) t
where customer is not null
order by Customer,product