一个查询的简单问题

mailto520 2008-12-12 04:36:40

table1
Company Genre Model Total
IBM IC A2051 200
IBM PCB 12ad 300
IBM IC ded22 220
IBM PCB aed45 220
Lenovo IC adde2 500
Lenovo PCB 15a 100
Lenovo IC ad33 0
ZIP IC abcd 200
ZIP IC addf 0

改成以下表：
Company IC Total PCB Total
IBM A2051 200
IBM 12ad 300
IBM ded22 220
IBM aed45 220
Lenovo adde2 500
Lenovo 15a 100
Lenovo ad33 0
ZIP abcd 200
ZIP addf 0

...全文

88 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

ljhcy99 2008-12-12

打赏
举报

select Company, IC = case when Genre ='IC' then Model else NULL end,
Total1 =case when Genre ='IC' then Total else NULL end,
PCB =case when Genre =' PCB' then Model else NULL end,
Total2=case when Genre ='PCB' then Total else NULL end
from table

百年树人 2008-12-12

打赏
举报

declare @ta table(Company varchar(10),   Genre varchar(10),    Model varchar(10),     Total  int)

insert @ta  

select 'IBM','IC','A2051',200 union all

select 'IBM','PCB','12ad',300 union all

select 'IBM','IC','ded22',220 union all

select 'IBM','PCB','aed45',220  union all

select 'Lenovo','IC','adde2',500 union all

select 'Lenovo','PCB','15a',100 union all

select 'Lenovo','IC','ad33',0 union all

select 'ZIP','IC','abcd',200 union all

select 'ZIP','IC','addf',0



select 

  Company,

  isnull(case Genre when 'IC' then (select Model from @ta where Genre='IC' and Model=a.Model) end,'') as [IC],

  isnull(case Genre when 'IC' then (select Total from @ta where Genre='IC' and Model=a.Model) end,'') as [Total],

  isnull(case Genre when 'PCB' then (select Model from @ta where Genre='PCB' and Model=a.Model) end,'') as [PCB],

  isnull(case Genre when 'PCB' then (select Total from @ta where Genre='PCB' and Model=a.Model) end,'') as [Total]

from @ta a



/**

Company    IC         Total       PCB        Total       

---------- ---------- ----------- ---------- ----------- 

IBM        A2051      200                    0

IBM                   0           12ad       300

IBM        ded22      220                    0

IBM                   0           aed45      220

Lenovo     adde2      500                    0

Lenovo                0           15a        100

Lenovo     ad33       0                      0

ZIP        abcd       200                    0

ZIP        addf       0                      0



（所影响的行数为 9 行）

**/

mailto520 2008-12-12

打赏
举报

谢谢maco_wang !!!

csdyyr 2008-12-12

打赏
举报

DECLARE @TB TABLE(Company  VARCHAR(6),  Genre VARCHAR(6),    Model  VARCHAR(6),     Total    INT)

INSERT @TB

SELECT 'IBM',  'IC',  'A2051',  200 UNION ALL 

SELECT 'IBM',  'PCB',  '12ad',  300 UNION ALL 

SELECT 'IBM',  'IC',  'ded22',  220 UNION ALL 

SELECT 'IBM',  'PCB',  'aed45',  220 UNION ALL 

SELECT 'Lenovo',  'IC',  'adde2',  500 UNION ALL 

SELECT 'Lenovo',  'PCB',  '15a',  100 UNION ALL 

SELECT 'Lenovo',  'IC',  'ad33',  0 UNION ALL 

SELECT 'ZIP',  'IC',  'abcd',  200 UNION ALL 

SELECT 'ZIP',  'IC',  'addf',  0





SELECT Company,

                case when Genre='IC' THEN Model else '' end as IC,

                 CASE WHEN genre='IC' THEN RTRIM(TOTAL) ELSE '' END AS TOTAL,

                case when Genre='PCB' THEN Model else '' end as IC,

                 CASE WHEN genre='PCB' THEN RTRIM(TOTAL) ELSE '' END AS TOTAL              

FROM @TB

/*

Company IC     TOTAL        IC     TOTAL        

------- ------ ------------ ------ ------------ 

IBM     A2051  200                 

IBM                         12ad   300

IBM     ded22  220                 

IBM                         aed45  220

Lenovo  adde2  500                 

Lenovo                      15a    100

Lenovo  ad33   0                   

ZIP     abcd   200                 

ZIP     addf   0    

*/

叶子 2008-12-12

打赏
举报



declare @table table (company varchar(6),genre varchar(3),model varchar(5),total varchar(20))

insert into @table

select 'ibm','ic','a2051',200 union all

select 'ibm','pcb','12ad',300 union all

select 'ibm','ic','ded22',220 union all

select 'ibm','pcb','aed45',220 union all

select 'lenovo','ic','adde2',500 union all

select 'lenovo','pcb','15a',100 union all

select 'lenovo','ic','ad33',0 union all

select 'zip','ic','abcd',200 union all

select 'zip','ic','addf',0



select * from @table

/*

company genre model total

------- ----- ----- --------------------

ibm     ic    a2051 200

ibm     pcb   12ad  300

ibm     ic    ded22 220

ibm     pcb   aed45 220

lenovo  ic    adde2 500

lenovo  pcb   15a   100

lenovo  ic    ad33  0

zip     ic    abcd  200

zip     ic    addf  0

*/

select company ,ic=model, total=total,pcb='',total='' from @table where genre='ic'

union all

select company ,ic='', total='',pcb=model,total=total from @table where genre='pcb'

order by company



/*

company ic    total                pcb   total

------- ----- -------------------- ----- --------------------

ibm     a2051 200                        

ibm     ded22 220                        

ibm                                12ad  300

ibm                                aed45 220

lenovo                             15a   100

lenovo  adde2 500                        

lenovo  ad33  0                          

zip     abcd  200                        

zip     addf  0   

*/

叶子 2008-12-12

打赏
举报





declare @table table (company varchar(6),genre varchar(3),model varchar(5),total varchar(20))

insert into @table

select 'ibm','ic','a2051',200 union all

select 'ibm','pcb','12ad',300 union all

select 'ibm','ic','ded22',220 union all

select 'ibm','pcb','aed45',220 union all

select 'lenovo','ic','adde2',500 union all

select 'lenovo','pcb','15a',100 union all

select 'lenovo','ic','ad33',0 union all

select 'zip','ic','abcd',200 union all

select 'zip','ic','addf',0



select * from @table



select company ,ic=model, total=total,pcb='',total='' from @table where genre='ic'

union all

select company ,ic='', total='',pcb=model,total=total from @table where genre='pcb'

order by company